如何精通Excel办公脚本

由 Mux 主办的 DEV 全球展示挑战赛：展示你的项目！

Office Scripts 是 VBA 的在线版本。它专为在 Excel Online 中运行而设计，类似于 Google Docs App Scripts，但基于 TypeScript 而不是 JavaScript（但由于 TypeScript 是 JavaScript 的一个子集，因此您也可以使用 JavaScript）。

https://learn.microsoft.com

Office 脚本最强大的功能之一是它们可以被 Power Automate 调用，从而实现无人值守运行。如果您想了解 Power Automate 中的 Office 脚本，我写过一篇姊妹文章，内容与本文基本相同，但介绍了Power Automate中所需的其他功能和步骤。

本文将涵盖以下内容：

• 访问脚本
• 触发脚本
• 智能感知
• 变量、类型和接口
• 准备
• Excel 基本操作
• 如果
• 环形
• API
• 附加功能

访问脚本

在 Excel Online 中，您会在功能区看到“自动化”选项卡。该功能区允许您录制操作（就像 VBA 一样）、创建空白的新脚本、打开现有脚本以及使用 Power Automate 模板自动执行任务。

录制功能不会记录每个操作，而是使用选择而不是引用，但它确实能显示很好的笔记，是一种很好的学习方式。

触发脚本

可以通过在“自动化”选项卡中选择 Office 脚本来运行它们，也可以通过添加按钮来运行它们。

Office 脚本默认存储在 OneDrive 的“文档/Office 脚本”文件夹中，但您可以通过点击添加按钮所在菜单中的“共享”按钮，将脚本与文件共享。这样，脚本就会附加到文件上，任何拥有该文件的人都可以运行该脚本。

智能感知

智能感知功能可以自动补全代码，当您输入所需的函数时，Office Scripts 会尝试通过列出所有可能的选项来猜测您正在输入的内容。这不仅可以加快您的输入速度，还可以作为参考，帮助您快速找到所需的函数。

变量、类型和接口

任何具备 TypeScript 知识的人都可以从这里开始快速上手。变量用 'let' 声明，并且需要声明类型（必须为其赋值）。

let sString="";
let iNumber=0;
let bFlag=false;

我们还可以将工作簿、工作表、图像、范围等对象声明为变量，以便更轻松地使用和更新它们。

function main(workbook: ExcelScript.Workbook) {
    let ws=workbook.getWorksheet("Sheet1");
    ws.setName("test");
}

变量的作用域是局部的，因此在函数中声明的变量的作用域仅限于该函数，在循环中声明的变量的作用域仅限于该循环，并且不能在循环外部读取。

声明数组时有两种选择：空数组和结构化数组。
空数组用于不包含任何对象的简单数组；如果需要包含对象，则应使用接口来设置数组结构。

function main(workbook: ExcelScript.Workbooklet){ 
    aSimple=[]=[];
    let aStructured:schema[];
}

interface schema {
    stringField: string,
    numberField: number,
    booleanField: boolean
}

如您所见，您在函数外部声明了接口，它会创建数组的模式，因此 aStructure 将如下所示：

[
    {stringField:"test",numberField:1,booleanField:false},
    {stringField:"test2",numberField:2,booleanField:true}
]

而 aSimple 则类似于：

[1,2,3,4,5,6,7,8]

类型和接口几乎可以互换，在 Office Scripts 中两者之间的区别并不明显。因此，您可以使用类型代替接口，但大多数 Microsoft 文档都使用接口。

type dataType = {
    data: JSONData[],
    name: string
}

准备

正如你看到的 `getWorkSheet("Sheet1")`，`get` 用于引用某个对象，可以将其存储为变量，也可以执行某些操作，例如 `setValue()`。它不仅可以获取工作簿的各个部分，还可以获取它们的参数，例如工作表名称。

首先，获取工作表，获取范围，然后设置范围。范围可以是公式（setFormula）或值（setValue），可以是单个单元格或范围（setValues）。

所以，在下面的示例中，我们将把一个工作表中的筛选列表复制到另一个工作表中。

