How can I transfer data from Google Sheets to the Google Forms drop-down list?


Thanks for the spell check. Unfortunately, this is a direct copy and paste of the developer.google.com codes. You will probably have to correct your website.

Yes, it's possible

This can be done via the Google app script .setChoiceValues(values)

The full script can be found here https://developers.google.com/apps-script/reference/forms/list-item#setchoicevaluesvalues

Sample code:

var item = form.addListItem();
item.setTitle('Do you prefer cats or dogs?')
    .setChoices((
        item.createChoice('Cats'),
        item.createChoice('Dogs')

Instead of entering the actual choices as in the code example above (cats) in this parenthesized part of the script, instead use a range of cells and columns (4,3) that points to the data area on the sheet in which your dynamic dropdown is located – Choices are located As shown here, this code points to an active sheet. However, you can change the code to get ranges of cells by referring to the Sheet ID> instead

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()(0);

var range = sheet.getRange("B2:D4");

// The row and column here are relative to the range
// getCell(1,1) in this code returns the cell at B2, B2
var cell = range.getCell(1, 1);
Logger.log(cell.getValue());

You can choose which drop-down list items to choose by referencing the range of Google Sheet cells to which the form is linked, or another sheet by referring to the Google Sheet ID and sheet name where yours are located Dropdown selection. You can get the sheet ID by looking at the web link of your Google sheet in your browser. It's the long string and number, or how to get the leaf ID from the code> https://developers.google.com/apps-script/reference/. Spreadsheet / Spreadsheet # getid

Done once. To update the form automatically, use leaf or form triggers to call this update list script so that the form is updated with new options after reloading.

I recently did something similar – automatically filling out and automatically updating a Google form with data values ​​from the Google sheet after a response has been sent. Here is the working live example form: https://support.google.com/docs/thread/ 48344518? Hl = en