// Forms · Sheets · Apps Script

Create a form from spreadsheet data in Google Forms.

Use Google Apps Script to read a spreadsheet and programmatically build a Google Form, including how to filter blank cells that cause setChoiceValues() to throw 'Invalid data updating form'.

“I want to write an Apps Script that reads question labels and choice options from a spreadsheet and builds a Google Form automatically, without manually re-entering the data.”

The script

copy · paste · trigger

buildFormFromSheet.gs

Apps Script

// buildFormFromSheet — reads col A (question) + cols B-Z (choices) per row
// Skips rows with no question label; trims and filters blank choice cells
function buildFormFromSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Questions');
  var form  = FormApp.create('Generated Form');
  var data  = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    var row      = data[i];
    var label    = String(row[0]).trim();
    if (!label) continue;

    var choices  = row.slice(1)
                      .map(function(c) { return String(c).trim(); })
                      .filter(function(c) { return c !== ''; });

    var item = form.addMultipleChoiceItem();
    item.setTitle(label);
    if (choices.length > 0) {
      item.setChoiceValues(choices);
    }
  }

  Logger.log('Form URL: ' + form.getEditUrl());
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Sheet layout and what the script reads

Column A holds the question label for each row. Columns B onward hold the answer choices, one per cell. Questions with no label in column A are skipped entirely, so you can leave header rows or spacer rows in the sheet without breaking the build.

The script calls sheet.getDataRange().getValues(), which returns a 2D array of raw cell values. Numbers come back as JavaScript numbers, dates as Date objects, and empty cells as empty strings. Calling String(c).trim() on every cell normalizes all of that before any comparison happens.

The blank-cell trap inside setChoiceValues()

The error message 'Invalid data updating form' is the one that wastes an hour. It appears when any element in the array you pass to setChoiceValues() is an empty string. Google Forms does not allow a choice with no text, but the error names neither the item title nor the row number — just the generic failure.

The filter step in the script (filter(function(c) { return c !== ''; })) is the entire fix. It runs after the trim, so a cell containing only spaces is also excluded. I keep this pattern in a utils file now because every sheet-driven form builder I have written hit this on the first run — the source sheet almost always has ragged rows where some questions have three choices and some have eight.

The guard if (choices.length > 0) before calling setChoiceValues() handles rows where a question label exists but every choice column is blank. Without it, calling setChoiceValues([]) throws as well. The item still gets created with no choices, which you can fill in manually or treat as a free-response item.

Running the script and finding the new form

Open the spreadsheet, go to Extensions > Apps Script, paste the function, and run buildFormFromSheet(). The first run asks for authorization to access Forms and Sheets — both are required. After authorization, run it again.

The edit URL is logged to the Apps Script Logger (View > Logs). That URL goes to the form editor, where you can preview the form, adjust settings, and share the responder link. The form is created in your Google Drive root; move it to a folder afterward if your Drive is organized.

Each run creates a new form rather than updating an existing one. If you need idempotent updates — re-running without duplicating questions — you would pass an existing form ID to FormApp.openById() instead of FormApp.create(), then clear items first with form.getItems().forEach(function(item) { form.deleteItem(item); }). That is a separate pattern; for an initial build, create() is the right call.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does setChoiceValues() throw 'Invalid data updating form' even though my data looks fine?: At least one value in the array is an empty string. Cells that appear blank in Sheets return '' from getValues(). Filter the array with .filter(function(c) { return c !== ''; }) before passing it to setChoiceValues().
Can I mix question types — some multiple choice, some checkboxes, some short answer — in the same sheet?: Yes. Add a type column (e.g., column A for type, column B for label, columns C onward for choices) and use a conditional inside the loop: if (type === 'checkbox') form.addCheckboxItem(), if (type === 'text') form.addTextItem(), and so on. Each item class has its own setChoiceValues() equivalent.
The script created the form but all questions are missing their choices. What happened?: The choices array was empty on every row, so the setChoiceValues() call was skipped. Check that your choice data starts in column B (index 1 in the row array) and that the sheet name passed to getSheetByName() matches exactly, including capitalization.
How do I add the form to a specific Google Drive folder instead of the root?: FormApp.create() always places the form in Drive root. After creating it, get a reference with DriveApp.getFileById(form.getId()), then call DriveApp.getFolderById('FOLDER_ID').addFile(file) and DriveApp.getRootFolder().removeFile(file) to move it.