Excel Google 脚本基础

Google 脚本

鼠标放在侧边栏上等待一会后会显示完整的侧边栏内容。

当前 Google 脚本项目的详细信息。

① 命名 Google 脚本项目的名称。
② 撤销编辑的内容。
③ 重做编辑的内容。
④ 保存当前编辑的内容。
⑤ 运行选中的函数，会自动保存所有的内容。
⑥ 选择待运行的函数。
⑦ 点击后会在下面显示运行后输出的日志。

⑧ 添加文件，重命名文件名；添加第三方脚本库；添加服务。
⑨ 部署和管理服务。

用来添加触发器来执行函数，并且显示函数的运行的详细信息。

查看每个函数执行的时间和状态以及日志。

一些进阶的设置项目。

Google 脚本的 SpreadsheetApp 服务可以读取、写入、创建表格。

SpreadsheetApp.getActiveSpreadsheet()

通过 getActiveSpreadsheet() 获取当前正在使用的电子表格，无需放入任何参数，需要绑定 Google 应用才可以使用，否则获取不到当前正在使用的电子表格。

SpreadsheetApp.openByUrl(url)

通过 openByUrl(url) 获取指定的电子表格，url 参数放入表格的链接，需要注意的是当前的 Google 账号必须有表格的访问权限，否则获取不到电子表格。

SpreadsheetApp.openById(id)

通过 openById(id) 获取指定的电子表格，id 参数放入表格的 ID，效果和 openByUrl 一样，唯一的区别就是 openByUrl 放入的是表格链接，openById 放入的是表格 ID。

使用一些不同的方法获取工作表，然后才可以进行一些其它的操作。

SpreadsheetApp.getActiveSheet()

通过 getActiveSheet() 获取当前正在使用的工作表，无需放入任何参数，需要绑定 Google 应用才可以使用。

getSheets()

在使用 getSheets() 之前需要先 获取到电子表格，然后才可以使用。例如获取当电子表格的所有工作表，那么先获取到当前的电子表格 SpreadsheetApp.getActiveSpreadsheet()，然后再加上 getSheets()
完整代码如下：

SpreadsheetApp.getActiveSpreadsheet().getSheets()

当然，也可以把 getActiveSpreadsheet() 换成其它的方法来获取电子表格的，然后再获取工作表，获取到的结果是一个数组。

getSheetByName(name)

需要先获取到电子表格后，才可以获取指定的工作表，name 放入工作表的名称。代码示例如下：

SpreadsheetApp.getActiveSpreadsheet().getSheetByName('工作表1')

Google 脚本 工作表相关

获取工作表的一些信息。

getSheetName()

需要先获取到电子表格后，才可以获取当前工作表的名称，代码示例如下：

SpreadsheetApp.getActiveSpreadsheet().getSheetName()

获取当前工作表 ID

getSheetId()

需要先 获取到电子表格 后，才可以获取到当前工作表的 ID，也就是 gid= 后面数字的部分，返回的结果是数值。代码示例如下：

SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].getSheetId()

跳转表格

第一种

setActiveSheet(sheet)

sheet 参数需要放入电子表格。示例如下：

SpreadsheetApp.setActiveSheet(SpreadsheetApp.getActiveSpreadsheet().getSheets()[1])

使用 SpreadsheetApp.getActiveSpreadsheet().getSheets()[1] 获取到第二个工作表，然后把整个代码作为 setActiveSheet 的参数。运行后就会跳转到第二个工作表了。

activate()

使用前需要先 获取到工作表，然后在后面使用 activate()，完整例子如下：

SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].activate()

先获取到第一个工作表，然后使用 activate() 跳转到获取到的工作表。

通过脚本对表格进行操作。

注意：以下所有内容，必须先 获取到工作表后 才可以进行操作。

使用 hideSheet() 将工作表隐藏，但是不允许把所有的工作表全部都隐藏起来，至少保留一个公开的工作表。

SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].hideSheet()

显示表格

使用 showSheet() 显示被隐藏起来的工作表，如果工作表已经显示则不会有任何效果。

SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].showSheet()

修改表格名称

使用 setName(name) 修改表格名称，name 参数放入表格的名称。

