google-sheetsgoogle-apps-scriptgoogle-forms

Populate multiple forms from single sheet


Not a coder. I've copy/pasted code from other sources to get a basic foundation, but I need help adding functionality.

I have a single Google Sheet with data in the "events" tab that I would like to use to automatically populate a dropdown menu in multiple different Google Forms.

The code I'm using is only setup for a single Form, and I need guidance in setting up the code so I can add more forms in the future to automatically populate.

The first form ID is 1jDopSo1ofeZpy_5rhL--bg3DpInidz2TtBmWfgKLfiw

The second form ID (which needs to be added to the code) is 1DhnRIqvFt0z4LXjKB0JI3Yl7zeUaHyQchBKTjDvMvj0

function updateDropdown() {
  // Get the form and the spreadsheet
  var form = FormApp.openById('1jDopSo1ofeZpy_5rhL--bg3DpInidz2TtBmWfgKLfiw');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('events');  

  // Get the data from the spreadsheet
  var data = sheet.getRange('A1:A').getValues();
  var items = data.flat().filter(String);  

  // Put the dropdown question in the form
  var formItems = form.getItems(FormApp.ItemType.LIST);
  var listItem = formItems[0].asListItem();  

  // Update the dropdown options
  listItem.setChoiceValues(items);

}

I found similar questions

but I'm not experienced enough to transpose either solution to my particular situation.


Solution

  • You will need to place the URL of each form into a list on sheet. Next get this list an an array and loop through opening each form using FormApp.openByURL(URL), updating the dropdown on each form.

    function updateDropdown() {
     
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('events');  
    
      // Get the data from the spreadsheet
      var data = sheet.getRange('A1:A').getValues();
      var items = data.flat().filter(String);  
      
      //Get the list of form URLs from the spreadsheet in Column D for example
      var formLinks = sheet.getRange('D1:D').getDisplayValues()
      var formURLs = formlinks.flat().filter(string);
      
      for (var x = 0; x < formURLs.length; x++){
        //Open the form 
        var formToUpdate = FormApp.openByUrl(formURLs[x]);
        
        // Put the dropdown question in the form
        var formItems = formToUpdate.getItems(FormApp.ItemType.LIST);
        var listItem = formItems[0].asListItem(); 
         
        // Update the dropdown options
        listItem.setChoiceValues(items);
        
      }
    
    
    }