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 上,在這邊先提供給大家:

概念:試算表 > 工作表

這邊先說明一個基本概念,我們一般會說「你那個 Excel 的檔案整理好後寄給我」,我們講 Excel 時腦子裡想到的會是 Excel === 試算表 === 工作表 === Google Sheets。
實際上看過官方文件後,就會理解到一個 Google Sheets 的檔案,就是「試算表」(Spreadsheet),而一個試算表裡我們會開有很多張表,那些表就是「工作表」(Sheet)。
看文件會看見命變數時,第一行常常會是:
var ss = SpreadsheetApp.getActiveSpreadsheet();
就是在命這整個試算表本身。

建立模擬資料

為了可以實際操作看效果,August 有建立了一個 Demo 用的試算表,裡面的資料都是用以前寫的這篇「如何用 Postman Mock Server 快速建立 API Server」建立的。
Demo 用的試算表內容如下:
裡面有二張工作表:測試表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

新增工作表

新增的工作表,位置會插入在執行中的工作表之後。
比方我們目前在執行中的工作表是「測試表1」,那新增的工作表就會插在測試表1之後。
function insertSheet() {
  s.insertSheet('測試新增工作表');
}
執行後:
插入在執行中的工作表之後

刪除工作表

刪除指定的工作表,抓到工作表就可以刪除,範例示範的是刪除指定的工作表名稱。
function deleteSheet() {
  const sheet = s.getSheetByName('測試新增工作表');
  s.deleteSheet(sheet);
}
刪除工作表 deleteSheet

試算表加上客製選單

這個功能蠻有趣的,就是可以在 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 長這樣:
右下角彈出視窗 toast

工作表好用函式

從名稱找工作表

範例中是抓出工作表,並且 Log 出該工作表是排序第幾。
function getSheetByName() {
  const sheet = s.getSheetByName('測試表1');
  if (sheet != null) {
    Logger.log(sheet.getIndex());
  }
}
從名稱找工作表 getSheetByName

取得工作表的名稱

範例中是先用 getSheets()[0] 取出第一張工作表,然抓再取出工作表名稱。
function getSheetName() {
  const sheet = s.getSheets()[0];
  Logger.log(sheet.getSheetName());
}
取得工作表的名稱 getSheetName

取得工作表的值

範例中提供了二種抓工作表所有值的方法: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

工作表最底部新增一列值

這個 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

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

比方我們的 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);
}
結合應用,直接取整張工作表的值

原始碼