SpreadsheetApp.getActiveSheet().setName(name)

复制表格

使用 copyTo 可以复制一个工作表，也可以将工作表复制到另外一个电子表格。

const sheet = SpreadsheetApp.getActiveSpreadsheet()
const orginSheet = sheet.getActiveSheet()
orginSheet.copyTo(sheet)

需要两个参数，一个是工作表，另一个是电子表格。例子中的 orginSheet 变量是当前的工作表，使用 copyTo(sheet)，sheet 参数放入电子表格，可以是当前的电子表格，也可以是其它的电子表格，前提是需要有编辑的权限。

复制完表格后，默认会在原来表格名称的基础上加上（副本），不过可以使用上面提到的 setName 将新建的表格重命名。

const sheet = SpreadsheetApp.getActiveSpreadsheet()
const orginSheet = sheet.getActiveSheet()
orginSheet.copyTo(sheet).setName('sheetName')

复制工作表后，将工作表重命名为 “sheetName”。

使用 deleteSheet(spreadsheet) 删除工作表，spreadsheet 参数放入工作表。

const sheet = SpreadsheetApp.getActiveSpreadsheet()
sheet.deleteSheet(sheet.getSheets()[1])

需要两个参数，一个是电子表格，另一个是工作表。例子中的 sheet 变量是当前的电子表格，使用 deleteSheet(spreadsheet)，spreadsheet 放入需要删除的工作表。

使用 protect() 设置工作表的保护权限，禁止被编辑。

const sheet = SpreadsheetApp.getActiveSheet()
const email = sheet.protect().getEditors()
sheet.protect().removeEditors(email)

例子中的 sheet 变量是当前的工作表。使用 protect() 为工作表添加保护权限，但是在默认情况下，添加保护会允许所有人都可以编辑，所以需要移除掉所有人，仅限自己可以编辑。使用 getEditors() 获取到当前表格有权限的邮箱账号，并且设置 email 变量。removeEditors(email) 用来移除其他人的权限，email 参数放入邮箱，支持数组。全部邮箱都移除后，最后就剩下自己的账号有编辑的权限了。

使用 protect() 取消工作表的保护权限。

const sheet = SpreadsheetApp.getActiveSheet()
sheet.protect().remove()

例子中的 sheet 变量是当前的工作表，使用 protect() 操作工作表的保护权限，再使用 remove() 移除所有的保护权限。

将内容写入到日志进行调试。

Logger.log(data)

data 放入需要输出到日志的内容。下面是示例代码：

Logger.log('Log 测试')

输出结果

Logger.log(format, values)

format 放入包含占位符的内容，values 放入相应的变量。

%d 占位符代表整数，下面是示例代码：

Logger.log('我有 %d 本书', 5)

输出结果

输出的结果会将内容中 %d 替换对应的参数。

%s 占位符代表字符串

Logger.log('青苹果是%s', '绿色')

输出结果

支持同时使用多个占位符，示例代码如下：

Logger.log('橘子是%s的，香蕉是%s的', '橙色', '黄色')

输出结果

会按照从左到右的顺序放入对应的变量。

获取指定单元格的范围。

getCurrentCell()

获取表格内选中的其中一个单元格，即使选择多个单元格，也只返回左上角的第一个单元格。代码示例：

SpreadsheetApp.getActiveSheet().getCurrentCell()

例如下图，选中 B4 到 D10，但是只会返回 B4 单元格的范围。

getActiveRange()

获取表格内选中的范围。代码示例：

SpreadsheetApp.getActiveSheet().getActiveRange()

例如下图，选中 B3 到 C5 这个范围，那么就会返回这部分单元格的范围。

getDataRange()

从 A1 为起始点获取当前数据存在的范围。代码示例：

SpreadsheetApp.getActiveSheet().getDataRange()

例如下图，数据存放在 B2 到 D8 的范围，那么会返回从 A1 到 D8 单元格的范围。

getRange(a1Notation)

a1Notation 放入 A1 表示法引用单元格范围。代码示例：

SpreadsheetApp.getActiveSheet().getRange('C2:C7')

