Google Sheets API，在 Google Apps Script 上的好用部份
Tags: tutorial, programming, api, javascript
Published: 2022-11-30 12:16:30

## 本篇要解決的問題

之前寫過蠻多篇關於 [Google Sheets](https://www.letswrite.tw/category/google/sheets/) 的應用，卻一直沒仔細的看過官方文件。取值、寫值靠的是別種的方法而不是原有的 Google Apps Script 的內建函式。

最近因為一些事情，覺得將來可能有機會拿 Google Sheet 當一個小資料庫來用，就認真的閱讀了文件，把認為會用到的一些函式給存下來，未來如果機會成熟了，就可以回頭來看這篇使用。

本篇是 August 看過了一遍 Google Apps Script 關於 Google Sheets 的文件後，選出的幾個好用函式，實際使用看效果並存下來，並沒有包含所有文件上提到的內容。

以下提到 Google Apps Script 的部份簡稱 GAS，提到 Google Sheets 的部份簡稱 Sheets。

因為本篇主要是用 GAS 來讀寫 Sheets，所以原始碼這次就不存 GitHub 上，直接存在 GAS 上，在這邊先提供給大家：

<https://script.google.com/d/1JZfC44ra8NIWAXdsgNBMrg5ilG2_r_xwdpW7PaDMuUTiBM6Sf8Iw9g9K/edit?usp=sharing>

---

## 概念：試算表 > 工作表

這邊先說明一個基本概念，我們一般會說「你那個 Excel 的檔案整理好後寄給我」，我們講 Excel 時腦子裡想到的會是 Excel === 試算表 === 工作表 === Google Sheets。

實際上看過官方文件後，就會理解到一個 Google Sheets 的檔案，就是「試算表」（Spreadsheet），而一個試算表裡我們會開有很多張表，那些表就是「工作表」（Sheet）。

看文件會看見命變數時，第一行常常會是：

```js
var ss = SpreadsheetApp.getActiveSpreadsheet();
```

就是在命這整個試算表本身。

---

## 建立模擬資料

為了可以實際操作看效果，August 有建立了一個 Demo 用的試算表，裡面的資料都是用以前寫的這篇「[如何用 Postman Mock Server 快速建立 API Server](https://www.letswrite.tw/postman-mock-server/)」建立的。

Demo 用的試算表內容如下：

<https://docs.google.com/spreadsheets/d/1FTMg3DkfgSvx3a71A2w08pKZ7zrBb1t6T-R1L_CIlss/edit?usp=sharing>

裡面有二張工作表：測試表1、測試表2。

---

## 試算表好用函式

以下程式碼中的變數 `s`，都是指試算表本身：

```js
const s = SpreadsheetApp.getActiveSpreadsheet();
```

---

### 取得試算表的 ID

文件：[getId()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getid)

試算表的 ID 可以直接從網址上看到，也可以用函式取得。

```js
function getId() {
  const id = s.getId();
  Logger.log(id)  
}
```

透過 GAS 的執行功能，會看到如下結果：

![取得試算表的 ID getId](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q92u6csqhi6bwt4c2qq1.png)

---

### 取得試算表的名稱

文件：[getName()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getname)

取得試算表的檔案名稱。

```js
function getName() {
  const name = s.getName();
  Logger.log(name)  
}
```

![取得試算表的名稱 getName](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1iv0cwrvkqmb86foj4um.png)

---

### 取得所有工作表

文件：[getSheets()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets)

取得試算表下的所有工作表，取出來後要用迴圈再來取得工作表的資訊，範例中是用 `getName()` 來取得每張工作表的名稱。

```js
function getSheets() {
  const sheets = s.getSheets();
  if (sheets.length > 1) {
    for(let ss of sheets) {
      Logger.log(ss.getName())
    }
  }
}
```

![取得所有工作表 getSheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/36hl0edpyuw954osfwbe.png)

---

### 新增工作表

文件：[insertSheet(sheetName)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#insertsheetsheetname)

新增的工作表，位置會插入在執行中的工作表之後。

比方我們目前在執行中的工作表是「測試表1」，那新增的工作表就會插在測試表1之後。

```js
function insertSheet() {
  s.insertSheet('測試新增工作表');
}
```

![新增工作表 insertSheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sr3qqc8hmiu7c53m32mr.png)

執行後：

![插入在執行中的工作表之後](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/omzrwiomo4d3plroffrq.png)

---
  
### 刪除工作表

文件：[deleteSheet(sheet)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#deletesheetsheet)

刪除指定的工作表，抓到工作表就可以刪除，範例示範的是刪除指定的工作表名稱。

```js
function deleteSheet() {
  const sheet = s.getSheetByName('測試新增工作表');
  s.deleteSheet(sheet);
}
```

![刪除工作表 deleteSheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bsbyw3e8yrjtzdtvtddz.png)

---

### 試算表加上客製選單

文件：[addMenu(name, subMenus)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#addmenuname,-submenus)

這個功能蠻有趣的，就是可以在 Sheets 的導覽列上加上我們想要的選項。

也可以設定點了我們新增的選項後要執行哪個 function。

```js
function addMenu() {
  const menu = [];

  // name：項目名稱。functionName：點擊時執行哪個 function
  menu.push({ name: '新增工作表', functionName: 'insertSheet' });
  menu.push(null); // 分隔線
  menu.push({ name: '刪除工作表', functionName: 'deleteSheet' });

  s.addMenu("加上客製選單", menu);
}

// 試算表打開時執行
function onOpen() {
  addMenu();
}
```

打開試算表，執行完 `addMenu` 後就會看見導覽列上多了一個選項：

![試算表加上客製選單，開啟試算表時執行](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/luf1livvorp5hitu448w.png)

---

### 更新試算表的客製選單

文件：[updateMenu(name, subMenus)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#updatemenuname,-submenus)

這跟新增客製選單很像，要注意的是，如果原本就有加上客製選單，再用更新選單時，**選單名稱相同會覆蓋，不同的才會新增**。

```js
// 選單名稱相同會覆蓋原本 addMenu 時新增的
function updateMenu_update() {
  const menu = [];
  menu.push({name: '更新成只有新增工作表', functionName: 'insertSheet'});
  s.updateMenu('加上客製選單', menu);
}

// 選單名稱不同，就會新增一個客製按鈕
function updateMenu_create() {
  const menu = [];
  menu.push({name: '來啊再新增一個加入工作表', functionName: 'insertSheet'});
  menu.push(null); // 分隔線
  menu.push({name: '來啊再新增一個刪除工作表', functionName: 'deleteSheet'});
  s.updateMenu('加上客製選單2', menu);
}
```

因為一次實作了覆蓋跟新增，原本結果部份錄成影片的方式呈現，結果 YouTube 不給傳，最後還刪掉了影片，就算了，就請大家自行貼上程式碼後測試囉~

---

### 右下角彈出視窗

文件：[toast(msg, title)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#toastmsg)

Toast 就是指出現在畫面上的一個小視窗，通常會有時間性，比方三秒或五秒後自動消失。

```js
function toast() {
  s.toast("Let's Write - 這是一個 Toast 的內文部份。", "開啟一個 Toast");
}
```

Sheets 上的 Toast 長這樣：

![右下角彈出視窗 toast](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9t9mxpavltz02zdd5eo2.png)

---

## 工作表好用函式

### 從名稱找工作表

文件：[getSheetByName(name)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename)

範例中是抓出工作表，並且 Log 出該工作表排序第幾。

```js
function getSheetByName() {
  const sheet = s.getSheetByName('測試表1');
  if (sheet != null) {
    Logger.log(sheet.getIndex());
  }
}
```

![從名稱找工作表 getSheetByName](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rjdmhnbqlladg3cfelcd.png)

---

### 取得工作表的名稱

文件：[getSheetName()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetname)

範例中是先用 `getSheets()[0]` 取出第一張工作表，然抓再取出工作表名稱。

```js
function getSheetName() {
  const sheet = s.getSheets()[0];
  Logger.log(sheet.getSheetName());
}
```

![取得工作表的名稱 getSheetName](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1s5m50xkeah9w8fl89v3.png)

---

### 取得工作表的值

文件：[getSheetValues(startRow, startColumn, numRows, numColumns)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetvaluesstartrow,-startcolumn,-numrows,-numcolumns)

範例中提供了二種抓工作表所有值的方法：`getSheetValues`、`getRange`。

function 中給的參數都一樣：

- startRow：第幾列開始
- startColumn：第幾欄開始
- numRows：共要抓幾列的值
- numColumns：共要抓幾欄的值

下列範例程式碼寫：`1, 1, 6, 2`，意思就是從第 1 列、第 1 欄開始，抓 6 * 2 的資料。

```js
function getSheetValues() {
  const sheet = s.getSheets()[0];

  // 方法 1：getSheetValues
  const values1 = sheet.getSheetValues(1, 1, 6, 2);
  Logger.log(values1);
  
  // 方法 2：getRange
  const range = sheet.getRange(1, 1, 6, 2);
  values2 = range.getValues();
  Logger.log(values2);
}
```

![取得工作表的值 getSheetValues、getRange](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7mg4mm2onktsbh7it0v6.png)

---

### 清空工作表的值

文件：[clear()](https://developers.google.com/apps-script/reference/spreadsheet/sheet#clear)

這個就不示範了，因為一用會整張工作表的值都被清空。

```js
function clear() {
  const first = s.getSheetByName('測試表1');
  first.clear();
}
```

---

### 工作表最底部新增一列值

文件：[appendRow(rowContents)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#appendrowrowcontents)

這個 function 好用，要塞資料到 Sheet，就寫一個迴圈不斷用 `appendRow` 就對了。

```js
function appendRow() {
  const sheet = s.getSheets()[0];
  sheet.appendRow(['ID', '姓名', 'email', '電話']);
}
```

![工作表最底部新增一列值 appendRow](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g6xecohqyboisfdneviv.png)

---
### 取得最後有值的列是第幾列

文件：[getLastRow()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getlastrow)

比方我們的 Demo 共有 6 列資料，用 `getLastRow` 就會回傳 `6`。

```js
function getLastRow() {
  const sheet = s.getSheets()[0];
  const lastRow = sheet.getLastRow();
  Logger.log(lastRow);
}
```

![取得最後有值的列是第幾列 getLastRow](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/31u92vckh3xp1qrgj5j2.png)

---

### 取得最後有值的欄是第幾欄

文件：[getLastColumn()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getlastcolumn)

比方我們的 Demo 共有 4 列資料，用 `getLastColumn` 就會回傳 `4`。

```js
function getLastColumn() {
  const sheet = s.getSheets()[0];
  const lastCol = sheet.getLastColumn();
  Logger.log(lastCol);
}
```

![取得最後有值的欄是第幾欄 getLastColumn](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yop75pvskogiwxkj6ntj.png)

---

### 結合應用，直接取整張工作表的值

這邊結合上面三個 function，可以不用手動輸入要抓幾列幾欄，就一次抓工作表裡的資料。

```js
function getSheetVal() {
  const sheet = s.getSheets()[0];
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const values = sheet.getSheetValues(1, 1, lastRow, lastColumn);
  Logger.log(values);
}
```

![結合應用，直接取整張工作表的值](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ugt59l8e5iny0fk9ozj.png)

---

## 原始碼

在放在 Google Apps Script 上，請自行點選檔案切換觀看：

<https://script.google.com/d/1JZfC44ra8NIWAXdsgNBMrg5ilG2_r_xwdpW7PaDMuUTiBM6Sf8Iw9g9K/edit?usp=sharing>