Forms

/**
 * Checks if users are required to log in to respond to the Form.
 * Note: Due to some limitations in Apps Script compared to current Google Form settings,
 * this check may not be exhaustive. Please verify the settings manually for accuracy.
 * Author: Mr Shane
 * Version: 2024-10-04
 */
function checkFormAnonimity() {
  const form = FormApp.getActiveForm(); // Get the active form.
  // Check Form settings (except if "Disable auto-save for all respondents" is enabled.)
  const settings = {
    "Collect email addresses": form.collectsEmail(), // "Do not collect" = false
    "Allow response editing": form.canEditResponse(),
    "Limit to 1 response": form.hasLimitOneResponsePerUser(),
    "Restrict to users in [yourdomain.com] and its trusted organizations": form.requiresLogin() // DEPRECATED
  };
  for (const [setting, value] of Object.entries(settings)) {
    Logger.log(`${setting}: ${value}`);
  }
  // Check if the Form has a File Upload item.
  const hasFileUpload = form.getItems().some(item => item.getType() === FormApp.ItemType.FILE_UPLOAD);
  Logger.log(`Contains File Upload item: ${hasFileUpload}`);
}

/**
 * Create "edit response" links for every response, in the linked sheets file.
 * Author: Mr Shane
 * Version 2024-07-24
 * Prerequisites: Manually run this script in the apps script editor of the Form to which this script is bound.
 */
const linkCol = 5; // Configure the target column for the URLs.
const firstRow = 2; // Configure the first row of data.

function createEditLinks() {
  const form = FormApp.getActiveForm(); // Returns the form to which the script is container-bound.
  const formId = form.getId(); // Gets the ID of the form.
  const destinationType = form.getDestinationType(); // Gets the type of the form's response destination.
  if (destinationType !== FormApp.DestinationType.SPREADSHEET) { // IF the DestinationType is not a Sheets file...
    Logger.log('This form is not saving responses in Google Sheets'); // THEN log this error...
    return;// AND abort the script.
  }
  const responses = form.getResponses(); // Gets an array of all of the form's responses.
  const editUrls = [];
  for (let i = 0; i < responses.length; i++) {
    const response = responses[i];
    const editUrl = response.getEditResponseUrl(); // Generates a URL that can be used to edit a response that has already been submitted.
    editUrls.push([editUrl]); // Stores the URL in a 2D array.
  }
  const ssId = form.getDestinationId(); // Gets the ID of the form's response destination.
  const ss = SpreadsheetApp.openById(ssId); // Opens the form's response destination.
  const sheets = ss.getSheets(); // Gets all sheets in the response destination file.
  const sh = sheets.find(sheet => sheet.getFormUrl() && sheet.getFormUrl().includes(formId)); // Finds the sheet linked to the form.
  const range = sh.getRange(firstRow, linkCol, editUrls.length, 1); // Sets the output range.
  range.setValues(editUrls); // Sets all the edit URLs in one go.
}

/** Very simple 1 line script to delete ALL responses in the Form */
function deleteAllResponses() {FormApp.getActiveForm().deleteAllResponses()}

/**
 * Log the parent folder ID, Sheets file ID, and sheet name associated with the form and its responses.
 */
function getResponsesSheetName() {
  const form = FormApp.getActiveForm(); // Get the form to which the script is container-bound.
  const formId = form.getId(); // Get the ID of the form.
  Logger.log(`The ID of the editable form is: ${formId}`); // Log the ID of the editable form.
  const parentFolder = DriveApp.getFileById(formId).getParents().next(); // Get the Drive folder where the form is located.
  const pfId = parentFolder.getId(); // Get the ID of the parent folder.
  Logger.log(`The form is stored in the folder with ID of: ${pfId}`); // Log the parent folder ID.
  const destinationType = form.getDestinationType(); // Get the type of the form's response destination.
  if (destinationType !== FormApp.DestinationType.SPREADSHEET) { // IF the DestinationType is not a Sheets file...
    Logger.log('This form is not saving responses in Google Sheets'); // THEN log this error...
    return; // AND abort the script.
  }
  const ssId = form.getDestinationId(); // Get the ID of the form's response destination (ie. the linked Sheets file).
  Logger.log(`The form is linked to Sheets file with ID of: ${ssId}`); // Log the linked Sheets file ID where responses are stored.
  const ss = SpreadsheetApp.openById(ssId); // Open the Sheets file.
  const sheets = ss.getSheets(); // Get all sheets in the Sheets file.
  const sh = sheets.find(sheet => sheet.getFormUrl().includes(formId)); // Find the sheet linked to the form.
  const sheetName = sh.getName(); // Get the name of the sheet.
  Logger.log(`The form responses are stored in the sheet named: ${sheetName}`); // Log the name of the sheet where responses are stored.
}

