Apps Script: Generate Google Form Items from Google Sheet Rows

Typing hundreds of quiz questions into a Google Form by hand is a miserable way to spend an afternoon.

It is also completely unnecessary.

Google Apps Script lets you treat a simple spreadsheet as a database for your assessments and surveys.

With a few lines of JavaScript, you can map rows of text directly into multiple-choice options, checkboxes, and text fields.

Here is how to write a script that pulls your question data from a Google Sheet and builds the form automatically.

Why generate Google Form items from spreadsheet rows?

Building forms through the visual interface works fine for a ten-question survey, but it breaks down at scale. Moving your form structure into a spreadsheet offers strict structural advantages.

• Bulk assessment creation: Teachers and corporate trainers can maintain massive question banks in a single spreadsheet. They can filter rows by subject or difficulty, copy them to a new sheet, and generate specific quizzes on demand.

• Standardized checklists: Safety and compliance teams regularly update inspection criteria. By keeping the master list in a spreadsheet, they can push changes to field forms automatically without manually clicking through the form UI to find the right section.

• Dynamic onboarding: HR departments juggle different intake requirements for different roles. A script allows them to generate tailored onboarding forms instantly by pulling only the rows relevant to a new hire's specific department.

• Version control: A spreadsheet gives you a visible, trackable history of every question, typo fix, and answer key update. The native Google Forms interface hides this history entirely.

• Collaborative drafting: Multiple subject matter experts can review and edit spreadsheet rows simultaneously. Co-editing a live Google Form often results in people accidentally overwriting each other's work or deleting options.

How should you structure your Google Sheet for question data?

A script is only as reliable as the data you feed it. Apps Script cannot guess what a row means.

Before writing any code, you need a strict, predictable column layout. This structure tells the script exactly what type of form element to create and what text to populate.

Freeze your header row so it does not get processed as a question.

Ensure your Item Type column uses exact, uppercase strings that your code will recognize (like TEXT, MULTIPLE_CHOICE, or CHECKBOX).

Formatting your options requires discipline. If your stakeholders submit messy data, the script will create messy form items.

• ❌ Weak: Sales - Marketing - IT - Operations (and maybe HR)

• ✅ Strong: Sales, Marketing, IT, Operations, HR

If you regularly receive raw briefs from stakeholders that are completely unstructured, standardizing them into this format manually can still take time. In those cases, converting a document to a Google Form using parsing tools might bypass the spreadsheet step entirely.

For a pure Apps Script solution, strict data hygiene in these five columns is mandatory.

How do you access and configure the Apps Script editor?

Google hides the Apps Script environment behind a few menus, but it is natively available in every Google Sheet. You do not need to install external add-ons or configure a local development environment.

1. Open your active workspace: Open the Google Sheet containing your structured question data.

2. Launch the editor: Click Extensions in the top menu bar, then select Apps Script.

3. Initialize the workspace: Wait for the new browser tab to load the script editor interface.

4. Name the project: Click Untitled project in the top left corner and rename it to something recognizable, like Form Generator Script.

5. Clear the canvas: Delete the placeholder function myFunction() {} block from the Code.gs file so you have a completely blank screen.

The first time you run any code here, Google will pause execution and prompt you to authorize the script.

Click Review permissions, select your active Google account, click Advanced, and click the link to proceed. This grants your script the OAuth scopes needed to read your Sheets and create files in your Drive.

What is the Apps Script code to create a form from sheet rows?

The core logic relies on reading the active spreadsheet, iterating through the data array, and appending the corresponding item to a new Google Form.

Copy and paste the following JavaScript into your empty Code.gs file.

function generateFormFromSheet() {
  // 1. Connect to the active spreadsheet and read all data
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  // 2. Create a new Google Form in Drive
  const form = FormApp.create('Automated Intake Form');
  form.setDescription('Generated automatically from Google Sheets data.');
  
  // 3. Loop through the rows (starting at index 1 to skip headers)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    
    // 4. Map the columns to variables (adjust indices based on your sheet)
    const title = row[0];
    const type = row[1];
    const optionsString = row[2];
    const isRequired = row[3];
    const helpText = row[4];
    
    // Skip empty rows
    if (!title) continue;
    
    let item;
    
    // 5. Evaluate the type and create the corresponding form item
    switch (type.toUpperCase()) {
      case 'TEXT':
        item = form.addTextItem();
        break;
        
      case 'PARAGRAPH':
        item = form.addParagraphTextItem();
        break;
        
      case 'MULTIPLE_CHOICE':
        item = form.addMultipleChoiceItem();
        if (optionsString) {
          // Split by comma and trim whitespace from each option
          const choices = optionsString.split(',').map(opt => opt.trim());
          item.setChoiceValues(choices);
        }
        break;
        
      case 'CHECKBOX':
        item = form.addCheckboxItem();
        if (optionsString) {
          const choices = optionsString.split(',').map(opt => opt.trim());
          item.setChoiceValues(choices);
        }
        break;
        
      default:
        Logger.log(`Unsupported item type: ${type} for question: ${title}`);
        continue;
    }
    
    // 6. Apply universal settings like title, help text, and requirement status
    item.setTitle(title);
    
    if (helpText) {
      item.setHelpText(helpText);
    }
    
    if (isRequired === true || isRequired.toString().toUpperCase() === 'TRUE') {
      item.setRequired(true);
    }
  }
  
  // 7. Output the link to the new form in the execution log
  Logger.log('Form created successfully!');
  Logger.log('Edit URL: ' + form.getEditUrl());
}

Here is exactly what this script does, broken down by its configuration steps:

