My Google Apps Script Works Perfectly with Two Exceptions

[Robert Mcleod]

First of all, I sincerely want all of you to know today June 7 is my birthday. Therefore, I am hoping to receive a solution to my very frustrating Apps Script code as my birthday present. 

Hello...My name is Robert, and I am middle school math teacher who is attempting to use a Google Apps Script to create Google Forms quizzes and tests from Google Sheets data starting with the following school year.

----------------------------------------------------------------------------------------------------------------------------------------------

function createQuizFromSheet() {
  // Get the active spreadsheet and sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const quizTitle = sheet.getName();
  // Get all the data from the sheet, skipping the header row
  const data = sheet.getDataRange().getValues().slice(1);

  // Create a new Google Form
  const form = FormApp.create(quizTitle);
  form.setIsQuiz(true); // Turn the form into a quiz

  // Loop through each row of data (each question)
  data.forEach(row => {
    // Extract question details from the row
    const questionTitle = row[0];
    const choices = row.slice(1, 5); // Choices A-D
    const correctAnswerLetter = row[5];
    const points = row[6];

     // Convert the letter to index for correct choice
    let correctAnswerIndex = 0;
    if (correctAnswerLetter === "B") correctAnswerIndex = 1;
    if (correctAnswerLetter === "C") correctAnswerIndex = 2;
    if (correctAnswerLetter === "D") correctAnswerIndex = 3;


    // Add multiple choice question to the form
    const item = form.addMultipleChoiceItem()
      .setTitle(questionTitle);

     // Create choices, mark the correct one and set points
    const choiceItems = [];
    for(let i = 0; i< choices.length; i++){
      if (choices[i]){ //only create if the choice is not blank
        choiceItems.push(item.createChoice(choices[i], i ===

correctAnswerIndex));
      }
    }
    item.setChoices(choiceItems);
    item.setPoints(points);
  });


  // Get and log the form's published URL
  const formUrl = form.getPublishedUrl();
  Logger.log('Form URL: ' + formUrl);

  // Optional: Display form URL in the spreadsheet
  // sheet.getRange(1, 8).setValue('Form URL'); // Set header
  // sheet.getRange(2, 8).setValue(formUrl); // Output URL
}

----------------------------------------------------------------------------------------------------------------------------------------------

I want you to know my Google Apps Script Works Perfectly with two exceptions. (Exception 1): The script doesn't publish the url of the new Google Forms assessment. Therefore, I can only access the form from my Google Drive. (Exception 2): If you click the attached Google Form, you will see the script tried to add another question after Problem 14 referred to as "Question" with an "Option 1" choice. If the script is reading Columns I through L, I am wondering whether or not there is a way to program the script to ignore that data. Any assistance you can provide will be greatly appreciated. I provided you with the attached links to my Google Forms assessment; my Google Sheets data and the Apps Script for your reference.

https://docs.google.com/forms/d/1Q8KdgjxAkciEJy_9MWK8SDk_kdxFBMtrzlpYydjNgTU/edit

https://docs.google.com/spreadsheets/d/1eQbgeywpWrEiMPkyyGGc4Te9ZM-lT1MmGy_JHX0iPUI/edit?usp=sharing

https://script.google.com/u/0/home/projects/1esRye12sjrBT6MN_ifZa-4IvJJ-fS_2EOEi0ff4gInOxgMT13UOY48zh/edit

