Search code examples
javascriptgoogle-apps-scriptgoogle-sheetssynchronizationgoogle-forms

getSheets() Google Apps Script multiple calls issue


(For the purposes of this post, I will refer to each "sheet" in a google spreadsheet as a tab, I find the naming convention to be confusing when reading)

In a Google Sheets add-on, I am attempting to create a setup process that will allow the user to enter the link of a Google Form based on a prompt for that form (the reason I don't just pull all the ID's into the code manually is that the forms themselves being used may change from user to user), and have that form's responses be linked to the spreadsheet, then rename that tab with a pre-assigned name.

For whatever reason, whenever I call the getSheets() function more than once, the return value from the call is the same as the whatever the return was from the first call. For example, if I had 3 tabs, named "first, second, third" and I ran the attached code, sheetList correctly is returned as [first, second, third]. But, say during the for loop, a tab is added (as a result of linking a form), and I then call getSheets() again to get the new first tab (should be the linked form data), what returns is still [first]

Not sure where to go from here, is this a problem with apps script? Or my approach? Any tips or advice would be appreciated.

    const separateSheetNames = [
    "English Household Information",
    "English Camper Information",
    "English Parent Signatures",
    "English Household Number Recovery",
    "Spanish Household Information",
    "Spanish Camper Information",
    "Spanish Parent Signatures",
    "Spanish Household Number Recovery"
  ];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetList = []
  ss.getSheets().forEach(function(val){sheetList.push(val.getName())});

  for (i=0;i<separateSheetNames.length;i++){
    var ui = SpreadsheetApp.getUi();
    var currentSheetName = separateSheetNames[i]
    var isAlreadySheet = sheetList.indexOf(currentSheetName)

    if (isAlreadySheet == -1){
      var formUrl = ui.prompt('Paste the link for the ' + currentSheetName + ' form').getResponseText()
      var form = FormApp.openByUrl(formUrl)
      const ssId = ss.getId();
      form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId)
      var firstsheet = ss.getSheets()[0]
      console.log('firstSheetName ' + firstsheet.getName())
      firstsheet.setName(currentSheetName)
      var currentSheetLastCol = ss.getSheetByName(currentSheetName).getLastColumn()+1
      ss.getSheetByName(currentSheetName).getRange(1,currentSheetLastCol).setValue('Row in Combined Data')
    }
  }

Solution

  • Leaving this up for anyone else who might run into a similar issue- the solution offered by @TheMaster worked perfectly:

    try SpreadsheetApp.flush() before getSheets() call.