• Data retrieval: sheet.getDataRange().getValues() pulls every cell with data into a two-dimensional JavaScript array in one single API call. This prevents the script from timing out, which often happens if you try to read cells one by one inside a loop.

• Form initialization: FormApp.create('Automated Intake Form') initializes a brand new form in your Drive root folder. It immediately returns a Form object that we can manipulate.

• Iteration logic: for (let i = 1; i < data.length; i++) starts our loop at the second row. Index 0 holds the column headers, which we do not want to turn into a question.

• Routing via switch statement: switch (type.toUpperCase()) looks at your Item Type column. Depending on the text, it routes the logic to the correct Google Form method, like addTextItem() or addCheckboxItem().

• Array mapping: optionsString.split(',').map(opt => opt.trim()) handles the comma-separated options. It splits the string into an array and removes any accidental spaces users typed before or after the commas.

• Universal properties: Regardless of the item type created, the script applies the setTitle(), setHelpText(), and setRequired() methods at the end of the loop so you do not have to write them repeatedly inside every switch case.

How do you prevent duplicate questions on script reruns?

If you run the creation script above twice, you will end up with two entirely separate forms in your Google Drive.

If you modify the script to attach to an existing form using FormApp.openById(), running it twice will append the same questions to the bottom, creating duplicates.

To make your script idempotent - meaning it safely yields the exact same result no matter how many times you click run - you need an architectural safeguard.

Step 1: The wipe-and-replace approach

This is the simplest way to enforce idempotency if you do not care about preserving past form responses.

Before the script enters the for loop, you instruct it to delete everything currently on the form.

You do this by calling form.getItems() to retrieve an array of all current questions.

Then, loop through that array backwards and call form.deleteItem(item) on each one. Looping backwards is critical; if you loop forwards and delete item 0, item 1 shifts into the 0 slot, and your loop will skip items.

Once the form is empty, the script proceeds to rebuild the questions fresh from the spreadsheet rows.

Step 2: The ID mapping approach

Wiping a form orphans any existing response data associated with those deleted questions. To preserve response data, your script must update existing items instead of replacing them.

Add a new column to your spreadsheet called Form Item ID.

When the script runs, it checks if this column contains a value.

If the cell is empty, the script creates a new item, gets its unique ID using item.getId(), and uses sheet.getRange().setValue() to write that ID back to the spreadsheet cell.

If the cell already has an ID, the script calls form.getItemById(id).

Instead of calling an add method, it casts the retrieved item to its specific class (e.g., .asMultipleChoiceItem()) and updates the title and options using .setTitle() and .setChoiceValues().

How do you troubleshoot common Apps Script execution errors?

Writing Apps Script means dealing with Google's specific API quirks. When your script fails, the error messages are not always intuitive.

If you frequently encounter parsing errors because stakeholders submit messy text that breaks your split(',') logic, you might need a different workflow entirely.

Instead of forcing raw text into a strict spreadsheet, you can generate a Google Form from a description using tools that parse natural language without relying on strict comma separation.

What are the Google API quotas and limitations for bulk creation?

Google Apps Script is a free platform, but it operates under strict server quotas to prevent abuse and manage server load.

If you are building massive assessments, syncing thousands of rows, or running this script automatically on a daily trigger, you need to understand your limits.

The caps differ slightly depending on whether you use a free consumer Gmail account or a paid Workspace tier.

Expert tip: If you must generate a form with hundreds of questions that approaches the 6-minute timeout, use PropertiesService to store your current row index. Terminate the script at 5 minutes, and use a time-driven trigger to resume exactly where it left off on the next run.

Keep in mind that while there is no hard cap on the number of questions a Google Form can hold, browser performance degrades severely after 100 items.

The Form editor will become sluggish, and respondents on older mobile devices may experience crashes.

If your spreadsheet contains 500 rows, design your script to split the output across multiple forms rather than creating one massive document.

FAQ

Can I update existing form questions using this script instead of creating new ones?

Yes, as long as you store the unique Item ID for each question in your spreadsheet when it is first created. Your script can use FormApp.openById(formId).getItemById(itemId) to fetch the exact question. From there, you cast it to its specific type (like .asMultipleChoiceItem()) and use .setTitle() to overwrite the existing text without deleting the underlying response history.

How do I add multiple-choice options dynamically from a comma-separated cell?

Read the cell value as a standard text string, then use the JavaScript .split(',') method to convert it into an array of strings. Because users often add inconsistent spaces after commas, map over the array with .trim() to clean up the formatting. Pass that final, clean array directly into the .setChoiceValues() method of your multiple-choice item.

Can this script automatically turn the generated form into a self-grading quiz?

Yes, you can enable quiz mode programmatically by adding form.setIsQuiz(true) immediately after creating the form object. To set points and correct answers, cast your generated item to the correct type and use .setPoints(10). You will also need to build a FormApp.createChoice(value, isCorrect) array to explicitly tell the script which options represent the right answer.

How do I handle section headers and page breaks using Apps Script?

You manage these just like regular questions by adding dedicated rows in your spreadsheet with a specific type, like SECTION or PAGE_BREAK. In your script's switch statement, map these types to form.addSectionHeaderItem() and form.addPageBreakItem(). You can then use .setTitle() and .setHelpText() on the section header item to populate the instructions for that new page.

Automating Google Forms through Apps Script bridges the gap between raw spreadsheet data and user-friendly data collection. By keeping your questions in a centralized sheet, you gain version control, easy bulk editing, and a clean backup of your assessments. If writing JavaScript and managing execution timeouts feels like too much overhead for your team, try Doc2Form to instantly convert your existing documents and briefs into ready-to-use forms. Otherwise, take the code above, paste it into your editor, and let the script handle the repetitive data entry for you.