求Office Script实现工作表名填充列及在线Excel活动工作表名方法

背景 #

此前使用VBA脚本获取工作簿中所有工作表名并填充到一列，脚本运行正常，但迁移至不支持VBA的在线Excel版本后需替代方案。原VBA脚本如下：

Function SHEET_NAMES()

Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook

Dim out As Variant

Dim x As Variant
x = mainworkBook.Sheets.Count - 1

ReDim out(x, 0)

Dim i As Variant
For i = 0 To x
    out(i, 0) = mainworkBook.Sheets(i + 1).Name
Next i

SHEET_NAMES = out

End Function

参考的Google Sheets实现脚本：

/**
 * Returns the names of all sheets.
 * 
 * @return The name of all sheets in the Google Sheets spreadsheet.
 * @customfunction
 */
function ALLSHEETNAMES() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach(function (sheet) {
    sheetNames.push(sheet.getName());
  });
  return sheetNames;
}

1. 获取所有工作表名（Office Scripts） #

在线Excel（Excel for the web）支持Office Scripts作为VBA的替代方案，以下提供两种实现方式：

方式1：直接填充到指定列 #

function main(workbook: ExcelScript.Workbook) {
  // 获取所有工作表
  const sheets = workbook.getWorksheets();
  // 提取工作表名并整理为二维数组
  const sheetNames = sheets.map(sheet => [sheet.getName()]);
  // 指定目标工作表（示例为第一个工作表，可自行修改）
  const targetSheet = workbook.getWorksheets()[0];
  // 从A1开始填充数据
  targetSheet.getRange("A1").getResizedRange(sheetNames.length - 1, 0).setValues(sheetNames);
}

使用步骤：

• 打开在线Excel文件，点击顶部菜单栏「自动化」→「新建脚本」
• 粘贴上述代码到编辑器，点击「运行」即可将所有工作表名填充到第一个工作表的A列。

方式2：自定义函数调用 #

/**
 * 获取工作簿中所有工作表名称
 * @customfunction
 */
function GET_ALL_SHEET_NAMES(): string[][] {
  const workbook = ExcelScript.getActiveWorkbook();
  const sheets = workbook.getWorksheets();
  return sheets.map(sheet => [sheet.getName()]);
}

使用步骤：在任意单元格输入=GET_ALL_SHEET_NAMES()，按回车即可自动生成所有工作表名的列表。

2. 获取活动工作表名（Office Scripts） #

由于CELL("filename")在在线Excel中无效，可通过以下Office Script实现：

方式1：直接填充到指定单元格 #

function main(workbook: ExcelScript.Workbook) {
  // 获取当前活动工作表
  const activeSheet = workbook.getActiveWorksheet();
  // 获取并填充工作表名称到A1单元格
  activeSheet.getRange("A1").setValue(activeSheet.getName());
}

方式2：自定义函数返回名称 #

/**
 * 获取当前活动工作表的名称
 * @customfunction
 */
function GET_ACTIVE_SHEET_NAME(): string {
  const workbook = ExcelScript.getActiveWorkbook();
  return workbook.getActiveWorksheet().getName();
}

使用步骤：在任意单元格输入=GET_ACTIVE_SHEET_NAME()，按回车即可显示当前活动工作表的名称。

内容的提问来源于stack exchange，提问作者Bnikushev