How to google form with multiple sections, each section with a dropdown list, populate from Google sheet

Question

I have a google form with multiple sections, each section with a dropdown list. I wish to pull the data for the dropdown lists from spreadsheet with matching name.

Google Form

Google Sheet

Execution log 4:42:11 PM Notice Execution started 4:42:18 PM Error
Exception: Questions cannot have duplicate choice values. (anonymous) @ Code.gs:23 populateGoogleForms @ Code.gs:17

function getDataFromGoogleSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(`your text`);
  const sheet = ss.getSheetByName("FormList");
  const [hA, ...rows] = sheet.getDataRange().getDisplayValues();
  const cols = {};
  const col={};
  const idx={};
  hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]).filter(e=>e);col[h]=i+1;idx[h]=i; });
  return cols;
}

function populateGoogleForms() {
  const GOOGLE_FORM_ID = `1cuPfuzzoEPEaIliiRKDqU`your text`vZ3dq4vawDPLERZYMUwpZQ`;
  const googleForm = FormApp.openById(GOOGLE_FORM_ID);
  const items = googleForm.getItems();
  const choices = getDataFromGoogleSheets();
  items.forEach(function (item) {
    const itemTitle = item.getTitle();
    if (itemTitle in choices) {
      const itemType = item.getType();
      switch (itemType) {
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(choices[itemTitle]);
          break;
        default:
          Logger.log("Ignore question", itemTitle);
      }
    }
  });
}

Answer 1

I thought that the reason for your current issue of Exception: Questions cannot have duplicate choice values. might be due to the values of const choices = getDataFromGoogleSheets();. When the values of each array have duplicated values, such an error occurs at setChoiceValues. In order to remove this, when your script is modified, as a simple modification, how about the following modification? Please modify your getDataFromGoogleSheets() as follows.

From:

hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]).filter(e=>e);col[h]=i+1;idx[h]=i; });

To:

hA.forEach((h, i) => { cols[h] = [...new Set(rows.map(r => r[i]).filter(e => e))]; col[h] = i + 1; idx[h] = i; });

• In this modification, the duplicated values are removed with Set.

• As another modification, how about modifying getDataFromGoogleSheets() as follows?

  function getDataFromGoogleSheets() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName("FormList");
    const values = sheet.getDataRange().getDisplayValues();
    const cols = Object.fromEntries(values[0].map((_, col) => {
      const [h, ...v] = values.flatMap(row => row[col] || []);
      return [h, [...new Set(v)]];
    }));
    return cols;
  }
  

• By the way, in your script, at const ss = SpreadsheetApp.getActiveSpreadsheet(your text);, an argument is used like your text. In this case, getActiveSpreadsheet has no arguments. Please be careful about this.