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

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

本篇要解決的問題

之前寫過蠻多篇關於 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
取得試算表的 ID getId

取得試算表的名稱

文件:getName()

取得試算表的檔案名稱。

function getName() {
  const name = s.getName();
  Logger.log(name)  
}
取得試算表的名稱 getName
取得試算表的名稱 getName

取得所有工作表

文件:getSheets()

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

function getSheets() {
  const sheets = s.getSheets();
  if (sheets.length > 1) {
    for(let ss of sheets) {
      Logger.log(ss.getName())
    }
  }
}
取得所有工作表 getSheets
取得所有工作表 getSheets

新增工作表

文件:insertSheet(sheetName)

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

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

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

執行後:

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

刪除工作表

文件:deleteSheet(sheet)

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

function deleteSheet() {
  const sheet = s.getSheetByName('測試新增工作表');
  s.deleteSheet(sheet);
}
刪除工作表 deleteSheet
刪除工作表 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
右下角彈出視窗 toast

工作表好用函式

從名稱找工作表

文件:getSheetByName(name)

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

function getSheetByName() {
  const sheet = s.getSheetByName('測試表1');
  if (sheet != null) {
    Logger.log(sheet.getIndex());
  }
}
從名稱找工作表 getSheetByName
從名稱找工作表 getSheetByName

取得工作表的名稱

文件:getSheetName()

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

function getSheetName() {
  const sheet = s.getSheets()[0];
  Logger.log(sheet.getSheetName());
}
取得工作表的名稱 getSheetName
取得工作表的名稱 getSheetName

取得工作表的值

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

範例中提供了二種抓工作表所有值的方法:getSheetValuesgetRange

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
取得工作表的值 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
工作表最底部新增一列值 appendRow

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

文件:getLastRow()

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

function getLastRow() {
  const sheet = s.getSheets()[0];
  const lastRow = sheet.getLastRow();
  Logger.log(lastRow);
}
取得最後有值的列是第幾列 getLastRow
取得最後有值的列是第幾列 getLastRow

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

文件:getLastColumn()

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

function getLastColumn() {
  const sheet = s.getSheets()[0];
  const lastCol = sheet.getLastColumn();
  Logger.log(lastCol);
}
取得最後有值的欄是第幾欄 getLastColumn
取得最後有值的欄是第幾欄 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);
}
結合應用,直接取整張工作表的值
結合應用,直接取整張工作表的值

原始碼

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

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

Summary
Google Sheets API,在 Google Apps Script 上的好用部份
Article Name
Google Sheets API,在 Google Apps Script 上的好用部份
Description
探索 Google Sheets API 在 Google Apps Script 上的實用操作和技巧。本文詳細解釋了試算表和工作表的操作、好用函式和原始碼分享,是想要深入了解 Google Sheets API 應用的理想指南。
Augustus
Let's Write
Let's Write
https://letswrite.tw/wp-content/uploads/2020/08/logo_512.jpg
訂閱
通知
guest

0 Comments
Inline Feedbacks
看所有留言