透過 Google Apps Script 抓取 Google 試算表資料

Google 試算表是一個很方便的工具,讓我們可以在線上使用類似 excel 的功能,還可以分享給其他人協作,而這邊我們要來講的就是透過 Google Apps Script 讀取 Google 試算表的資料,讓我們即使不用資料庫也能更新網頁資料。

首先請到 google drive 建立一支新的 Google Apps Script:

 

接著在建立的那個檔案裡面,輸入這段 code

function goGet(e) {
  var url = 'google 試算表 url'
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  Logger.log(SpreadSheet.getName());
}

 

然後我們在 google drive 裡建立一個測試的試算表,接著複製他的網址到上述的程式裡取代掉 url 那段。

儲存之後點選上方三角形的按鈕執行,會有一段要授權的過程,再授權一下就好。

程式裡面透過 Logger.log(SpreadSheet.getActiveSheet().getName()); 就可以取得試算表的標題,可以確認看看是否確實讀取到了。

可以參考 Spreadsheet Service 看更多用法

 

接著來抓取試算表內的資料,首先確認一下你要抓的試算表

把 code 改成這樣

var url = 'url'
var name = '工作表1'
var SpreadSheet = SpreadsheetApp.openByUrl(url);
var SheetName = SpreadSheet.getSheetByName(name);

 

接著使用 getSheetValues() 來幫我們抓取試算表內的資料,可以傳入的參數有 startRow、startColumn、numRows 和 numColumns,前兩個傳入得參數代表起始的儲存格,比如 A1 就是 1,1,後面兩個參數代表從起始儲存格開始要讀幾欄跟幾列,所以如果只要 A1 那格,就要輸入這樣:

getSheetValues(1,1,1,1)

 

完整的 code 會長這樣:

function goGet(e) {
  var url = 'url'
  var name = '工作表1'
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  var SheetName = SpreadSheet.getSheetByName(name);
  Logger.log(SheetName.getSheetValues(1,1,1,1));
}

 

也可以改成透過 getRange() 跟 getValues() 的方式來取得值:

var range = SheetName.getRange(2, 1);
var values = range.getValues();
Logger.log(values);

 

如果想要抓取整個表單的資料,可以透過 getLastColumn() 和 getLastRow() 來取得最後一欄跟一列的值。

function goGet(e) {
  var url = 'url'
  var name = '工作表1'
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  var SheetName = SpreadSheet.getSheetByName(name);
  var lastColumn = SheetName.getLastColumn();
  var lastRow = SheetName.getLastRow();
  Logger.log(SheetName.getSheetValues(1,1,lastRow,lastColumn));
}