本篇記錄用 GAS 操作 Google 試算表的測試結果.
試算表 (SpreadSheetApp) 是 Google 雲端硬碟最常用的 App, 除了可以取代 Excel 外, 它還可以做為一個小型資料庫. GAS 提供豐富的 API 來存取與操控試算表, 搭配觸發器可以打造免費的雲端自動化應用, 教學文件參考 :
GAS 程式可以是獨立放在 Google 雲端硬碟下的 .gs 檔; 也可以是寄生在各種 Google Apps (文件, 試算表, 或表單等), 它們的差別在於 GAS API 的存取範圍, 以存取試算表為例, SpreadSheetApp 物件提供了四個方法 (API) 來取得試算表 (SpreadSheet) 或工作表 (Sheet) 物件 :
| SpreadSheetApp 物件方法 | 說明 |
| openById(id) | 根據 ID 開啟試算表, 傳回 SpreadSheet 物件 |
| openByUrl(url) | 根據 URL 開啟試算表, 傳回 SpreadSheet 物件 |
| getActiveSpreadsheet() | 傳回使用中的試算表物件 (SpreadSheet), 僅寄生的 GAS 可用. |
| getActiveSheet() | 傳回使用中的工作表 (Sheet) 物件, 僅寄生的 GAS 可用. |
注意, getActiveSpreadsheet() 與 getActiveSheet() 這兩個方法只能在寄生的 GAS 程式中可以呼叫, 而 openById() 與 openByUrl() 則不論是在獨立的 GAS 檔或寄生的 GAS 程式碼中均可呼叫.
一. 新增試算表 :
為了以下測試, 我們先在雲端硬碟建立一個 GAS 資料夾, 並在其下建立一個試算表, 並將其更名為 myproject :
將底下預設的工作表更名為 scores, 然後在儲存格中輸入成績資料 :
點選上方 "擴充功能" 選單, 再點選 "Apps Script" 開啟一個 GAS 程式編輯視窗 :
把上方預設的 GAS 專案名稱改為 scores :
二. 用 GAS 操作試算表 :
操作試算表之前須先開啟試算表取得 Spreadsheet 物件, 然後利用其方法來存取儲存格內容. 對於一個已建立之試算表, 我們可以透過下列 SpreadSheetApp 物件的四個方法開啟它 :
- openById(id)
- openByUrl(url)
- getActiveSpreadsheet()
- getActiveSheet()
如前所述, 前兩者不管是獨立的 GAS 程式或寄生於試算表內的 GAS 程式都可呼叫, 但後兩者只有寄生的 GAS 程式可呼叫. 它們都會傳回一個 Spreadsheet 物件, 它有許多方法, 其中最常用的是 getSheetByName(), 只要傳入工作表名稱就會傳回一個 Sheet 物件, 呼叫其 getRange() 方法就能定位出儲存格取得 Range 物件, 最後呼叫 Range 物件的 setValue() 與 getValue() 方法就可以存取儲存格內容了.
注意, 若 getRange() 選取的是單一儲存格, 存取時是呼叫 getValue() 與 setValue(); 若選取一個區域, 則存取時是呼叫 getValues() 與 setValues(), 傳回值或傳入值均為陣列.
首先來釐清試算表的 ID 與 URL, 在試算表視窗上方的網址列就是此試算表的 URL, 例如 :
https://docs.google.com/spreadsheets/d/1ZEGvDt7JtglqVS3IuAPyelSOOjWe_BHN0jT4R7rHNk4/edit#gid=0
其中 https://docs.google.com/spreadsheets/d/ 後面一直到 /edit... 之間的字串就是其 id :
以下分別測試這三種開啟試算表的方式 :
1. 呼叫 openByUrl() 開啟試算表並存取工作表 :
只要將試算表的 URL 傳入 openByUrl() 方法即可開啟試算表, 它會傳回一個 SpreadSheet 物件. 在 GAS 程式編輯視窗輸入下列程式碼 :
function myFunction() {
var url="https://docs.google.com/spreadsheets/d/1ZEGvDt7JtglqVS3IuAPyelSOOjWe_BHN0jT4R7rHNk4/edit#gid=0";
var ss=SpreadsheetApp.openByUrl(url); //傳回 Spreadsheet 物件
var sheet=ss.getSheetByName('scores'); //傳回 Sheet 物件
var range=sheet.getRange(2, 1); //取得第 2 列第 1 欄儲存格 Range 物件
Logger.log(range.getValue()); //取得第 2 列第 1 欄儲存格內容 ('金智媛')
}
此程式用 openByUrl() 取得試算表物件 ss, 然後呼叫 ss 的 getSheetByName() 取得名為 scores 的工作表物件 sheet, 接著呼叫 sheet 的 getRange() 方法取得第 2 列第 1 欄的儲存格物件 range, 最後呼叫 range 物件的 getValue() 取得儲存格內容.
注意 getRange() 的參數結構有兩種, 第一種是使用索引來選取 :
getRange(row, column [, numRows, numColumns])
前兩個參數分別是列索引與欄索引 (都是 1 起始, A 欄為 1, B 欄為 2, ...); 後兩個為可有可無參數, 分別為列數與欄數 (用來選取一個矩形區域之儲存格).
第二種是使用試算表的欄列名稱標示法 (傳入值為字串), 例如 :
getRange('A2:C6') //選取 A2~C6 範圍內之儲存格
也可以跳過取得工作表物件步驟, 直接呼叫 Spreadsheet 物件的 getRange() 方法, 但在傳入參數前面添加工作表名稱 (用 ! 隔開欄列名稱), 例如 :
ss.getRange('scores!A2:C6') //選取 scores 工作表的 A2~C6 範圍內之儲存格
執行結果如下 :
上面程式碼是取得單一儲存格, 如果 getRange() 時是選取一個區域, 則要呼叫 getValues(), 它會傳回一個陣列, 例如將上面的程式碼改成如下 :
function myFunction() {
var url="https://docs.google.com/spreadsheets/d/1ZEGvDt7JtglqVS3IuAPyelSOOjWe_BHN0jT4R7rHNk4/edit#gid=0";
var ss=SpreadsheetApp.openByUrl(url); //傳回 Spreadsheet 物件
var sheet=ss.getSheetByName('scores'); //傳回 Sheet 物件
var range=sheet.getRange(2, 1, 3, 4); //取得第 2 列第 1 欄起 3 列 4 欄的區域儲存格 Range 物件
Logger.log(range.getValues()); //取得第 2 列第 1 欄起 3 列 4 欄的區域內容 (陣列)
}
注意, 此處是選取一個區域的儲存格, 所以應該呼叫 getValues() 而非 getValue(), 結果會傳回三個學生的全部成績, 結果如下 :
2. 呼叫 openById() 開啟試算表並存取工作表 :
將上面程式碼改為呼叫 openById() 來開啟試算表, 同樣會傳回一個 Spreadsheet 物件 :
function myFunction() {
var id="1ZEGvDt7JtglqVS3IuAPyelSOOjWe_BHN0jT4R7rHNk4";
var ss=SpreadsheetApp.openById(id); //傳回 Spreadsheet 物件
var sheet=ss.getSheetByName('scores'); //傳回 Sheet 物件
var range=sheet.getRange(2, 1, 3, 4); //取得第 2 列第 1 欄起 3 列 4 欄的區域儲存格 Range 物件
Logger.log(range.getValues()); //取得第 2 列第 1 欄起 3 列 4 欄的區域內容 (陣列)
}
執行結果與上面相同 :
3. 呼叫 getActiveSpreadsheet() 開啟試算表並存取工作表 :
將上面程式碼改為呼叫 getActiveSpreadsheet() 來開啟試算表, 同樣會傳回一個 Spreadsheet 物件 :
function myFunction() {
var ss=SpreadsheetApp.getActiveSpreadsheet(); //傳回 Spreadsheet 物件
var sheet=ss.getSheetByName('scores'); //傳回 Sheet 物件
var range=sheet.getRange(2, 1, 3, 4); //取得第 2 列第 1 欄起 3 列 4 欄的區域儲存格 Range 物件
Logger.log(range.getValues()); //取得第 2 列第 1 欄起 3 列 4 欄的區域內容 (陣列)
}
結果如下 :
也可以呼叫 getActiveSheet() 方法直接取得使用中的工作表, 例如 :
function myFunction() {
var sheet=SpreadsheetApp.getActiveSheet(); //傳回使用中的 Sheet 物件 (工作表)
var range=sheet.getRange(2, 1, 3, 4); //取得第 2 列第 1 欄起 3 列 4 欄的區域儲存格 Range 物件
Logger.log(range.getValues()); //取得第 2 列第 1 欄起 3 列 4 欄的區域內容 (陣列)
}
執行結果與上面相同 :
注意, getActiveSpreadsheet() 與 getActiveSheet() 方法只能在寄生於試算表中的 GAS 程式碼可呼叫, 無法於獨立的 GAS 程式中使用.
4. 於獨立的 GAS 程式中開啟試算表 :
上面的測試中所使用的 GAS 是寄生於試算表的程式碼, 可以使用 SpreadsheetApp 物件的openByUrl(), openById(), 與 getActiveSpreadsheet() 方法開啟試算表, 但在獨立於 App 外的 .gs 檔案中, 只能呼叫 openByUrl() 與 openById() 這兩個方法來開啟試算表.
首先按雲端硬碟底下的 "+ 新增" 鈕, 點選 "更多..." 後再點選 "Google Apps Script" :
這時會彈出一個視窗, 提醒此資料夾的所有協作者皆能存取此 GAS 檔, 按 "建立指令碼" 鈕會開啟一個 "未命名的專案" 的 Google Apps 程式編輯視窗 :
這時切回雲端硬碟視窗會發現底下出現一個 "未命名的專案.gs" 程式檔 :
點選 Google Apps 程式編輯視窗左上角的 "未命名的專案" 將其重新命名為 "myproject" :
這時切回雲端硬碟視窗, 原本的 "未命名的專案.gs" 也會更名為 "myproject.gs" :
然後編輯 GAS 程式專案 myproject 裡面的預設函式 myFunction(), 輸入如下程式碼 :
function myFunction() {
var url="https://docs.google.com/spreadsheets/d/1ZEGvDt7JtglqVS3IuAPyelSOOjWe_BHN0jT4R7rHNk4/edit#gid=0";
var ss=SpreadsheetApp.openByUrl(url); //傳回 Spreadsheet 物件
var sheet=ss.getSheetByName('scores'); //傳回 Sheet 物件
var range=sheet.getRange(2, 1, 3, 4); //取得第 2 列第 1 欄起 3 列 4 欄的區域儲存格 Range 物件
Logger.log(range.getValues()); //取得第 2 列第 1 欄起 3 列 4 欄的區域內容 (陣列)
}
按 "存檔" 鈕後按 "執行" 會出現授權頁面, 按 "審查權限" 鈕 :
選擇自己的 Google 帳戶 :
在警示視窗中按 "進階" :
按右下角的 "允許" 即完成授權 :
完成授權即馬上會執行 GAS 程式檔, 結果與上面寄生於試算表的 GAS 程式相同 :
也可以更改 myproject.gs 檔改用 openById() 開啟試算表 :
function myFunction() {
var id="1ZEGvDt7JtglqVS3IuAPyelSOOjWe_BHN0jT4R7rHNk4";
var ss=SpreadsheetApp.openById(id); //傳回 Spreadsheet 物件
var sheet=ss.getSheetByName('scores'); //傳回 Sheet 物件
var range=sheet.getRange(2, 1, 3, 4); //取得第 2 列第 1 欄起 3 列 4 欄的區域儲存格 Range 物件
Logger.log(range.getValues()); //取得第 2 列第 1 欄起 3 列 4 欄的區域內容 (陣列)
}
結果與上面相同.
