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

更新於 發佈於 閱讀時間約 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 的執行功能,會看到如下結果:

raw-image

取得試算表的名稱

文件:getName()

取得試算表的檔案名稱。

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

取得所有工作表

文件:getSheets()

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

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

新增工作表

文件:insertSheet(sheetName)

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

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

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

執行後:

raw-image

刪除工作表

文件:deleteSheet(sheet)

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

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

試算表加上客製選單

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

raw-image

更新試算表的客製選單

文件: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 長這樣:

raw-image

工作表好用函式

從名稱找工作表

文件:getSheetByName(name)

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

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

取得工作表的名稱

文件:getSheetName()

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

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

取得工作表的值

文件: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);
}
raw-image

清空工作表的值

文件: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', '電話']);
}
raw-image

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

文件:getLastRow()

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

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

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

文件:getLastColumn()

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

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

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

這邊結合上面三個 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);
}
raw-image

原始碼

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

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

留言
avatar-img
留言分享你的想法!
avatar-img
Let's Write 的沙龍
9會員
19內容數
沙龍到底是…做什麼用的勒?
Let's Write 的沙龍的其他內容
2024/08/24
了解如何在 GitLab 中設置和使用 CodiumAI PR-Agent 進行 AI Code Review,自動檢查和改進程式碼。本文提供詳細步驟,包括環境設置、提交必要檔案,以及如何使用 OpenAI API Key 進行配置。
Thumbnail
2024/08/24
了解如何在 GitLab 中設置和使用 CodiumAI PR-Agent 進行 AI Code Review,自動檢查和改進程式碼。本文提供詳細步驟,包括環境設置、提交必要檔案,以及如何使用 OpenAI API Key 進行配置。
Thumbnail
2024/08/07
了解如何使用 Cloudflare Workers AI 與 Whisper 建立免費開源的語音辨識功能。本文詳細說明註冊步驟、部署流程及程式碼修改,讓你輕鬆將語音轉換成文字。
Thumbnail
2024/08/07
了解如何使用 Cloudflare Workers AI 與 Whisper 建立免費開源的語音辨識功能。本文詳細說明註冊步驟、部署流程及程式碼修改,讓你輕鬆將語音轉換成文字。
Thumbnail
2024/06/05
學習如何使用 Tensorflow.js 的 COCO-SSD 模型在網頁上進行圖片物件辨識,包括基本使用方法、進階應用及實作範例,輕鬆辨識圖片中的人數和物件。
Thumbnail
2024/06/05
學習如何使用 Tensorflow.js 的 COCO-SSD 模型在網頁上進行圖片物件辨識,包括基本使用方法、進階應用及實作範例,輕鬆辨識圖片中的人數和物件。
Thumbnail
看更多
你可能也想看
Thumbnail
已命名範圍是 Google 試算表的功能,簡單來說可以把儲存格參照範圍改成自己想要的名字,可以讓算式更好讀、減少維護時間、減少錯誤機率、在算式和可以用這個名字引用這個範圍。來看看怎麼做!
Thumbnail
已命名範圍是 Google 試算表的功能,簡單來說可以把儲存格參照範圍改成自己想要的名字,可以讓算式更好讀、減少維護時間、減少錯誤機率、在算式和可以用這個名字引用這個範圍。來看看怎麼做!
Thumbnail
今天的文章跟以往的教學有點不一樣,比較偏向我個人的觀點,來稍稍比較 Google 試算表跟 Excel 的不同!
Thumbnail
今天的文章跟以往的教學有點不一樣,比較偏向我個人的觀點,來稍稍比較 Google 試算表跟 Excel 的不同!
Thumbnail
高效生活,幫助你找回更多自己的時間 哈囉,這裡是 AL 的 Googlesheet 學習筆記 本系列文章,會帶你認識各種函數,學習並應用於日常,加速生活與工作、提升效率 今天要介紹的函數是 if 和 ifs
Thumbnail
高效生活,幫助你找回更多自己的時間 哈囉,這裡是 AL 的 Googlesheet 學習筆記 本系列文章,會帶你認識各種函數,學習並應用於日常,加速生活與工作、提升效率 今天要介紹的函數是 if 和 ifs
Thumbnail
來試試看用 GPT_TABLE 函式,把它加入到你的工作流裡吧!
Thumbnail
來試試看用 GPT_TABLE 函式,把它加入到你的工作流裡吧!
Thumbnail
在公民科學中,以群眾標註或是問卷得來的表單資料,常會有評分的需要,而一般評分可能會有比較複雜的邏輯需求,可能會用幾行程式來處理最為容易。
Thumbnail
在公民科學中,以群眾標註或是問卷得來的表單資料,常會有評分的需要,而一般評分可能會有比較複雜的邏輯需求,可能會用幾行程式來處理最為容易。
Thumbnail
[情報]GOOGLE APPS SCRIPT(GAS)線上課程優惠資訊 🔖課程名稱 : GOOGLE APPS SCRIPT(GAS)線上課程。 🔖課程地址 : https://reurl.cc/43v1WY。 🔖課程費用 : 募資價1590(僅30天)/正式價2690。 (另外也有
Thumbnail
[情報]GOOGLE APPS SCRIPT(GAS)線上課程優惠資訊 🔖課程名稱 : GOOGLE APPS SCRIPT(GAS)線上課程。 🔖課程地址 : https://reurl.cc/43v1WY。 🔖課程費用 : 募資價1590(僅30天)/正式價2690。 (另外也有
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News