getSheets()Google Apps脚本多次调用问题 [英] getSheets() Google Apps Script multiple calls issue

问题描述

(出于这篇文章的目的，我将Google电子表格中的每个工作表"都称为标签，我发现阅读时的命名约定令人困惑)

(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)

在Google表格加载项中，我尝试创建一个设置过程，该过程将允许用户根据该表单的提示输入Google表单的链接(我不只是拉动所有手动将ID放入代码中是，所使用的表单本身可能会因用户而异)，并将表单的响应链接到电子表格，然后使用预先分配的名称重命名该标签.

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.

无论出于何种原因，每当我多次调用getSheets()函数时，该调用的返回值都将与第一次调用的返回值相同.例如，如果我有3个选项卡，分别名为第一，第二，第三"，并且运行了所附的代码，则正确地将sheetList返回为[first, second, third].但是，假设在for循环中，添加了一个选项卡(作为链接表单的结果)，然后我再次调用getSheets()以获取新的第一个选项卡(应该是链接的表单数据)，返回的内容是仍然[first]

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')
    }
  }

推荐答案

将其留给可能遇到类似问题的其他人使用-@TheMaster提供的解决方案非常有效:

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

尝试SpreadsheetApp.flush().

这篇关于getSheets()Google Apps脚本多次调用问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！