/**
 * Log the questions and answer options for a Google Forms quiz,
 * including whether each option is marked as correct using isCorrectAnswer().
 * Note: Apps Script can only processes these gradable item types: Multiple Choice, Checkboxes, and Drop-down.
 *
 * Author: Mr Shane
 * Version: 2025-05-07
 *
 * To use:
 * - Open the script editor from your Google Form (More︙ > Apps Script).
 * - Paste this script and manually ▶ Run the 'logQuizAnswers' function.
 * - In the script editor, go to 'Executions' to see the output.
 *
 * @see https://developers.google.com/apps-script/reference/forms/item-type
 * @see https://developers.google.com/apps-script/reference/forms/multiple-choice-item
 * @see https://developers.google.com/apps-script/reference/forms/checkbox-item
 * @see https://developers.google.com/apps-script/reference/forms/list-item
 *
 */
function logQuizAnswers() {
  const form = FormApp.getActiveForm(); // Get the active Google Form
  const items = form.getItems(); // Get all items from the form
  items.forEach((item, index) => { // Loop through each item with its index
    const type = item.getType(); // Get the item's type
    const title = item.getTitle(); // Get the item's title (question text)
    if (
      type === FormApp.ItemType.MULTIPLE_CHOICE || // Multiple choice
      type === FormApp.ItemType.CHECKBOX || // Checkboxes
      type === FormApp.ItemType.LIST // Drop-down
    ) {
      let choices; // Declare a variable to store the choices
      switch (type) { // Check the specific item type
        case FormApp.ItemType.MULTIPLE_CHOICE: // Multiple choice
          choices = item.asMultipleChoiceItem().getChoices(); // Get choices
          break; // End this case
        case FormApp.ItemType.CHECKBOX: // Checkboxes
          choices = item.asCheckboxItem().getChoices(); // Get choices
          break; // End this case
        case FormApp.ItemType.LIST: // Drop-down
          choices = item.asListItem().getChoices(); // Get choices
          break; // End this case
      }
      console.log(`Q${index + 1}: ${title}`); // Log the question number and title
      choices.forEach(choice => { // Loop through each choice
        const correct = choice.isCorrectAnswer(); // Determine if the choice is correct
        console.log(` - ${choice.getValue()} (Correct: ${correct})`); // Log the value and correctness
      });
    }
  });
}

/**
 * 'On form submit', calculate RATING scores starting after the first section, and send an email with the results to the respondent.
 * Includes: section names, totals, averages, and overall statistics.
 * Author: Mr Shane
 * Version: 2024-12-11
 *
 * Prerequisites:
 * 1. The form settings uses "Collect email addresses" = "Verified".
 * 2. The Google Forms API can only return scores for Multiple Choice, Dropdown and Checkbox style questions.
 * 3. RATING questions must not begin in section 1.
 * 4. Each section must have a title.
 * 5. Create an 'On form submit' installable trigger for the 'onFormSubmit' function.
 * 6. Configure the 'Time zone' in 'Project Settings > General settings'
 */