[Michael O'Shaughnessy]

Hello Robert,

First off.... Happy Birthday!!!

Now for the issues you are having.  I am not sure what all your formulas are trying to accomplish BUT that is what is causing the error.  NOT that you have formulas, it is the fact that you have formulas in rows 16-20 that have no data.  So when your script grabs the data it grabs those rows also because they are NOT empty, they have formulas in them.  So when you script got to the data in row 16 of your sheet, there is nothing there.  So, delete those formulas and your script will work.

As for your second issue, the code to place the URL on your sheet is commented out in your code.  You should just uncomment and you will be good to go.

Let me know if you have any questions.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/101d950f-e7d9-42e2-b304-ed0e473a073dn%40googlegroups.com.

[Robert Mcleod]

Thank you for the birthday comment, and I believe I fixed the url commented-out issue as well Okay...I had a feeling the formulas from Column I through Column L were causing my script errors. I created formulas to minimize manual steps to achieve my goal. My ultimate goal is to have each active sheet recognize the number of correct answer choices I paste into Column F. Populate Column A through Column E and Column G based on Column F data,

 and convert all of that data into a brand new Google Form that generates the url preferably within the script itself rather than the active spreadsheet.

Column A is "Refer to PDF Problem 1", "Refer to PDF Problem 2", "Refer to PDF Problem 3", etc. Column B = A, Column C = B, Column D = C, Column E = D. The point value for each problem in Column G equals 100 divided by the number of correct answers in Column F. The maximum number of problems I have in any quiz or test is 20. Therefore, the formulas start at Row 2 and extend to Row 21. If a script can be made to have A2:E21,G2:G21 automatically adjust their data based on Column F, two great things will happen. I will no longer need formulas in the spreadsheets, and my goal will finally be achieved.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/M8MQNeNzdKc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLhXo3vV%3D7r7bkJ0uR18iYZ-Kg2d_kwoV1u_7%2BM2PxNf2A%40mail.gmail.com.

[Brent Guttmann]

If you can provide an example sheet with example data, I  have some time this evening and can take and likely wrap this up for you.

[Robert Mcleod]

Hi Brent...I recently came to the decision to be satisfied with finally creating a script that generates a Google Form based on Google Sheets data although I have to add a few more manual steps than I prefer. The attached image shows a few minor modifications I made to my active spreadsheet by relocating Column data. The reason for this action is I can't figure out how to create a script that will automatically populate Columns A through E based on Column G data(Correct Answer Choices). In other words, my perfect script will allow me to simply paste correct answers in Column G, and observe all of the other columns autofill based on the Column F data. I now have to manually autofill along with calculating the Question Points(Column F) by dividing 100 by the number of answer choices in Column E. If you are unable to assist me, I want you to know how appreciative I am for receiving support from you, Alice and Michael from the Google Apps Script Community.

Robert

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/fe03d6fa-f85b-45e6-81e0-379cbece455bn%40googlegroups.com.

[Robert Mcleod]

Brent...I wanted to give you the following copy of the script that currently works for me as well:

function createQuizFromSheet() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getActiveSheet();

  const sheetName = sheet.getName();

  // Get data, skipping the header row

  const data = sheet.getDataRange().getValues().slice(1);

  // Filter out rows where Column G (correct answer) is empty

  const validQuestions = data.filter(row => row[6]);

  if (validQuestions.length === 0) {

    Logger.log("No questions with designated correct answers found in Column G.");

    SpreadsheetApp.getUi().alert("No questions with designated correct answers found in Column G.");

    return;

  }

  // Calculate points per question

  const totalPoints = 100;

  const pointsPerQuestion = totalPoints / validQuestions.length;

  const form = FormApp.create(sheetName);

  form.setIsQuiz(true);

  validQuestions.forEach(row => {

    const questionTitle = row[0];

    const choices = [row[1], row[2], row[3], row[4]]; // Columns B to E

    const correctAnswerLetter = row[6].toUpperCase(); // Get correct answer letter from Column G

    const item = form.addMultipleChoiceItem();

    item.setTitle(questionTitle)

      .setPoints(pointsPerQuestion);

    const formChoices = choices.map(choiceValue => {

      const isCorrect = choiceValue === correctAnswerLetter;

      return item.createChoice(choiceValue, isCorrect);

    });

    item.setChoices(formChoices);

    // Populate Columns A to F in the Sheet based on Column G

    const rowIndex = data.indexOf(row) + 2; // +2 to account for header and 0-based index

    sheet.getRange(rowIndex, 1).setValue(questionTitle);

    sheet.getRange(rowIndex, 2).setValue(row[1]);

    sheet.getRange(rowIndex, 3).setValue(row[2]);

    sheet.getRange(rowIndex, 4).setValue(row[3]);

    sheet.getRange(rowIndex, 5).setValue(row[4]);

    sheet.getRange(rowIndex, 6).setValue(pointsPerQuestion);

  });

  const formUrl = form.getPublishedUrl();

  Logger.log('Quiz created: ' + formUrl);

  SpreadsheetApp.getUi().alert('Quiz created: ' + formUrl);

}

