从Google表格生成Google表单：创建答案密钥时出错

Issue Analysis #

The original code collects correct answers into a flat array, assuming entries are ordered by question index. But since it iterates spreadsheet rows first, correct answer entries don’t align with question order when processing 3+ questions, leading to mismatched answer keys.

Fixed Code #

function getDataFromGoogleSheets() {
  const correctColor = "#ff0000"; // Set your correct answer text color here

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('test1');
  const range = sheet.getDataRange();
  const ar = range.getDisplayValues();
  
  // Convert columns to question-option structure: [question title, [option1, option2, ...]]
  const choices = ar[0].map((_, col) => ar.map(row => row[col])).map(([h, ...v]) => [h, v]);
  
  // Create a map: key = question index (column), value = correct option index (0-based)
  const correctMap = range.getTextStyles().reduce((map, rowStyles, rowIndex) => {
    rowStyles.forEach((cellStyle, colIndex) => {
      if (cellStyle.getForegroundColorObject().asRgbColor().asHexString() === correctColor) {
        // rowIndex -1 skips the header row (options start at row 1)
        map[colIndex] = rowIndex - 1;
      }
    });
    return map;
  }, {});

  return { choices, correctMap };
}

function myFunction1() {
  const { choices, correctMap } = getDataFromGoogleSheets();
  const form = FormApp.create('New Form');
  form.setIsQuiz(true);
  
  choices.forEach(([questionTitle, options], questionIndex) => {
    const question = form.addMultipleChoiceItem()
      .setTitle(questionTitle)
      .showOtherOption(true);
    
    // Create choices with correct answer marked via correctMap lookup
    const formChoices = options.map((optionText, optionIndex) => {
      return question.createChoice(optionText, optionIndex === correctMap[questionIndex]);
    });
    
    question.setChoices(formChoices);
  });
}

Key Changes #

1. Correct Answer Mapping: Replaced the flat correct array with correctMap, an object that directly maps each question’s column index to its correct option index. This eliminates order-dependent errors.
2. Accurate Answer Matching: In the form creation loop, we use correctMap[questionIndex] to instantly retrieve the correct option for the current question, ensuring consistent, correct answer key assignment.

Spreadsheet Structure Reminder #

• First row: Question titles (each column = one question)
• Rows 2+: Options for each question (each column’s rows = options for that question)
• Correct option text must use the hex color specified in correctColor (default: red #ff0000)

内容的提问来源于stack exchange，提问作者seed13