那么会获取 C2 到 C7 范围的单元格。

getRange(row, column, numRows, numColumns)

row 设置起始行；column 设置起始列；numRows 设置行数，非必填参数，默认为 1；numColumns 设置列数，非必填参数，默认为 1。
代码示例：

SpreadsheetApp.getActiveSheet().getRange(4, 2, 3, 2)

从第 3 行，第 2 列开始计算，也就是下图中 B4 的位置，然后再获取 3 行 和 2 列的内容。同等于 B4:C6 范围。

获取指定范围的行和列数。

getLastColumn()

获取指定范围在表格中最后一列的列数，代码示例：

SpreadsheetApp.getActiveSheet().getRange('B:C').getLastColumn()

虽然选择了 B 和 C 列，只获取了两列，但是 C 列作为末尾列，C 列在第 3 列的位置，所以返回的结果是 3。

getLastRow()

获取指定范围在表格中最后一行的行数。代码示例：

SpreadsheetApp.getActiveSheet().getRange('B3:C10').getLastRow()

选择 B3 到 C10 的范围，第 10 行诗最末尾的一行，所以返回结果是 10。

getRow()

获取指定范围起始行的行数。代码示例：

SpreadsheetApp.getActiveSheet().getRange('B3:C10').getRow()

选择 B3 到 C10 的范围，那么起始行是第 3 行，所以返回的结果是3。

getColumn()

获取指定范围起始列的列数，代码示例：

SpreadsheetApp.getActiveSheet().getRange('B:C').getColumn()

选中 B 和 C 列，B 列作为起始列，B 列在第 2 列的位置，所以返回的结果是 2。

getNumColumns()

获取指定范围内的列数。代码示例：

SpreadsheetApp.getActiveSheet().getRange('B3:C10').getNumColumns()

选择 B3 到 C10 范围，B 到 C 列一共就两列，所以返回值是 2。

getNumRows()

获取指定范围内的行数。代码示例：

SpreadsheetApp.getActiveSheet().getRange('B3:C10').getNumRows()

选择 B3 到 C10 范围，从第 3 行到 第 10 行这个范围一共有 8 行，所以返回值是 8。

读取和写入指定单元格的内容。

getValue()

返回单个单元格的值，这个值可以是任意类型：数值、布尔值、日期或者字符串，类型取决于单元格的内容。如果是空的内容会返回一个空的字符串。即使选择了一个范围的单元格，也只会返回左上角第一个单元格的值。代码示例：

SpreadsheetApp.getActiveSheet().getRange('B3').getValue()

返回表格中对应位置的值

getValues()

允许获取一个范围的内容，返回的结果是一个二维数组。代码示例：

SpreadsheetApp.getActiveSheet().getRange('A1:B3').getValues()

返回结果如下

[[35, 17], [23, 7], [15, 'A']]

写入内容

向单个单元格写入内容

setValue(value)

value 放入需要写入的内容。代码示例：

SpreadsheetApp.getActiveSheet().getRange('A1').setValue(4)

运行后会在 A1 单元格写入数值 4。

setValues(values)

values 放入对应范围尺寸的二维数组。代码示例：

SpreadsheetApp.getActiveSheet().getRange('A1:A3').setValues([[1], [2], [3]])

获取 A1 到 A3 的单元格范围，然后写入 1，2，3 数值。

copyTo(destination)

在使用 copyTo 之前需要先获取一个范围，然后在 destination 放入需要写入内容的范围。注意，这个方法不仅复制单元格的内容，还会复制单元的格式。代码示例：

const origRange = SpreadsheetApp.getActiveSheet().getRange('C:C')
const newRange = SpreadsheetApp.getActiveSheet().getRange('G:G')
origRange.copyTo(newRange)

将 C 列的内容复制到 G 列，效果如下图。

clearContent()

在使用前需要先获取一个范围，然后会清空指定范围的内容。代码示例：

SpreadsheetApp.getActiveSheet().getRange('C:C').clearContent()

运行后会清理 C 列的内容。

在电子表格创建一个菜单。

createMenu(caption)

caption 放入菜单名称，然后创建一个菜单。代码示例：