function onFormSubmit(e) {
  const form = FormApp.getActiveForm();
  const response = e.response;
  const respondentEmail = response.getRespondentEmail();
  if (!respondentEmail) {
    console.log("No respondent email found.");
    return;
  }
  const items = form.getItems();
  let results = [];
  let currentSet = { title: "", totalRating: 0, numQuestions: 0 };
  items.forEach((item) => {
    switch (item.getType()) {
      case FormApp.ItemType.PAGE_BREAK:
        if (currentSet.numQuestions > 0) results.push(currentSet);
        const pageBreakTitle = item.asPageBreakItem().getTitle();
        currentSet = { title: pageBreakTitle, totalRating: 0, numQuestions: 0 }; // Set new section title
        break;
      case FormApp.ItemType.RATING:
        const ratingResponse = response.getResponseForItem(item);
        const ratingValue = ratingResponse ? Number(ratingResponse.getResponse()) : 0;
        if (ratingValue) {
          currentSet.totalRating += ratingValue;
          currentSet.numQuestions++;
        }
        break;
    }
  });
  if (currentSet.numQuestions > 0) results.push(currentSet);
  const totalRating = results.reduce((sum, set) => sum + set.totalRating, 0);
  const totalQuestions = results.reduce((sum, set) => sum + set.numQuestions, 0);
  const totalAverage = totalQuestions ? (totalRating / totalQuestions).toFixed(2) : "N/A";
  const emailBody = results.reduce((body, set) => {
    const sectionAverage = set.numQuestions ? (set.totalRating / set.numQuestions).toFixed(2) : "N/A";
    return `${body}${set.title}:\nTotal Rating: ${set.totalRating}\nAverage Rating: ${sectionAverage}\n\n`;
  }, "Thank you for your feedback!\n\nYour ratings by section are as follows:\n");
  MailApp.sendEmail({
    to: respondentEmail,
    subject: "Feedback Results",
    body: `${emailBody}Overall Total Rating: ${totalRating}\nOverall Average Rating: ${totalAverage}`
  });
  console.log("Email sent successfully!");
}

/**
 * 'On form submit', calculate RATING scores starting after the first section, and send an email with the results to the respondent.
 * Includes: section totals, averages, and overall statistics.
 * Author: Mr Shane
 * Version: 2024-12-11
 *
 * Prerequisites:
 * 1. The form settings uses "Collect email addresses" = "Verified".
 * 2. The Google Forms API can only return scores for Multiple Choice, Dropdown and Checkbox style questions.
 * 3. RATING questions must not begin in section 1.
 * 4. Create an 'On form submit' installable trigger for the 'onFormSubmit' function.
 * 5. Configure the 'Time zone' in 'Project Settings > General settings'
 */
function onFormSubmit(e) {
  const form = FormApp.getActiveForm();
  const response = e.response;
  const respondentEmail = response.getRespondentEmail();
  if (!respondentEmail) {
    console.log("No respondent email found.");
    return;
  }
  const items = form.getItems();
  let results = [];
  let currentSet = { totalRating: 0, numQuestions: 0 };
  items.forEach((item) => {
    switch (item.getType()) {
      case FormApp.ItemType.PAGE_BREAK:
        if (currentSet.numQuestions > 0) results.push(currentSet);
        currentSet = { totalRating: 0, numQuestions: 0 }; // Reset for next section
        break;
      case FormApp.ItemType.RATING:
        const ratingResponse = response.getResponseForItem(item);
        const ratingValue = ratingResponse ? Number(ratingResponse.getResponse()) : 0;
        if (ratingValue) {
          currentSet.totalRating += ratingValue;
          currentSet.numQuestions++;
        }
        break;
    }
  });
  if (currentSet.numQuestions > 0) results.push(currentSet);
  const totalRating = results.reduce((sum, set) => sum + set.totalRating, 0);
  const totalQuestions = results.reduce((sum, set) => sum + set.numQuestions, 0);
  const totalAverage = totalQuestions ? (totalRating / totalQuestions).toFixed(2) : "N/A";
  const emailBody = results.reduce((body, set, index) => {
    const sectionAverage = set.numQuestions ? (set.totalRating / set.numQuestions).toFixed(2) : "N/A";
    return `${body}Section #${index + 1}:\nTotal Rating: ${set.totalRating}\nAverage Rating: ${sectionAverage}\n\n`;
  }, "Thank you for your feedback!\n\nYour ratings by section are as follows:\n");
  MailApp.sendEmail({
    to: respondentEmail,
    subject: "Feedback Results",
    body: `${emailBody}Overall Total Rating: ${totalRating}\nOverall Average Rating: ${totalAverage}`
  });
  console.log("Email sent successfully!");
}

