Google Sheets API，在 Google Apps Script 上的好用部份

Let's Write

2023/04/01閱讀時間約 15 分鐘

本篇要解決的問題

之前寫過蠻多篇關於 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）。

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

var ss = SpreadsheetApp.getActiveSpreadsheet();

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

建立模擬資料

為了可以實際操作看效果，August 有建立了一個 Demo 用的試算表，裡面的資料都是用以前寫的這篇「如何用 Postman Mock Server 快速建立 API Server」建立的。

Demo 用的試算表內容如下：

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

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

試算表好用函式

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

const s = SpreadsheetApp.getActiveSpreadsheet();

取得試算表的 ID

文件：getId()

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

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

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

取得試算表的 ID getId

取得試算表的名稱

文件：getName()

取得試算表的檔案名稱。

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

取得試算表的名稱 getName

取得所有工作表

文件：getSheets()

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

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

取得所有工作表 getSheets

新增工作表

文件：insertSheet(sheetName)

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

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

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

執行後：

插入在執行中的工作表之後

刪除工作表

文件：deleteSheet(sheet)

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

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

刪除工作表 deleteSheet

試算表加上客製選單

文件：addMenu(name, subMenus)

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

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

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 後就會看見導覽列上多了一個選項：

試算表加上客製選單，開啟試算表時執行

更新試算表的客製選單

文件：updateMenu(name, subMenus)

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

// 選單名稱相同會覆蓋原本 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)

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

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

Sheets 上的 Toast 長這樣：

右下角彈出視窗 toast

工作表好用函式

從名稱找工作表

文件：getSheetByName(name)

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

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

從名稱找工作表 getSheetByName

取得工作表的名稱

文件：getSheetName()

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

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

取得工作表的名稱 getSheetName

取得工作表的值

文件：getSheetValues(startRow, startColumn, numRows, numColumns)

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

function 中給的參數都一樣：

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

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

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

清空工作表的值

文件：clear

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

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

預設的清除，除了資料外連原本設定的格式也會被清空，比方今天我們設定了條件式格式突顯出重複的值，一用了 clear，那設定的條件式格式也會被清掉，就變成每次都要重新設定。

Sheet 很貼心的給了參數來選擇 clear 是要清格式、清資料，還是二個都清。

formatOnly：清格式。

contentsOnly：清資料。

function clear() {
  const first = s.getSheetByName('測試表1');
  first.clear({ formatOnly: false, contentsOnly: true });
}

另外補充一個，條件式格式突顯重複值的公式為：

=countif($A:$A,$A1)>1

工作表最底部新增一列值

文件：appendRow(rowContents)

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

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

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

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

文件：getLastRow()

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

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

取得最後有值的列是第幾列 getLastRow

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

文件：getLastColumn()

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

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

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

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

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

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);
}

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