2024年3月24日 星期日

Google Apps Script (GAS) 學習筆記 (四) : 試算表操作 (上)

本篇記錄用 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 欄的區域內容 (陣列)
}

結果與上面相同.

沒有留言 :