Populate bulk data from google spreadsheet into google form dropdown list

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.

This is the script i run but it doesn't seems to be working.

function getDataFromGoogleSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("DATA");
const [header, ...data] = sheet.getDataRange().getDisplayValues();
const choices = {}
header.forEach(function(title, index) {
  choices[title] = data.map(row => row[index]).filter(e => e !== "");
});
return choices;
}

function populateGoogleForms() {
  const GOOGLE_FORM_ID = "1nsDQ6MtdCci-g5XgLxJ-4XNJ19E9sDz42G6DoFLwiFE";
  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.CHECKBOX:
          item.asCheckboxItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues(choices[itemTitle]);
          break;
        default:
        Logger.log("Ignore question", itemTitle);
       }
    }
  });
}

question from:https://stackoverflow.com/questions/65849834/populate-bulk-data-from-google-spreadsheet-into-google-form-dropdown-list

Answer 1

I already had multiple choice form and so I just renamed the headings and generated some data (see table below). I played around with your code because I'd never seen a declaration like in the fourth line. Pretty cool thanks. I tried you code on my form which I created manually and to my surprise it worked the first time.

function getDataFromGoogleSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const [hA, ...rows] = sheet.getDataRange().getDisplayValues();
  const cols = {};//just made some minor changes to fit my personal likes in labeling
  const col={};
  const idx={};
  hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]);col[h]=i+1;idx[h]=i; });
  return cols;
}

function populateGoogleForms() {
  const GOOGLE_FORM_ID = getGlobal('formid');//Have the id stored in a spreaddsheet.  Other than that though it's exactly the same code
  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.CHECKBOX:
          item.asCheckboxItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues(choices[itemTitle]);
          break;

        default:
          Logger.log("Ignore question", itemTitle);
      }
    }
  });
}

Data Sheet:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
0	0	1	2	0	1	2	1	0	0
2	2	2	1	1	2	2	2	2	2
0	1	1	0	0	0	0	0	0	1