/**
 * 'On form submit', calculate section scores of a quiz, and send an email with the results to the respondent.
 * Author: Mr Shane
 * Version 2025-05-07
 *
 * Prerequisites:
 * 1. The form settings uses "Collect email addresses" = "Verified".
 * 2. The Google Forms API can NOT get points for 'Multiple-choice grid' or 'Tick box grid' questions.
 * 3. Quiz questions must not begin in section 1.
 * 4. Each section must have a title.
 * 5. Checkboxes questions require all the correct answers to be selected otherwise the score will be 0.
 * 6. Create an 'On form submit' installable trigger for the 'onFormSubmit' function.
 * 7. Configure the 'Time zone' in 'Project Settings > General settings'
 *
 * @see https://developers.google.com/apps-script/reference/forms/item-type
 * @see https://developers.google.com/apps-script/reference/forms/text-item
 * @see https://developers.google.com/apps-script/reference/forms/multiple-choice-item
 * @see https://developers.google.com/apps-script/reference/forms/checkbox-item
 * @see https://developers.google.com/apps-script/reference/forms/list-item
 *
 */
function onFormSubmit(e) {
  const form = FormApp.getActiveForm(); // Get the form.
  const response = e.response; // Get the submitted response.
  const respondentEmail = response.getRespondentEmail(); // Get the respondent's verified email address.
  if (!respondentEmail) {
    console.log("No respondent email found.");
    return;
  }
  const items = form.getItems();
  let results = [];
  let currentSet = { title: "", score: 0, points: 0 }; // Initialize with no title.
  items.forEach((item) => {
    switch (item.getType()) {
      case FormApp.ItemType.PAGE_BREAK:
        // Save the current section's data before moving to the next one.
        if (currentSet.points > 0) results.push(currentSet);
        // Use the title of the PAGE_BREAK as the section title.
        const pageBreakTitle = item.asPageBreakItem().getTitle();
        currentSet = { title: pageBreakTitle, score: 0, points: 0 };
        break;
      case FormApp.ItemType.TEXT: // Short answer.
      case FormApp.ItemType.MULTIPLE_CHOICE: // Multiple choice.
      case FormApp.ItemType.CHECKBOX: // Checkboxes.
      case FormApp.ItemType.LIST: // Drop-down.
        const gradableItem = castQuizItem_(item);
        const gradableResponse = response.getGradableResponseForItem(item);
        currentSet.score += gradableResponse?.getScore() || 0; // Accumulate scores.
        currentSet.points += gradableItem.getPoints() || 0; // Accumulate points.
        break;
    }
  });
  if (currentSet.points > 0) results.push(currentSet); // Push the last section if it has any data.
  // Generate email content for each section.
  const emailBody = results.reduce((body, set) => {
    return `${body}${set.title}:\nScore: ${set.score}/${set.points}\n\n`;
  }, "Thank you for taking the quiz!\n\nYour scores are as follows:\n");
  const totalScore = results.reduce((sum, set) => sum + set.score, 0); // Calculate total scores.
  const totalPoints = results.reduce((sum, set) => sum + set.points, 0); // Calculate total pionts.
  MailApp.sendEmail({ // Send an email...
    to: respondentEmail, // to the respondent...
    subject: "Quiz Results", // about their quiz results...
    body: `${emailBody}Total score: ${totalScore}/${totalPoints}` // including their scores/points.
  });
  console.log(`Quiz Results email sent to ${respondentEmail}`); // Log when an email is sent.
}
function castQuizItem_(item) {
  switch (item.getType()) {
    case FormApp.ItemType.TEXT: return item.asTextItem(); // Short answer.
    case FormApp.ItemType.MULTIPLE_CHOICE: return item.asMultipleChoiceItem(); // Multiple choice.
    case FormApp.ItemType.CHECKBOX: return item.asCheckboxItem(); // Checkboxes.
    case FormApp.ItemType.LIST: return item.asListItem(); // Drop-down.
    default: return null;
  }
}

