如何从Google App脚本获取自动刷新Google电子表格自定义单元格功能[自定义功能刷新] [英] How to get auto refresh google spread sheet custom cell fuction from google app script [ custom fuction refresh ]

问题描述

在制作新工作表或更改工作表名称或复制工作表或从Google电子表格中删除工作表时，如何通过Google应用程序脚本自动刷新工作表名称的当前列表

how to get current list of sheet names automatically refresh by google app script when making new sheets or changing sheet name or duplicaing sheets or deleting sheets from google spread sheet

:::::::我需要工作表名称列表::::::::::::

:::::: I need list of sheets name ::::::::::::

1. 有很多张纸
2. 新工作表将由其他用户添加
3. 新工作表的名称将由其他用户更改
4. 某些工作表将被其他用户删除
5. 我需要不存在的现有工作表名称列表

::::::::::::::::::::::::::::::::::::::::

::::::::::::::::::::::::::::::::::::::::

，工作表名称列表应显示在第二个工作表上，代码表达式为工作表[1]

and the list of sheet name should display on second sheet that code expression is sheet[1]

下面的代码运行良好.但不能通过添加工作表或删除工作表来刷新

below code are working well. but it's not refresh by adding sheets or deleting sheets

function sheetnames()
{
 return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function(x) {return x.getName();});
}

推荐答案

我相信您的情况和目标如下.

I believe your situation and goal as follows.

• 您将sheetnames()函数用作Google Spreadsheet上的自定义函数.
• 您已经确认sheetnames()的功能正常.
• 您要在删除，插入，复制工作表并更改工作表名称时刷新自定义功能.

• You are using the function of sheetnames() as the custom function on Google Spreadsheet.
• You have already confirmed that your function of sheetnames() works.
• You want to refresh the custom function when the sheet is deleted, inserted, copied and the sheet name is changed.

为了达到上述目的，我想提出以下方法.

In order to achieve above, I would like to propose the following method.

在这种情况下，用于刷新电子表格中sheetnames()的自定义功能的示例脚本由OnChange事件触发器运行.为此，请将以下示例脚本复制并粘贴到Spreadsheet的容器​​绑定脚本中，然后保存该脚本.

In this case, the sample script for refreshing the custom function of sheetnames() in the Spreadsheet is run by the OnChange event trigger. For this, please copy and paste the following sample script to the container-bound script of Spreadsheet, and save the script.

function onChange(e) {
  var lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      const prop = PropertiesService.getScriptProperties();
      if ((e.changeType === "OTHER" || e.changeType === "REMOVE_GRID" || e.changeType === "INSERT_GRID") && !prop.getProperty("run")) {
        const formula = "=sheetnames";  // <--- Please set the function name of the custom function.
        const ss = e.source;
        const tempFormula = "=sampleFormula";
        ss.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
        ss.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
        prop.setProperty("run", "done");
      } else {
        prop.deleteProperty("run");
      }
    } catch(e) {
      throw new Error(e);
    } finally {
      lock.releaseLock();
    }
  }
}

• 为了避免脚本的重复运行，请使用LockService.
• 为了避免触发的无限循环，请使用PropertiesService.
• In order to avoid the duplicate run of the script, LockService is used.
• In order to avoid the infinite loop of the trigger, PropertiesService is used.

为了执行onChange的功能，请将OnChange事件触发器安装到功能onChange.您可以在此官方文档中查看安装方法.

In order to execute the function of onChange, please install the OnChange event trigger to the function onChange. You can see the method for installing this at this official document.

为了测试以上脚本，例如，在安装功能onChange作为可安装的OnChange事件触发器之后，请插入新的工作表.这样，您可以确认自定义功能sheetnames()已刷新.

In order to test above script, after you installed the function onChange as the installable OnChange event trigger, for example, please insert new sheet. By this, you can confirm the custom function sheetnames() is refreshed.

• 可安装的触发器
• Class TextFinder
• 锁定服务
• 类PropertiesService
• Installable Triggers
• Class TextFinder
• Lock Service
• Class PropertiesService

这篇关于如何从Google App脚本获取自动刷新Google电子表格自定义单元格功能[自定义功能刷新]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！