是否有Google表格公式可将表格名称放入单元格中? [英] Is there a Google Sheets formula to put the name of the sheet into a cell?

问题描述

以下插图应有帮助:

推荐答案

您有2个选择，但我不确定我是否是其中两个的拥护者，但这是我的看法.您可能会有不同的感觉:

You have 2 options, and I am not sure if I am a fan of either of them, but that is my opinion. You may feel differently:

选项1:强制运行该功能.

单元格中的函数除非引用了已更改的单元格，否则不会运行.更改工作表名称不会触发电子表格中的任何功能.但是我们可以通过向其传递一个范围来强制该函数运行，并且只要该范围中的某个项目发生更改，该函数就会触发.

A function in a cell does not run unless it references a cell that has changed. Changing a sheet name does not trigger any functions in the spreadsheet. But we can force the function to run by passing a range to it and whenever an item in that range changes, the function will trigger.

您可以使用以下脚本创建一个自定义函数，该函数将检索名称:

You can use the below script to create a custom function which will retrieve the name:

function mySheetName() {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return key;
}

并在单元格中放置以下内容:

and in the cell place the following:

=mySheetName(A1:Z)

现在，如果该传递范围内的单元格中的任何值发生更改，则脚本将运行.每次更改任何值时，都需要花费一秒钟的时间来运行脚本并在单元格中设置一条消息，因此这可能会非常烦人.如前所述，它还需要更改范围以使其触发，因此对相当静态的文件没有太大帮助.

Now if any value in a cell in that passed range changes the script will run. This takes a second to run the script and sets a message in the cell each time any value is changed so this could become annoying very quickly. As already mentioned, it also requires a change in the range to cause it to trigger, so not really helpful on a fairly static file.

选项2:使用OnChange事件

虽然运行时感觉比上面的选项更好，并且这不依赖于电子表格单元格中的值更改，但我不喜欢这样做，因为它会强制名称继续.如果需要，可以使用实用程序表在各个表中定义此位置.以下是基本思路，如果您喜欢此选项，可能会帮助您入门.

While the run time feels better than the above option, and this does not depend on a value changing in the spreadsheet's cells, I do not like this because it forces where the name goes. You could use a Utilities sheet to define this location in various sheets if you wish. Below is the basic idea and may get you started if you like this option.

更改工作表名称时将触发OnChange事件.您可以使下面的代码更复杂，以检查错误，检查工作表ID以仅在给定的工作表上运行，等等.但是，基本代码是:

The OnChange event is triggered when the sheet name is changed. You can make the code below more sophisticated to check for errors, check the sheet ID to only work on a given sheet, etc. The basic code, however, is:

function setSheetName(e) {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('K1').setValue(key);
}

保存代码后，在脚本编辑器中将当前项目的更改触发"设置为此功能.它将在任何更改事件时将工作表名称写入单元格K1.要设置触发器，请在编辑菜单下选择当前项目的触发器.

Once you have saved the code, in the script editor set the Current Project's On Change Trigger to this function. It will write the sheet name to cell K1 on any change event. To set the trigger, select Current project's triggers under the Edit menu.

这篇关于是否有Google表格公式可将表格名称放入单元格中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！