[profe...@gmail.com]

Estimado(a) remitente,

Gracias por su mensaje. Hemos revisado su consulta y parece que no se ajusta a ninguna de nuestras categorías predefinidas (Productos, Quejas, Asistencia a Eventos o Apertura de Cuenta).

Para poder ayudarle de la mejor manera posible, le sugerimos que nos contacte a través de Telegram, donde nuestro equipo podrá atenderle de forma más personalizada.

Enlace a Telegram: https://t.me/miobot

Atentamente,
Equipo N8N

---
This email was sent automatically with n8n

[Robert Mcleod]

Hello...I'm sure you sent the previous message to the wrong person because I don't speak or write in Spanish.

--

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/M8MQNeNzdKc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAJrshg-XSRwY6AOEjDcxS1BWPtEfNpa2V9RbzvND5ssxnfgZgw%40mail.gmail.com.

[profe...@gmail.com]

Estimado/a remitente,

Gracias por su correo.

Parece que su consulta no se ajusta a ninguna de nuestras categorías predefinidas (información de productos, quejas, asistencia a eventos o apertura de cuenta).

Para una atención más personalizada, le sugerimos que se ponga en contacto con nosotros a través de Telegram: https://t.me/gasmadrid_bot

Atentamente,

Equipo N8N

[Brent Guttmann]

I think the formula you are looking for is =ARRAYFORMULA(IF(A2:A<>"", ROUNDDOWN(100/COUNTA(A2:A), 0), ""))

Since I don’t have your exact version or full context, I didn’t go too deep — but I did add a custom toolbar menu to generate the form. When the form is created, a new sheet is also generated to store the responses automatically.

Pretty sure all you needed was that formula, but feel free to give this version a try and see if it works for you.

https://docs.google.com/spreadsheets/d/1h31dEtiEntYtApr1IrZ4IU06HW0izHvmH7do-MlbG7Y/edit?usp=sharing

function onOpen() {

  SpreadsheetApp.getUi()

    .createMenu('Quiz Tools')

    .addItem('Generate Form', 'createQuizFromSheet')

    .addItem('Export as CSV', 'exportResultsData')

    .addToUi();

}

function createQuizFromSheet() {

  const ss         = SpreadsheetApp.getActiveSpreadsheet();

  const sheet      = ss.getActiveSheet();

  const quizTitle  = sheet.getName();

  const data       = sheet.getDataRange().getValues().slice(1); // skip headers

  const form       = FormApp.create(quizTitle).setTitle(quizTitle);

  const folderName = `Quiz Forms - ${new Date().getFullYear()}`;

  const formFolder = getOrCreateFolderByName(folderName);

  DriveApp.getFileById(form.getId()).moveTo(formFolder);

  form.setIsQuiz(true)

    .setRequireLogin(true)

    .setLimitOneResponsePerUser(true)

    .setAllowResponseEdits(false)

    .setPublishingSummary(false)

    .setShowLinkToRespondAgain(false)

    .setConfirmationMessage("Your response has been recorded.")

    .setShuffleQuestions(false);

  form.addTextItem().setTitle("Full Name").setRequired(true);

  const correctAnswerMap = {};

  const questionTitles   = [];

  data.forEach(row => {

    const question     = (row[0] || '').toString().trim();

    const choices      = row.slice(1, 5).map(c => (c || '').toString().trim());

    const correctChar  = (row[5] || '').toString().trim().toUpperCase();

    const points       = Number(row[6]) || 0;

    const correctIndex = ['A', 'B', 'C', 'D'].indexOf(correctChar);

    if (!question || correctIndex === -1 || !choices[correctIndex]) return;

    const item = form.addMultipleChoiceItem().setTitle(question);

    const opts = choices.map((choice, idx) =>

      choice ? item.createChoice(choice, idx === correctIndex) : null

    ).filter(Boolean);

    item.setChoices(opts).setPoints(points);

    correctAnswerMap[question] = choices[correctIndex];

    questionTitles.push(question);

  });

  // Store correct answers in script properties

  PropertiesService.getDocumentProperties().setProperty(quizTitle, JSON.stringify(correctAnswerMap));

  const resultsSheetName = `${quizTitle} - Results`;

  const resultsSheet     = ss.getSheetByName(resultsSheetName) || ss.insertSheet(resultsSheetName);

  resultsSheet.clear();

  const publicUrl = form.getPublishedUrl();

  const timestamp = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), "yyyy-MM-dd HH:mm");

  resultsSheet.getRange("A1:B1").merge().setValue(`${quizTitle} - Generated ${timestamp}`);

  resultsSheet.getRange("C1:G1").merge()

  resultsSheet.getRange("C1").setValue(publicUrl);

  resultsSheet.getRange("A1:G1")

    .setFontSize(8)

    .setWrap(false)

    .setVerticalAlignment("middle");

  const allHeaders = ["Name", ...questionTitles, "Correct Count", "Wrong Count"];

  resultsSheet.getRange(2, 1, 1, allHeaders.length).setValues([allHeaders]);

  resultsSheet.getRange(2, 1, 1, allHeaders.length)

    .setFontWeight("bold")

    .setBackground("#f1f1f1");

  resultsSheet.setColumnWidths(2, 16, 133);

  resultsSheet.setColumnWidth(1, 139);

  resultsSheet.getRange('B2:Q2').setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)

  resultsSheet.getRange("2:2").setVerticalAlignment('bottom');

  resultsSheet.setRowHeight(2, 51);

  resultsSheet.setFrozenRows(2);

  resultsSheet.getRange(3, 1, resultsSheet.getMaxRows() - 2, allHeaders.length).setWrap(true);

  resultsSheet.getRange("1:1").setWrap(false);

  applyZebraStriping(resultsSheet, allHeaders.length);

  ScriptApp.newTrigger("syncQuizResponsesToSheet")

    .forForm(form)

    .onFormSubmit()

    .create();

}