/**
 * 'On form submit', calculate section scores of a quiz, and send an email with the results to the respondent.
 * Author: Mr Shane
 * Version: 2025-05-07
 *
 * Prerequisites:
 * 1. The form settings uses "Collect email addresses" = "Verified".
 * 2. The Google Forms API can NOT get points for 'Multiple-choice grid' or 'Tick box grid' questions.
 * 3. Quiz questions must not begin in section 1.
 * 4. Checkboxes questions require all the correct answers to be selected otherwise the score will be 0.
 * 5. Create an 'On form submit' installable trigger for the 'onFormSubmit' function.
 * 6. Configure the 'Time zone' in 'Project Settings > General settings'
 *
 * @see https://developers.google.com/apps-script/reference/forms/item-type
 * @see https://developers.google.com/apps-script/reference/forms/text-item
 * @see https://developers.google.com/apps-script/reference/forms/multiple-choice-item
 * @see https://developers.google.com/apps-script/reference/forms/checkbox-item
 * @see https://developers.google.com/apps-script/reference/forms/list-item
 *
 */
function onFormSubmit(e) {
  const form = FormApp.getActiveForm(); // Get the form.
  const response = e.response; // Get the submitted response.
  const respondentEmail = response.getRespondentEmail(); // Get the respondents Verifies email address.
  if (!respondentEmail) {
    console.log("No respondent email found.");
    return;
  }
  const items = form.getItems();
  let results = [];
  let currentSet = { title: "Section #1", score: 0, points: 0 };
  let setCounter = 1;
  items.forEach((item) => {
    switch (item.getType()) {
      case FormApp.ItemType.PAGE_BREAK:
        if (currentSet.points > 0) results.push(currentSet);
        currentSet = { title: `Section #${++setCounter}`, score: 0, points: 0 };
        break;
      case FormApp.ItemType.TEXT: // Short answer.
      case FormApp.ItemType.MULTIPLE_CHOICE: // Multiple choice.
      case FormApp.ItemType.CHECKBOX: // Checkboxes.
      case FormApp.ItemType.LIST: // Drop-down.
        const gradableItem = castQuizItem_(item);
        const gradableResponse = response.getGradableResponseForItem(item);
        currentSet.score += gradableResponse?.getScore() || 0; // Calculate total scores.
        currentSet.points += gradableItem.getPoints() || 0; // Calculate total points.
        break;
    }
  });
  if (currentSet.points > 0) results.push(currentSet);
  const emailBody = results.reduce((body, set) => {
    return `${body}${set.title}:\nScore: ${set.score}/${set.points}\n\n`;
  }, "Thank you for taking the quiz!\n\nYour scores are as follows:\n");
  const totalScore = results.reduce((sum, set) => sum + set.score, 0);
  const totalPoints = results.reduce((sum, set) => sum + set.points, 0);
  MailApp.sendEmail({ // Send an email...
    to: respondentEmail, // to the respondent...
    subject: "Quiz Results", // about their quiz results...
    body: `${emailBody}Total score: ${totalScore}/${totalPoints}` // including their scores/points.
  });
  console.log(`Quiz Results email sent to ${respondentEmail}`); // Log when an email is sent.
}
function castQuizItem_(item) {
  switch (item.getType()) {
    case FormApp.ItemType.TEXT: return item.asTextItem(); // Short answer.
    case FormApp.ItemType.MULTIPLE_CHOICE: return item.asMultipleChoiceItem(); // Multiple choice.
    case FormApp.ItemType.CHECKBOX: return item.asCheckboxItem(); // Checkboxes.
    case FormApp.ItemType.LIST: return item.asListItem(); // Drop-down.
    default: return null;
  }
}

/**
 * 'On form submit', create an 'edit response' URL in the linked sheets file.
 * Author: Mr Shane
 * Version 2024-07-24
 * Prerequisites: Create an 'On form submit' installable trigger for the 'createEditLink' function.
 */
