SpreadsheetApp

Google Apps Script 試算表類別

【學習目標】

1. 學習使用GAS提供的API，連接Google試算表。

2. 了解Google試算表四種不同的連接方式與應用時機。

3. 依範例完成Goolge試算表儲存格的存取。

與試算表連結

API 試算表與指令碼分開試算表中執行指令碼

openById() OK OK

openByUrl() OK OK

getActiveSheet No OK (只取得試算表的第一個工作表)

getActiveSpreadsheet() No OK (試算表中的所有工作表)

API的寫法

以將類別、方法、屬性宣告為變數方式來撰寫，程式較短且容易瞭解。

1.openById

function myFunction(){

var ss = SpreadsheetApp.openById("1KmHRQo4N-AjvuzaO_blZLvXgonaUGCT0Wn_rn3LKapQ");

var SheetName = ss.getSheetByName("sheet1");

var range = SheetName.getRange(1,1); //取得儲存格A1

range.setValue(123); //儲存格A1=123(數字)

}

試算表ID為：紅色框住部分。

2.openByUrl

function myFunction(){

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1KmHRQo4N-AjvuzaO_blZLvXgonaUGCT0Wn_rn3LKapQ/");

var SheetName = ss.getSheetByName("sheet1");

var range = SheetName.getRange(2,2); //取得儲存格B2

range.setValue("123"); //儲存格B2='123'(字串)

}

試算表URL為：紅色框住部分。

3.getActiveSheet

function myFunction(){

SpreadsheetApp.getActiveSheet().getRange(2,2).setValue("123");

//取得第一個(開啟中?)工作表儲存格B2，儲存格B2='123'(字串)，結果與上一個例子相同，不鼓勵用此寫法。

}

※.getRange(row: any, column: any):

使用宣告變數方式來寫

function myFunction(){

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange(2,2); //取得第一個(開啟中?)工作表儲存格B2

range.setValue("123"); //儲存格B2='123'(字串)

}

4.getActiveSpreadsheet

function myFunction(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var range = ss.getRange("B2"); //取得儲存格B2

range.setValue("ABC"); //儲存格B2='ABC'

}

※.getRange(a1Notation: string): SpreadsheetApp.Range

其他範例

1.類別、方法、屬性說明

SpreadsheetApp.getActiveSheet().getRange(2,2).setValue(123);

類別：SpreadsheetApp

方法或屬性：getActiveSheet() //開啟中?的工作表

方法或屬性：getRange(2,2) //取得B2儲存格

參數：setValue(123) //將123寫入B2

2.添加儲存格公式

SpreadsheetApp.getActiveSpreadSheet().getRange("C1").setFormula("=SUM(A1:B1)");

類別：SpreadsheetApp

方法或屬性：getActiveSpreadSheet() //與試算表連結

方法或屬性：getRange("C1") //取得C1儲存格

方法或屬性：setFormula() //向儲存格加入公式

參數：=SUM(A1:B1)

取得儲存格：getRange()

1.getRange(row, column)

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange(1,1);

range.setValue('getRange');

2.getRange(row, column, numRows)

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange(1,2,5);

range.setValue('getRange');

3.getRange(row, column, numRows, numColumns)

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange(1,2,5,4);

range.setValue('getRange');

4.getRange(a1Notation)

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange('工作表2!A2:C5');

range.setValue('getRange');

寫入資料：setValue()、setValues()

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange('Aa:C3');

range.setValues([1,2,3], [11,22,33], [111,222,333]);

取得資料：getValue()、getValues()

var ss = SpreadsheetApp.getActiveSheet();

var range = ss.getRange(1,1,3,3);

var value = range.getValues();

Logger.log(value);

【參考文獻】

取得列數，讀取儲存格內容：https://ithelp.ithome.com.tw/articles/10268417?sc=iThomeR (打勾發信)

(可以拿來檢查某帳號的密碼，或是依學號取得學生姓名、電子郵件等。)