在放在 Google Apps Script 上,請自行點選檔案切換觀看:
avatar-img
9會員
19內容數
沙龍到底是…做什麼用的勒?
留言0
查看全部
avatar-img
發表第一個留言支持創作者!
Let's Write 的沙龍 的其他內容
探索如何在 VS Code 中養一隻療癒的小寵物。本文將指導你安裝和使用 vscode-pets 擴充功能,讓可愛的寵物陪伴你一同寫程式。你可以選擇不同類型和顏色的寵物,甚至改變它們的遊玩場景,為你的編程時光增添樂趣和舒適。
2023 年 3 月第 3 週:OpenAI GPT-4 降臨。微軟推出 Microsoft 365 Copilot,Office AI 助手。FBI 首長:中國可透過 TikTok 控制數據 影響對台論述。IG 業配行情及技巧。
這篇文章教你如何使用 DOM to Image 進行網頁截圖。內容包括如何安裝和使用 DOM to Image,注意事項,以及一個實用的 Demo 和原始碼。文章還探討了 Notion AI 生成的文章,並在最後提供了一個關於生成式 AI 影響的彩蛋。
這篇文章教你如何使用 heic2any.js 來轉換 HEIC 格式的圖片。內容包括 HEIC 格式的介紹、如何使用 heic2any.js、處理從 iPhone 上傳的 HEIC 格式圖片,以及處理 API 回應的 HEIC Base64 格式圖檔的方法。文章提供了實用的 Demo 和原始碼。
探索如何在 VS Code 中養一隻療癒的小寵物。本文將指導你安裝和使用 vscode-pets 擴充功能,讓可愛的寵物陪伴你一同寫程式。你可以選擇不同類型和顏色的寵物,甚至改變它們的遊玩場景,為你的編程時光增添樂趣和舒適。
2023 年 3 月第 3 週:OpenAI GPT-4 降臨。微軟推出 Microsoft 365 Copilot,Office AI 助手。FBI 首長:中國可透過 TikTok 控制數據 影響對台論述。IG 業配行情及技巧。
這篇文章教你如何使用 DOM to Image 進行網頁截圖。內容包括如何安裝和使用 DOM to Image,注意事項,以及一個實用的 Demo 和原始碼。文章還探討了 Notion AI 生成的文章,並在最後提供了一個關於生成式 AI 影響的彩蛋。
這篇文章教你如何使用 heic2any.js 來轉換 HEIC 格式的圖片。內容包括 HEIC 格式的介紹、如何使用 heic2any.js、處理從 iPhone 上傳的 HEIC 格式圖片,以及處理 API 回應的 HEIC Base64 格式圖檔的方法。文章提供了實用的 Demo 和原始碼。
你可能也想看
Google News 追蹤
Thumbnail
隨著理財資訊的普及,越來越多台灣人不再將資產侷限於台股,而是將視野拓展到國際市場。特別是美國市場,其豐富的理財選擇,讓不少人開始思考將資金配置於海外市場的可能性。 然而,要參與美國市場並不只是盲目跟隨標的這麼簡單,而是需要策略和方式,尤其對新手而言,除了選股以外還會遇到語言、開戶流程、Ap
Thumbnail
嘿,大家新年快樂~ 新年大家都在做什麼呢? 跨年夜的我趕工製作某個外包設計案,在工作告一段落時趕上倒數。 然後和兩個小孩過了一個忙亂的元旦。在深夜時刻,看到朋友傳來的解籤網站,興致勃勃熬夜體驗了一下,覺得非常好玩,或許有人玩過了,但還是想寫上來分享紀錄一下~
Thumbnail
Google Docs 自動化應用情境,自動依 Google Sheets 試算表、Google Forms 表單回覆、Gmail 信件、Webhook、RSS 訂閱內容,建立相應 Google Docs 文件。還可以在新建 Google Docs 文件後,自動發送 Slack 通知給團隊成員!
Thumbnail
Excel是一個強大的電子試算表軟體,不僅適用於數據分析和報表製作,還能通過VBA(Visual Basic for Applications)進行自動化和擴展功能。要使用這些進階功能,首先需要啟用開發人員選項。以下將詳細介紹在Windows和Mac版本的Excel中如何啟用這個選項。 在Wi
Thumbnail
Google 提供了免費的雲端服務 Google Apps Script (GAS) ,我們可以撰寫一些簡易的程式APP,串接其他 Google 雲端服務 如 Google Docs ,Sheets …,就能夠幫助我們利用雲端硬碟做日常工作
Thumbnail
在 Google Sheets 中,SPARKLINE 函數提供了一個方便的方法來創建迷你圖表,讓你可以輕鬆地視覺化數據。這些迷你圖表可以是折線圖、柱狀圖或其他類型,並且可以在單個儲存格中顯示。本教學將向你展示如何使用 SPARKLINE 函數來創建迷你圖表,讓你能夠快速而直觀地理解你的數據。
Thumbnail
這邊統整了關於 IMPORTRANGE 的一系列文章!IMPORTRANGE 是 Google 試算表裡面相當好用方便的函式,跟內建功能和函式結合,可以讓你更彈性地處理資料。
Thumbnail
Google 試算表在 2022 年隆重推出了 LAMBDA 函式跟它的輔助函式,讓使用者可以製作自己的函式,還可以在指定範圍內做複雜的運算。LAMBDA 的出現,讓我們在試算表的資料處理能力帶來了革命性的進步,可以更輕鬆地完成複雜的工作!一起來看看。
Thumbnail
Meiko想向大家介紹一項非常實用的技術——Google Apps Script,簡稱GAS。你可能會好奇,這個GAS到底是什麼?想象一下,如果我們有一種方法,可以讓電腦幫我們自動完成繁瑣的數據整理和檔案合併,是不是聽起來就像擁有了一個聰明的助手?
Thumbnail
已命名範圍是 Google 試算表的功能,簡單來說可以把儲存格參照範圍改成自己想要的名字,可以讓算式更好讀、減少維護時間、減少錯誤機率、在算式和可以用這個名字引用這個範圍。來看看怎麼做!
Thumbnail
對於許多企業而言,試算表是日常業務和決策過程中不可或缺的工具。它們被用於各種目的,從財務預算和盈虧分析到庫存管理和客戶數據記錄。然而,隨著業務的發展和數據量的增加,許多人會發現自己面臨著試算表管理和維護的挑戰,這些挑戰可能妨礙效率、準確性和生產力。 1. 數據管理的繁瑣性 試算表中數據的輸入
Thumbnail
新的一年來臨,我期望重新檢視我的財務報表,讓自己更輕鬆的管理財務。我希望有一張預算編列表,然後利用這些表格來審視我的預算與到時候實際的收支狀況。我發現許多財務相關的apps都無法滿足我的需求。所以,我親手製作了一個包含預算和實際支出的表格,最後將這兩者合併成一個統一的表格。
Thumbnail
隨著理財資訊的普及,越來越多台灣人不再將資產侷限於台股,而是將視野拓展到國際市場。特別是美國市場,其豐富的理財選擇,讓不少人開始思考將資金配置於海外市場的可能性。 然而,要參與美國市場並不只是盲目跟隨標的這麼簡單,而是需要策略和方式,尤其對新手而言,除了選股以外還會遇到語言、開戶流程、Ap
Thumbnail
嘿,大家新年快樂~ 新年大家都在做什麼呢? 跨年夜的我趕工製作某個外包設計案,在工作告一段落時趕上倒數。 然後和兩個小孩過了一個忙亂的元旦。在深夜時刻,看到朋友傳來的解籤網站,興致勃勃熬夜體驗了一下,覺得非常好玩,或許有人玩過了,但還是想寫上來分享紀錄一下~
Thumbnail
Google Docs 自動化應用情境,自動依 Google Sheets 試算表、Google Forms 表單回覆、Gmail 信件、Webhook、RSS 訂閱內容,建立相應 Google Docs 文件。還可以在新建 Google Docs 文件後,自動發送 Slack 通知給團隊成員!
Thumbnail
Excel是一個強大的電子試算表軟體,不僅適用於數據分析和報表製作,還能通過VBA(Visual Basic for Applications)進行自動化和擴展功能。要使用這些進階功能,首先需要啟用開發人員選項。以下將詳細介紹在Windows和Mac版本的Excel中如何啟用這個選項。 在Wi
Thumbnail
Google 提供了免費的雲端服務 Google Apps Script (GAS) ,我們可以撰寫一些簡易的程式APP,串接其他 Google 雲端服務 如 Google Docs ,Sheets …,就能夠幫助我們利用雲端硬碟做日常工作
Thumbnail
在 Google Sheets 中,SPARKLINE 函數提供了一個方便的方法來創建迷你圖表,讓你可以輕鬆地視覺化數據。這些迷你圖表可以是折線圖、柱狀圖或其他類型,並且可以在單個儲存格中顯示。本教學將向你展示如何使用 SPARKLINE 函數來創建迷你圖表,讓你能夠快速而直觀地理解你的數據。
Thumbnail
這邊統整了關於 IMPORTRANGE 的一系列文章!IMPORTRANGE 是 Google 試算表裡面相當好用方便的函式,跟內建功能和函式結合,可以讓你更彈性地處理資料。
Thumbnail
Google 試算表在 2022 年隆重推出了 LAMBDA 函式跟它的輔助函式,讓使用者可以製作自己的函式,還可以在指定範圍內做複雜的運算。LAMBDA 的出現,讓我們在試算表的資料處理能力帶來了革命性的進步,可以更輕鬆地完成複雜的工作!一起來看看。
Thumbnail
Meiko想向大家介紹一項非常實用的技術——Google Apps Script,簡稱GAS。你可能會好奇,這個GAS到底是什麼?想象一下,如果我們有一種方法,可以讓電腦幫我們自動完成繁瑣的數據整理和檔案合併,是不是聽起來就像擁有了一個聰明的助手?
Thumbnail
已命名範圍是 Google 試算表的功能,簡單來說可以把儲存格參照範圍改成自己想要的名字,可以讓算式更好讀、減少維護時間、減少錯誤機率、在算式和可以用這個名字引用這個範圍。來看看怎麼做!
Thumbnail
對於許多企業而言,試算表是日常業務和決策過程中不可或缺的工具。它們被用於各種目的,從財務預算和盈虧分析到庫存管理和客戶數據記錄。然而,隨著業務的發展和數據量的增加,許多人會發現自己面臨著試算表管理和維護的挑戰,這些挑戰可能妨礙效率、準確性和生產力。 1. 數據管理的繁瑣性 試算表中數據的輸入
Thumbnail
新的一年來臨,我期望重新檢視我的財務報表,讓自己更輕鬆的管理財務。我希望有一張預算編列表,然後利用這些表格來審視我的預算與到時候實際的收支狀況。我發現許多財務相關的apps都無法滿足我的需求。所以,我親手製作了一個包含預算和實際支出的表格,最後將這兩者合併成一個統一的表格。