const linkCol = 5; // Configure the target column
function createEditLink(e) {
  const responseURL = e.response.getEditResponseUrl(); // Generates a URL that can be used to edit a response that has already been submitted.
  const form = e.source; // A Form object, representing the Google Forms file to which the script is container-bound.
  const formId = form.getId(); // Gets the ID of the form.
  const destinationType = form.getDestinationType(); // Gets the type of the form's response destination.
  if (destinationType !== FormApp.DestinationType.SPREADSHEET) { // IF the DestinationType is not a Sheets file...
    Logger.log('This form is not saving responses in Google Sheets'); // THEN log this error...
    return; // AND abort the script.
  }
  const ssId = form.getDestinationId(); // Gets the ID of the form's response destination.
  const ss = SpreadsheetApp.openById(ssId); // Opens the form's response destination.
  const sheets = ss.getSheets(); // Gets all sheets in the response destination file.
  const sh = sheets.find(sheet => sheet.getFormUrl() && sheet.getFormUrl().includes(formId)); // Finds the sheet linked to the form.
  const lastRow = sh.getLastRow(); // Gets the last row of the linked sheet.
  sh.getRange(lastRow,linkCol).setValue(responseURL); // Sets the edit URL value.
}

/**
 * Stop accepting responses once the response limit is reached.
 * Author: Mr Shane
 * Version: 2025-03-17
 * Prerequisites: Create an 'On form submit' installable trigger for the 'stopAcceptingResponses' function.
 */
function stopAcceptingResponses() {
  const maxResponses = 3; // Configure the maximum number of responses the form will accept.
  const form = FormApp.getActiveForm();
  const responses = form.getResponses();
  if (responses.length >= maxResponses) {
    form.setAcceptingResponses(false).setCustomClosedFormMessage("Sorry, you're too late. This form is no longer accepting responses.");
  }
}

/**
 * 'On form submit' write the Form response to a specific Sheets file.
 * Prerequisites: Create an 'On form submit' installable trigger for the 'onFormSubmit' function.
 */
function onFormSubmit(e) {
  const ssid = "1nzCm9yKmKxJtSgPaBU8zo4Ic3FLhukEuccM43u_s78I"; // Configure the file ID of the target Sheets file.
  const sheetName = "Sheet1"; // Configure the name of the target sheet.
  const r = e.response; // The form response event.
  const timestamp = r.getTimestamp(); // Get the timestamp of the form submission.
  const responses = r.getItemResponses().map(item => item.getResponse()); // Get the form responses.
  const rowData = [timestamp, ...responses]; // Combine the timestamp with the responses.
  const ss = SpreadsheetApp.openById(ssid);
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow(); // Get the last row that has data.
  sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]); // Write the row data to the next available row
}

/**
 * 'On form submit' write the quiz scores to a specific Sheets file.
 * Author: Mr Shane
 * Version 2024-10-14
 *
 * Prerequisites:
 * 1. The form settings uses "Collect email addresses" = "Verified".
 * 2. Create an 'On form submit' installable trigger for the 'onFormSubmit' function.
 */
function onFormSubmit(e) {
  const ssid = "1CsQmwnE_obufQ7S_Jwpataxc1CwvqcHGBw5VTNTQnRM"; // Configure the file ID of the target Sheets file.
  const sheetName = "Sheet1"; // Configure the name of the target sheet.
  const r = e.response; // The form response event.
  const timestamp = r.getTimestamp(); // Get the timestamp of the form submission.
  const emailAddress = r.getRespondentEmail(); // Get the email address of the respondent.
  const points = r.getGradableItemResponses().map(item => item.getScore()); // Get the form points.
  const rowData = [timestamp, emailAddress, ...points]; // Combine the timestamp with respondent email address and points.
  const ss = SpreadsheetApp.openById(ssid);
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow(); // Get the last row that has data.
  sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]); // Write the row data to the next available row
}
/**
 * 'On form submit' email the respondent only if score 10/10.
 * Author: Tanaike
 * Version: 2022-01-10
 * @see https://stackoverflow.com/a/70647905/14678267
 *
 * Prerequisites:
 * 1. The form settings uses "Collect email addresses" = "Verified".
 * 2. Create an 'On form submit' installable trigger for the 'sendEmail' function.
 */
function sendEmail(e) {
  const maxGrade = 10; // This is from "10/10" in your question.
  const grade = e.response.getGradableItemResponses().reduce((p, e) => p += e.getScore(), 0);
  const emailAddress = e.response.getRespondentEmail();
  if (grade < maxGrade || !emailAddress) return;
  const subject = "Sample subject"; // Please set the subject.
  const textBody = "Hi, you've already passed your quiz and you got a 10/10.";
  GmailApp.sendEmail(emailAddress, subject, textBody);
}