function main(workbook: ExcelScript.Workbook) {
    let ws=workbook.getWorksheet("summary");
    let i=0;
    let aNewRange=[]=[];

    let rng=ws.getUsedRange().getValues();
    aNewRange.push(rng[0]);

    for(i==0;i<rng.length; i++){
      if(rng[i][0]==3){
        aNewRange.push(rng[i]);
      }
    }
workbook.getWorksheet("Sheet2").getRange("A1:e"+aNewRange.length).setValues(aNewRange);
}

你可能认为最好的方法是先筛选Excel数据，然后复制粘贴。但更好的方法是先获取整个范围，进行筛选，然后粘贴并将范围设置为筛选后的值。

let rng=ws.getUsedRange().getValues();- 从范围内获取值
aNewRange.push(rng[0]);- 添加标题行
for(i==0;i<rng.length; i++){- 遍历数组中的行
if(rng[i][0]==3){- 如果满足条件
aNewRange.push(rng[i]);- 将行添加到数组
workbook.getWorksheet("Sheet2").getRange("A1:e"+aNewRange.length).setValues(aNewRange);- 将数组设置为范围

你也可以在循环中逐行设置范围，但这会对性能产生很大影响。所有与 Excel 文件的操作都会调用 API，因此我们应该尽可能避免将它们放在循环中。

Excel 基本操作

正如您所预期的，您可以与 Excel 工作簿进行交互。以下列表提供了一些示例，这些示例基于分配给 ws 和 wsPivot 变量的工作表。

正如您所预期的，您可以与 Excel 工作簿进行交互。以下列表提供了一些示例，这些示例基于分配给 ws 和 wsPivot 变量的工作表。

let ws= workbook.addWorksheet("test");- 添加名为“test”的工作表
ws.delete();- 删除工作表

let chartName = selectedSheet.addChart(ExcelScript.ChartType.pie, selectedSheet.getRange("A1:C15"));- 在选定的工作表中插入图表

let newPivotTable = workbook.addPivotTable("PivotTableName", ws.getRange("A1:C15"), wsPivot.getRange("A2:C16"))- 在 Sheet3 上添加一个新的数据透视表

newPivotTable.refresh();- 刷新新透视表

ws.getAutoFilter().apply(ws.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: ["1"] });- 将值过滤器 1 应用于范围

ws.getRange("A1:C4").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);- 在范围底部添加细边框

ws.getRange("A1:C4").getFormat().getFill().setColor("FFFF00");- 将范围的填充颜色设置为 FFFF00

ws.getRange("A1:C4").removeDuplicates([0], false);- 删除 G9:G39 区域中的重复项

ws.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);- 插入 F:F 列，并将现有单元格向右移动

ws.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left);- 删除 F:F 列

ws.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down);- 在 39:39 区域插入，并将现有单元格向下移动

ws.getRange("3:3").delete(ExcelScript.DeleteShiftDirection.up);- 删除第 39:39 行

ws.getRange("A1:C4").getFormat().getFill().setColor("FFFF00");- 将范围的填充颜色设置为 FFFF00

ws.getRange("A1:C4").removeDuplicates([0], false);- 删除 G9:G39 区域中的重复项

ws.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);- 插入 F:F 列，并将现有单元格向右移动

ws.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left);- 删除 F:F 列

ws.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down);- 在 39:39 区域插入，并将现有单元格向下移动

ws.getRange("3:3").delete(ExcelScript.DeleteShiftDirection.up);- 删除第 39:39 行

如果

如果是所有编码中的基本操作，幸运的是，Office Scripts 利用了 TypeScript/JavaScript，所以它非常简单易用。

if(rng[i][0]==3){
    aNewRange.push(rng[i]);
} else {
    console.log("Not a 3");
}