const ui = SpreadsheetApp.getUi()
ui.createMenu('主菜单')
  .addItem('菜单1', 'test')
  .addToUi()

需要先执行 SpreadsheetApp.getUi() 才能在用户界面添加菜单，然后使用 ui.createMenu() 创建一个名字叫主菜单的菜单，再使用 addItem 在菜单的下面添加项目，菜单内至少包含一个项目才可以创建，最后执行 addToUi() 将菜单添加到用户界面。

addItem(caption, functionName)

caption 放入项目名称，functionName 放入函数的名称，文本类型，当点击了对应项目的时候，就会触发设置好的函数。

function myFunction() {
  const ui = SpreadsheetApp.getUi()
  const menu = ui.createMenu('主菜单')
    .addItem('菜单1', 'test')
    .addToUi()
}

function test () {
  Logger.log('run')
}

例如在菜单添加了一个菜单 1 的项目，点击后就会运行设置好的 test 函数。

addSeparator()

在指定位置增加一个分割线。代码示例：

const ui = SpreadsheetApp.getUi()
ui.createMenu('主菜单')
  .addItem('菜单1', 'test')
  .addSeparator()
  .addToUi()

需要在菜单的后面加，而且必须在 addToUi() 的前面。

添加子菜单

addSubMenu(menu)

需要在菜单的后面使用，menu 放入子菜单。代码示例：

const ui = SpreadsheetApp.getUi()
const menu = ui.createMenu('主菜单')
  .addItem('菜单1', 'test')
  .addItem('菜单2', test)
  .addSeparator()
const subMenu = ui.createMenu('二级菜单')
  .addItem('菜单3', 'test')
  .addItem('菜单4', test)
menu.addSubMenu(subMenu)
  .addToUi()

创建后一个二级菜单后，将二级添加到主菜单的后面，这样就可以添加一个二级菜单了。

onOpen()

这是一个触发器函数，在打开电子表格的时候会自动运行，通常的时候会配合菜单一起使用。代码示例：

function onOpen() {
  const ui = SpreadsheetApp.getUi()
  const menu = ui.createMenu('主菜单')
    .addItem('菜单1', 'test')
    .addToUi()
}

将 onOpen() 设置为函数名称，每次打开表格的时候会自动运行函数内的代码，这样就可以实现在打开表格后自动加载好菜单。

发布一个 Web 页面。

createTemplateFromFile(filename)

filename 放入文件名称。代码示例：
Google Script

HtmlService.createTemplateFromFile('index')

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>test</p>
  </body>
</html>

需要提前创建好 index 文件，然后再使用此方法创建。

evaluate()

HTML 代码无法直接输出到 Web 页面，需要使用 evaluate() 解析后才可以输出。代码示例：

function doGet () {
  return HtmlService.createTemplateFromFile('index').evaluate()
}

iframe 权限

setXFrameOptionsMode(mode)

mode 设置模式。代码示例：

HtmlService.createTemplateFromFile('index').evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)

设置好后将允许在 iframe 中引用，不过要注意安全性问题，防止被劫持数据。

showSidebar(userInterface)

userInterface 放入解析后的 HTML 代码。示例代码：

const html = HtmlService.createTemplateFromFile('sidebar').evaluate()
SpreadsheetApp.getUi().showSidebar(html)

运行后会在表格右侧边栏加载 Web 界面，不过要注意的是，宽度只有 300px，无法变得更大，所以需要注意页面内容的尺寸，以免无法正常显示。

setTitle(title)

title 放入标题内容。示例代码：

const html = HtmlService.createTemplateFromFile('index').evaluate().setTitle('标题')
SpreadsheetApp.getUi().showSidebar(html)

发布后标题就会改为设置好的内容。

在表格增加或删减行或者列。

insertRows(rowIndex, numRows)

rowIndex 放入需要增加行的起始行；numRows 放入需要增加行的数量，非必填参数，默认为 1。代码示例：

SpreadsheetApp.getActiveSheet().insertRows(5, 2)

运行后会从第 5 行开始增加 2 行。

deleteRows(rowPosition, howMany)

rowPosition 放入要删除的行的起始行；howMany 放入需要删除的行的数量，非必填参数，默认为 1。代码示例：

