CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
chillsky
chillsky

Reputation: 5

Google AppScript - Update google Form using appscript

I have the following scenario

I currently update my form using appscript which is working fine however I have this problem , my form has question to select a country (from drop down list of countries ) and each option has each section mapped , (say if I select country as Netherlands whilst filling the form , it will take me to next section to select the town in Netherlands in the specified section ), so what happens now is whenever there is new country added in the sheet its updated in the form via below script and it works fine but it resets the section map that is set for each country , so we have to redo the section based answer again ! wanted to understand is there any way that to avoid resetting the section based options thats set.

Code

var ssID = "sheet ID"; //Global Variable sheet ID

// This is start of function1 to update form Monthly 
function formUpdateCC() {
  // call your form and connect to the drop-down item
  var form = FormApp.openById("form ID");

  var country = form.getItemById("ItemID").asListItem();

  // identify the sheet where the data resides needed to populate the drop-down - Countrylist
  var countrysheet = SpreadsheetApp.openById(ssID).getSheetByName("Country");

  // grab the values in the first column of the country tab in sheet - use 2 to skip header row
  var countrylist = countrysheet.getRange(2, 1, countrysheet.getMaxRows() - 1).getValues();

  var data = [];

  // convert the array ignoring empty cells
  for (var i = 0; i < countrylist.length; i++)
    if (countrylist[i][0] != "")
      data[i] = countrylist[i][0];

  country.setChoiceValues(data);

}

Upvotes: 0

Views: 186

Answers (1)

WilcoSoft
WilcoSoft

Reputation: 146

You can use setChoices with createChoice(value, navigationItem) instead of setChoiceValues to add navigation to choices. Create a new column (in this example, it's column B) with the section ID for the section you'd like to navigate for that country. Then use the code below to update the form.

  var form = FormApp.openById("Form Id");
  var country = form.getItemById("List Id").asListItem();

  // identify the sheet where the data resides needed to populate the drop-down - Countrylist
  var countrysheet = SpreadsheetApp.openById(ssID).getSheetByName("Country");

  // create a new column next to the country name for its target pagebreak ID from the form
  // grab the values in the first & second columns of the country tab in sheet - use 2 to skip header row
  var countrylist = countrysheet.getRange(2, 1, countrysheet.getMaxRows() - 1).getValues();
  var targetlist = countrysheet.getRange(2, 2, countrysheet.getMaxRows() - 1).getValues();

  var data = [];

  // convert the array ignoring empty cells
  var s = 0;
  for (var i = 0; i < countrylist.length; i++)
    if (countrylist[i][0] != "") {
  // use createChoice(value, navigationItem) to add the section navigation
      data[s] = country.createChoice(countrylist[i][0], form.getItemById(targetlist[i][0]).asPageBreakItem());
      s++;
    }

  country.setChoices(data);

Upvotes: 2

Related Questions