function syncQuizResponsesToSheet(e) {

  const response     = e.response;

  const items        = response.getItemResponses();

  const formTitle    = e.source.getTitle();

  const correctMap   = JSON.parse(PropertiesService.getDocumentProperties().getProperty(formTitle) || "{}");

  const ss           = SpreadsheetApp.getActiveSpreadsheet();

  const sheet        = ss.getSheetByName(`${formTitle} - Results`);

  const headers      = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues()[0];

  let name           = "";

  const answers      = [];

  let correctCount   = 0;

  items.forEach((item, i) => {

    const title  = item.getItem().getTitle();

    const answer = item.getResponse();

    if (i === 0 && headers[0] === "Name") {

      name = answer;

    } else {

      const correctAns = correctMap[title];

      const isCorrect  = answer === correctAns;

      answers.push((isCorrect ? "✅" : "❌") + " " + answer);

      if (isCorrect) correctCount++;

    }

  });

  const wrongCount = answers.length - correctCount;

  const row = [name, ...answers, correctCount, wrongCount];

  sheet.appendRow(row);

}

function getOrCreateFolderByName(name) {

  const folders = DriveApp.getFoldersByName(name);

  return folders.hasNext() ? folders.next() : DriveApp.createFolder(name);

}

function applyZebraStriping(sheet, colCount) {

  const range = sheet.getRange(3, 1, sheet.getMaxRows() - 2, colCount);

  range.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY).setHeaderRowColor("#f1f1f1");

}

function exportResultsData() {

  const sheet   = SpreadsheetApp.getActiveSheet();

  const ui      = SpreadsheetApp.getUi();

  const lastRow = sheet.getLastRow();

  const lastCol = sheet.getLastColumn();

  try {

    if (lastRow < 3) throw new Error("No data to export.");

    const headers = sheet.getRange(2, 1, 1, lastCol).getValues()[0];

    const data    = sheet.getRange(3, 1, lastRow - 2, lastCol).getValues();

    const csv     = [headers, ...data].map(row => row.join(",")).join("\n");

    const blob    = Utilities.newBlob(csv, "text/csv", `${sheet.getName()}.csv`);

    DriveApp.createFile(blob);

    ui.toast("CSV export complete.");

  } catch (err) {

    ui.alert("Export Failed", err.message, ui.ButtonSet.OK);

  }

}

// FORMULA FOR CELL G2: =ARRAYFORMULA(IF(A2:A<>"", ROUNDDOWN(100/COUNTA(A2:A), 0), ""))