SpreadsheetApp.getActiveSheet().deleteRows(5, 2)

运行后会从第 5 行开始删除 2 行。

insertColumns(columnIndex, numColumns)

columnIndex 放入需要增加列的起始列；numColumns 放入需要增加列的数量，非必填参数，默认为 1。代码示例：

SpreadsheetApp.getActiveSheet().insertColumns(2, 3)

运行后会从第 2 列开始增加 3 列。

deleteColumns(columnPosition, howMany)

columnPosition 放入需要删除的列的起始列；howMany 放入需要删除列的数量，非必填参数，默认为 1。代码示例：

sort(columnPosition, ascending)

columnPosition 放入需要排序的列，ascending 放入布尔值进行升序或者降序，非必填参数，默认为 true。示例代码：

SpreadsheetApp.getActiveSheet().sort(2, false)

将第二列进行倒序排序。

客户端和服务端互相传输数据。

google.script.run.myFunction(…)

myFunction 可以设置服务端任意的函数，并且可以放入参数。代码示例：
Code.gs

function doGet () {
  return HtmlService.createTemplateFromFile('index').evaluate()
}

function cellValues (area) {
  const values = SpreadsheetApp.openById('表格ID').getRange(area).getValues()
  Logger.log(values)
  return values
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <script>
      google.script.run.cellValues('A1')
    </script>
  </body>
</html>

打开 Web 应用后就会执行 cellValues 函数并且传入 A1 字符串。在 Logger 中可以看到获取 A1 单元格的结果。

withSuccessHandler(function)

function 设置回调函数。代码示例：
index.html

google.script.run.withSuccessHandler(function (result) {
  console.log(result)
}).cellValues('A1')

将返回的结果设置命名为 result 并且传入到 console 输出。

有一个可以直接调用的接口真是太方便了！

doGet(request)

这是一个 Web 应用的触发器，需要部署 Web 应用后才可以使用。当收到一个 HTTP GET 请求的时候就会执行，将请求的参数传入 request。

parameter

请求的参数会返回一个 Object 类型的值。代码示例：

function doGet (request) {
  Logger.log(request.parameter)
}

将传入 request 的参数使用 Logger 写入到日志。
返回结果

createTextOutput(content)

content 放入字符串，会在 Web 应用返回一个文本内容，需要使用 return 返回。代码示例：

function doGet () {
  return ContentService.createTextOutput('测试')
}

请求后就返回一个测试的文本。

使用网络应用将数据填入电子表格 A1 的位置。

function doGet (request) {
  const {text} = request.parameter
  SpreadsheetApp.openById('表格ID').getRange('A1').setValue(text)
  return ContentService.createTextOutput('完成')
}

将请求的参数解析，然后使用 setValue 写入到 A1 单元格。

const urlStr = {
  text: 'ok'
}
const response = await fetch(`放入部署后的链接?${new URLSearchParams(urlStr).toString()}`).then(text => text.text())
console.log(response)

将 Object 类型的内容使用 URLSearchParams 转换成链接格式，再使用 fetch 发送请求，最后 console 的结果就是前面在 createTextOutput 设置好的文本作为返回结果。请求后会将设置的 ok 内容写入到表格内容 A1 单元格。

使用触发器自动化运行函数。

在左侧选择触发器。

在右下角点击添加触发器。

在选择要运行的功能中选择需要运行的函数，每次只能添加一个。

等待电子表格加载完成后就会执行，每次打开电子表格都会重新执行一次，执行后就不会再重复执行了。

仅在修改单元格内容的时候才会执行，每次修改内容都会再次执行。

不仅在修改单元格的内容的时候会执行，修改工作表名称，或者进行其它的更改都会执行。

需要将表单绑定电子表格，当表格提交的时候就会执行。

在指定的时间执行，时间格式：YYYY-MM-DD HH:MM，只会执行一次。

在当前某个时间会自动运行，不过这个时间只能是在某个时间段的区间，并不会非常精确的运行。例如在下午 3 点至 4 点，那么就会在这个区间的任意时间运行，每次运行都不会固定在多少分钟运行。