本篇要解決的問題
之前寫過蠻多篇關於 Google Sheets 的應用，卻一直沒仔細的看過官方文件。取值、寫值靠的是別種的方法而不是原有的 Google Apps Script 的內建函式。
最近因為一些事情，覺得將來可能有機會拿 Google Sheet 當一個小資料庫來用，就認真的閱讀了文件，把認為會用到的一些函式給存下來，未來如果機會成熟了，就可以回頭來看這篇使用。
本篇是 August 看過了一遍 Google Apps Script 關於 Google Sheets 的文件後，選出的幾個好用函式，實際使用看效果並存下來，並沒有包含所有文件上提到的內容。
以下提到 Google Apps Script 的部份簡稱 GAS，提到 Google Sheets 的部份簡稱 Sheets。
因為本篇主要是用 GAS 來讀寫 Sheets，所以原始碼這次就不存 GitHub 上，直接存在 GAS 上，在這邊先提供給大家：
概念：試算表 > 工作表
這邊先說明一個基本概念，我們一般會說「你那個 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 的執行功能，會看到如下結果：
取得試算表的名稱
文件：getName()
取得試算表的檔案名稱。
function getName() {
const name = s.getName();
Logger.log(name)
}
取得所有工作表
文件：getSheets()
取得試算表下的所有工作表，取出來後要用迴圈再來取得工作表的資訊，範例中是用 getName() 來取得每張工作表的名稱。
function getSheets() {
const sheets = s.getSheets();
if (sheets.length > 1) {
for(let ss of sheets) {
Logger.log(ss.getName())
}
}
}
新增工作表
新增的工作表，位置會插入在執行中的工作表之後。
比方我們目前在執行中的工作表是「測試表1」，那新增的工作表就會插在測試表1之後。
function insertSheet() {
s.insertSheet('測試新增工作表');
}
執行後：
刪除工作表
刪除指定的工作表，抓到工作表就可以刪除，範例示範的是刪除指定的工作表名稱。
function deleteSheet() {
const sheet = s.getSheetByName('測試新增工作表');
s.deleteSheet(sheet);
}
試算表加上客製選單
這個功能蠻有趣的，就是可以在 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 後就會看見導覽列上多了一個選項：
更新試算表的客製選單
這跟新增客製選單很像，要注意的是，如果原本就有加上客製選單，再用更新選單時，選單名稱相同會覆蓋，不同的才會新增。
// 選單名稱相同會覆蓋原本 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 就是指出現在畫面上的一個小視窗，通常會有時間性，比方三秒或五秒後自動消失。
function toast() {
s.toast("Let's Write - 這是一個 Toast 的內文部份。", "開啟一個 Toast");
}
Sheets 上的 Toast 長這樣：
工作表好用函式
從名稱找工作表
範例中是抓出工作表，並且 Log 出該工作表排序第幾。
function getSheetByName() {
const sheet = s.getSheetByName('測試表1');
if (sheet != null) {
Logger.log(sheet.getIndex());
}
}
取得工作表的名稱
範例中是先用 getSheets()[0] 取出第一張工作表，然抓再取出工作表名稱。
function getSheetName() {
const sheet = s.getSheets()[0];
Logger.log(sheet.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);
}
清空工作表的值
文件：clear()
這個就不示範了，因為一用會整張工作表的值都被清空。
function clear() {
const first = s.getSheetByName('測試表1');
first.clear();
}
工作表最底部新增一列值
這個 function 好用，要塞資料到 Sheet，就寫一個迴圈不斷用 appendRow 就對了。
function appendRow() {
const sheet = s.getSheets()[0];
sheet.appendRow(['ID', '姓名', 'email', '電話']);
}
取得最後有值的列是第幾列
文件：getLastRow()
比方我們的 Demo 共有 6 列資料，用 getLastRow 就會回傳 6。
function getLastRow() {
const sheet = s.getSheets()[0];
const lastRow = sheet.getLastRow();
Logger.log(lastRow);
}
取得最後有值的欄是第幾欄
比方我們的 Demo 共有 4 列資料，用 getLastColumn 就會回傳 4。
function getLastColumn() {
const sheet = s.getSheets()[0];
const lastCol = sheet.getLastColumn();
Logger.log(lastCol);
}
結合應用，直接取整張工作表的值
這邊結合上面三個 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);
}
原始碼
在放在 Google Apps Script 上，請自行點選檔案切換觀看：
Top comments (0)