如果（条件）{//如果为真则执行}否则{//如果为假则执行）。如您所见，其逻辑与 Excel 不同，因此等于运算符为 ==（=== 也匹配类型，所以 1=="1" 为真，1==="1" 为假）。不等于运算符为 !=，大于和小于运算符为标准运算符（>、<、>=、<=）。您也可以直接传入一个布尔值或一个数组来检查它是否为空。

let bFlag=true
if(bFlad){
    console.log("its true");
}

循环

和往常一样，执行循环的方法有很多种，我推荐两种：forEach 和 for。你已经见过 for 循环了。

for(i==0;i<rng.length; i++){
    //do something
}

for(counter = start; till counter less than value; step by +1)。在这个例子中，我从 0 开始（数组中的第一个元素是 0 而不是 1），我循环直到 i 达到 rng 的长度（行数），每次循环我都加 1（所以 1-- 会向后移动一步，10++ 每次循环会加 10）。

要引用数组中的元素，可以使用方括号 []，因此 array[0] 表示数组中的第一个元素。此外，如果是二维数组（例如具有行和列的表格），则可以使用方括号 [][]，因此 array[1][0] 表示第二行第一列。

另一个有用的循环是 forEach 循环，在这里你不需要传递数组的计数器/索引，可以直接引用数组元素。在下面的例子中，我将元素命名为 ws，但你可以把它改成任何名称（通常我直接用 item）。

workbook.getWorksheets().forEach(ws =>
    console.log(ws.getName())
)

上面我们循环遍历工作簿中的所有工作表，然后记录工作表的名称。

API

这就是 Office Scripts 功能更加强大的地方，它可以扩展到工作簿之外，并与 API 进行交互（甚至可以与带有Graph API 的其他工作簿进行交互）。

async function main(workbook: ExcelScript.Workbook) {
    let iRows = 0;
    let aTemp=[]=[];

    const myHeaders = new Headers({'app-id': '############'});
    const myInit = {
        method: 'GET',
        headers: myHeaders
    };

    let fetchResult = await fetch('https://dummyapi.io/data/v1/user?limit=10',myInit);

    let oData: dataType = await fetchResult.json();
    let json: JSONData[] = oData.data;

    json.forEach(item => 
        aTemp.push([item.id, item.title, item.firstName, item.lastName,item.picture])
    );

    iRows = json.length;
    workbook.getWorksheet('Sheet1').getRange('A2:E' + (iRows+1)).setValues(aTemp);
}

interface JSONData {
    id: number,
    firstName: string,
    lastName: string,
    title: string,
    picture: string
}
interface dataType = {
    data: JSONData[]
}

要获取应用 ID 并使用代码，请在https://dummyapi.io/免费注册。

所以主要变化在于我们现在使用异步函数代替了 Main 函数。之后，我们使用标准的 JavaScript await fetch()。

const myHeaders = new Headers()- 创建请求头，例如内容类型、身份验证等
const myInit()- 配置调用，GET/POST/DELETE/PATCH/PUT 并添加请求头
let fetchResult = await fetch()- 将响应存储在 fetchResult 中
let oData: dataType = await fetchResult.json();- 等待响应，并将其存储为 oData（响应作为父数据对象，因此我们将 oData 设置为 dataType）
let json: JSONData[] = oData.data;- 将数据数组设置到 json 变量中- 遍历每一行并将其添加到简单的二维数组中
json.forEach(item =>aTemp.push([item.id, item.title, item.firstName, item.lastName,item.picture]));


需要注意的是，如果脚本是由 Power Automate 运行的，则无法调用 API，并且没有办法保护您的身份验证凭据。

附加功能

正如我所说，Office Scripts 是用 TypeScript/JavaScript 编写的，因此大多数 JavaScript 函数也能正常工作，以下是一些推荐的函数：

数组排序

if(sortType=="Number"){
    sortArray = data.sort((a, b) => {
      if (direct=="asc"){
        return a.ID - b.ID;
      }else{
        return b.ID - a.ID;
      }
    });
  } else{
    if (direct == "asc") {
      sortArray = data.sort((a, b) => a.ToSort.localeCompare(b.ToSort));
    }else{
      sortArray = data.sort((a, b) => b.ToSort.localeCompare(a.ToSort));
    }
  };

上面我们展示了如何按数字与字符串排序，以及如何进行升序或降序排序（数据是输入 - 未排序数组，sortArray 是输出 - 已排序数组）。

过滤器数组

let filteredArray=data.filter((item, index) => 
    return item.Field > 100
)

上面我们筛选出字段列大于 100 的所有行（数据是输入 - 未过滤的数组，filteredArray 是输出 - 过滤后的数组）。

正
则表达式允许您使用模式（而不仅仅是精确匹配）从其他字符串中提取（查找）字符串。

let regEx = new RegExp(rgex, flag);
let aMatches = inputString.match(regEx);
if (aMatches) {
    console.log("Regex found matches");
}

想了解更多关于正则表达式的信息，我推荐这个网站：https://regex101.com/