GoogleAppScript-改造你的Google試算表-2
If you would like to see the article in English, please go to the following page.
什麼是GoogleAppScript
Google App Script （簡稱GAS）是一個由 Google 提供的腳本引擎，用於開發基於 Google Workspace 的應用程式。它可以使用 JavaScript 編寫其程式語言，並且允許開發人員利用 JavaScript 語言建立Google 試算表、Google 表單、Google 文件和其他 Google 應用程式的自動化工具和應用程式。
因此，GoogleAppScript具備了簡單易用、具備整合性、擴展 Google 應用程式、可運作於雲端伺服器以及隨時隨地開發與部署等優勢。
GAS與Google試算表
前面文章提到，Google Workspace 中Google 試算表的功能和介面與Microsoft Office軟體中的Excel相似，都可以用表格來儲存各項數值與公式，用儲存格中的資料產生圖表，甚至於可以將儲存格中的資料匯出或者是從不同試算表檔案格式匯入試算表中。Google App Script可以與Google 試算表結合應用。藉此可以利用GAS指令碼撰寫程式，來將平日瑣碎的工作進行自動化處理增加工作效率。由於透過GAS指令碼操作試算表的方式多且繁雜，接下來將繼續介紹之。
四個開啟並連結試算表的方式
透過API與Google試算表連結的方式有四種，除了上一篇介紹過的 openById 與 openByUrl 外，還有 getActiveSheet() 與 getActiveSpreadsheet()。其間的差別何在？
還記得我們在上一篇提到過GAS 具備兩種運作模式，第一種是「附屬」於 Google Workspace 服務，而另一種則獨立作為獨立檔案運作。這裡提到的四種開啟並連結試算表的方式中，前面兩種主要用於GAS 指令碼作為獨立檔案存在並與被操作的Google試算表分開之狀況下；而後面兩種方式，則是GAS 指令碼必須「附屬」於Google試算表中。以下分別介紹後面兩種方式：
getActiveSpreadsheet()
getActiveSpreadsheet()可以取得試算表裡面的所有工作表。
前面提到，使用getActiveSpreadsheet的前提必須是GAS指令碼附屬於 Google Workspace 服務。因此，若要透過getActiveSpreadsheet連結Google試算表，則必須將指令碼寫在由Google試算表中開啟的GAS裡面，如果是透過獨立GAS操作試算表的話，則無法使用這個函式。
也因為這函式必須存在於試算表內嵌入的GAS裡面。既然要從試算表「裡面」操縱它，就不需要再多此一舉的指定連結試算表的id( openById )與url( openByUrl )這些外部資訊了。
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
Logger.log(ss.getUrl());
}
getActiveSheet()
這個函式可以從Google試算表檔案中，取得目前正在使用中的工作表頁面。不過，與前面的getActiveSpreadsheet()一樣，它需要寫在Google試算表內嵌入的app scrpit裡面，否則無法使用這個函式，然而，一旦寫Google試算表內，就會自動與該試算表進行連結。
var ss = SpreadsheetApp.getActiveSheet();與前面 getActiveSpreadsheet()不同的是， getActiveSheet() 只能取得試算表中目前正在使用中的一個工作表。
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
Logger.log(ss.getSheetName());
}執行上面的指令碼，可以得到如下的結果：
由於目前試算表中正在使用的工作表為Sheet2，因此得到此結果。
取得工作表：getSheets()
與試算表連結之後，我們可以透過 getSheets()取得試算表中所有的工作表。從GAS的官方說明裡面可以看到下面的指令碼示範：
// The code below logs the name of the second sheet
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
if (sheets.length > 1) {
Logger.log(sheets[1].getName());
}
如果試算表中有多個工作表，而我們要取得其中某個工作表時，可以在getSheets() 後面加上數字來指定工作表的位置。例如：使用getSheets()[0]表示試算表中的第一個工作表。
var ss = SpreadsheetApp.getActiveSheet();
var sheet = ss.getSheets()[0];取得儲存格
當我們透過前面任何一種方式，與Google試算表進行連結之後，就可以對試算表裡內的各工作表以及工作表內的儲存格資料進行各種操作。而在編輯儲存格之前，需要先取得儲存格的位置，以及決定儲存格的範圍。
getRange()取得儲存格
在將資料寫入或讀取儲存格之前，我們需要使用 .getRange() 方法來定位目標儲存格。 .getRange()可用於獲取單個儲存格，也可用於獲取某個範圍內的所有儲存格。此方法具有多種不同的參數設置，可用於獲取不同範圍內的資料。
取得單個儲存格
.getRange()可以透過下列方式取得某個特定的儲存格。
# 輸入行列位置 getRange(row, column)
若想要選擇特定的儲存格，可以透過 getRange() 函式並指定列(row)和欄(column)的值作為參數，來定位目標儲存格：
function selectCell() {
// 載入 Google Sheet
var ss = SpreadsheetApp.openById('1P0aLTKX-73azlCoTaC-9YjA').getSheets()[0];
// 選取第一行第一列的儲存格，並設定其背景色為黃色
var cell = ss.getRange(1, 1);
cell.setBackground('#ffff00');
}
以上指令碼會將指定的儲存格（第一行第一列）背景色設定為黃色，您可以根據需要修改顏色值，以符合您的需求。
# 輸入位置名稱 getRange(a1Notation)
下面的範例指令碼展示了如何透過 getRange() 函式，選取試算表中的特定儲存格，並設定其背景顏色。
function selectCell() {
// 載入 Google Sheet
var ss = SpreadsheetApp.openById('1P0aLphMHQEbTKX-73azlCA').getSheets()[0];
// 選取 A2 儲存格，並設定其背景色為黃色
var cell = ss.getRange('A2');
cell.setBackground('#ffff00');
}
執行結果如下，特定的儲存格A2的背景色被設定為黃色：
值得注意的是，getRange() 函式接受一個字串參數，該參數可以是儲存格的位置（例如，A2），也可以是一個儲存格區域（例如，A1:B2）。在此範例中，我們使用了儲存格位置 'A2'，以選擇單一儲存格。如果您需要選擇多個儲存格，則可以使用 getRange() 函式中指定儲存格範圍的方式，例如getRange( ‘A1:B2’)。
取得某個範圍的儲存格
getRange() 函式不僅可以取得單一儲存格，還可以取得一定範圍內的所有儲存格。以下是取得某個範圍儲存格的方式：
＃ 透過指定起始儲存格位置及範圍大小（列數與行數），例如 getRange(row, column, numRows, numColumns)
以下範例示範了如何透過 getRange() 函式，選取試算表中指定範圍的儲存格，並設定它們的背景顏色：
function selectRange() {
// 載入 Google Sheet
var ss = SpreadsheetApp.openById('1P0aLphMHQE1gSbTKX-9YjA').getSheets()[0];
// 選擇從第一行第一列開始，連續三行一列的儲存格範圍，並設定其背景色為黃色
var cell = ss.getRange(1, 1, 3, 1);
cell.setBackground('#ffff00');
}
執行結果，從第一行第一列開始，連續三行一列的儲存格範圍，並設定其背景色為黃色：
以上範例中，我們使用了 getRange() 函式的四個參數，分別是起始儲存格位置的列與行（1, 1），以及儲存格範圍的大小（3, 1）。這代表我們選擇的儲存格範圍，從第一列第一行的儲存格開始，連續選擇三列一行的儲存格。如果您需要選擇更大的儲存格範圍，可以調整 getRange() 函式的參數以符合個人的需求。
＃ 前面透過指定起始儲存格位置及範圍大小getRange(row, column, numRows, numColumns)的方式，也可以只輸入三個參數，如下面的指令碼：
function myCellOne() {
var ss = SpreadsheetApp.openById('1P0aLphMHQE8uDhFr1gSbTKXzlCoTaC-9YjA');
var cell = ss.getRange(1,1,3);
cell.setBackground('#ffff00');
}
指令碼的第三行使用了 getRange 方法來選擇了一個 3 行 1 列的儲存格範圍。第一個參數 1 代表選擇範圍的開始行數，第二個參數 1 代表選擇範圍的開始列數，第三個參數 3 代表選擇範圍的行數。這樣就會選擇從第一行第一列開始的 3 行 1 列的儲存格範圍。第四行指令碼使用了 setBackground 方法，將選擇的儲存格範圍的背景顏色設置為亮黃色。程式執行結果與前面使用四個參數一樣。
編輯儲存格
前面的鋪陳都是為了讓我們可以編輯儲存格，讓我們可以存取和修改儲存格中的資料。當編輯儲存格時，一般來說，我們會使用一些方法來取得儲存格中的資料，或者將資料寫入儲存格中。以下將分成這兩個部分，分別介紹取得資料和寫入資料的方法。
取得資料
在編輯儲存格時，我們經常需要讀取儲存格中的資料。這可以透過使用getValue()和getValues()方法來完成。getValue()方法可以返回單個儲存格中的值，而getValues()方法則可以返回一個包含多個儲存格值的二維數組。
# getValue()
使用 getValue() 方法，可以用來讀取儲存格資料。例如，想要讀取 A1 儲存格中的值（如上圖），可以使用以下的指令碼：
var sheet = SpreadsheetApp.getActiveSheet();
var value = sheet.getRange("A1").getValue();在上面的代碼中，getRange() 方法返回一個 Range 物件，代表 A1 儲存格。接著使用 getValue() 方法讀取儲存格中的值，並將其賦值給變數 value。
首先，要透過Google試算表「擴充功能」選單進入「Apps Script」，開啟編輯器，並且將上方的指令碼寫入編輯器。
最後，將讀取到的值用 Logger.log() 方法輸出，以便檢查讀取到的值是否正確。
Logger.log(value);執行指令碼的結果如下：應該會在「執行紀錄」中出現『Bien』字樣，因為Bien這個字串，位於試算表的A1儲存格中。
# getValues()
使用 getValues() 方法，可以讀取 Google Sheets 中的多個儲存格的資料。以下是使用 getValues() 方法來讀取一個儲存格範圍的指令碼：
首先，在編輯器中使用 getRange() 方法選擇要讀取資料的儲存格範圍。
以下面指令碼來說，要讀取 A1:B2 儲存格範圍的值，可以使用以下方法：
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:B2").getValues();在上面的指令碼中，getRange() 方法會返回一個代表 A1:B2 儲存格範圍的Range 物件。
接著使用 getValues() 方法讀取儲存格範圍中的所有值，並將取得的值賦給變數 values。getValues() 方法返回一個二維的陣列（[[Hello, Hola], [こんにちは, 您好]]），其中每個元素代表一個儲存格的值。並將讀取到的值用 Logger.log() 方法輸出，以便檢查讀取到的值是否正確。
Logger.log(values);二維值的陣列，依序逐列建立索引，然後逐欄建立索引。它的值可能為 Number、Boolean、Date 或 String等類型，端視儲存格的值而定。若儲存格為空白時，會在陣列中以空白字串表示。
最後，執行指令碼並讀取指定的儲存格範圍的資料。
在「執行紀錄」的地方，會返回一個二維的陣列（[[Hello, Hola], [こんにちは, 您好]]）。
綜上所述，使用getValue()與getValues()方法可以方便地讀取 Google Sheets 中的儲存格資料，讓你更輕鬆地進行資料的處理和分析。
寫入資料
除了讀取儲存格中的資料外，編輯儲存格還需要能夠將新的資料寫入儲存格中。這項工作可以透過使用setValue()和setValues()方法來完成。
setValue()方法可以將單一值寫入儲存格中，而setValues()方法可以將多個值寫入一個或多個儲存格中。以下是分別介紹使用 setValue() 和 setValues() 方法將資料寫入單一儲存格和多個儲存格中。
# setValue()
首先，我們需要在IDE編輯器中使用 getRange() 方法選擇要寫入資料的儲存格。例如，要寫入 A1 儲存格的值為「Hola」，可以使用以下指令碼：
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue("Hola");在上面的指令碼中，當getRange() 方法返回一個代表 A1 儲存格的Range 物件後，接著就可以使用 setValue() 方法將指定的值寫入儲存格中。
執行後，結果在A1儲存格出現「Hola」。
# setValues()
如果一次要寫入多個儲存格，可以使用 setValues() 方法。例如，要將一個二維陣列寫入 A1:B2 儲存格範圍中，可以使用以下指令碼：
var sheet = SpreadsheetApp.getActiveSheet();
var values = [["1A", "1B"], ["2A", "2B"]];
sheet.getRange("A1:B2").setValues(values);在上面的指令碼中，setValues()方法接受一個二維陣列作為參數，將其寫入儲存格範圍中。每個元素代表一個儲存格的值。
我們可以使用 getValues() 方法檢查寫入的值是否正確。例如，可以使用以下指令碼獲取 A1:B2 儲存格範圍的值：
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:B2").getValues();
Logger.log(values);最後，執行指令碼並將指定的資料寫入到儲存格中。
執行後，結果在A1到B2的儲存格中，分別出現1A, 1B（第一行）與2A, 2B（第二行）。
藉由上面的示範可以發現，使用 setValue() 和 setValues() 方法可以方便地將資料寫入 Google Sheets 中的儲存格，讓你更輕鬆地進行資料的輸入和處理。
結論
綜合以上所述，Google App Script是一款強大的工具，能夠提供使用者豐富的編輯試算表功能。
使用時，使用者可以根據自己的需求從四種開啟並連結試算表的方式中選擇一種來進行連結並且編輯試算表。包括透過試算表ID連結、透過試算表的URL連結、或者是在試算表中透過 getActiveSheet() 與 getActiveSpreadsheet()連結。
在編輯儲存格的過程中，getRange()方法能夠取得單一儲存格或一定範圍的儲存格，getValue()與getValues()方法能夠取得儲存格或範圍中的資料，setValue()與setValues()方法能夠寫入資料。這些編輯儲存格的方式讓使用者可以更有效地操作試算表中的資料，並加速完成各種工作。
最後，Google App Script對於需要處理大量資料的使用者來說，是一個相當實用的工具，能夠大幅提升工作效率，是一款非常值得使用的工具，能夠幫助使用者更輕鬆地編輯和管理試算表。
值得一提的是，由於篇幅的限制，本文只介紹了Google App Script的部分功能，還有更多強大的功能等待您去發掘和使用。如果您對Google App Script感興趣，下一篇文章將繼續介紹Google 試算表的其他操作方式，敬請期待。
