getSheets() Google Apps 脚本多次调用问题 [英] 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)

在 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:

在 getSheets() 调用之前尝试 SpreadsheetApp.flush().

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

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