Google 試算表適用的 Apps Script 基礎知識 #2：試算表、工作表和範圍

1. 簡介

歡迎來到「Google 試算表適用的 Apps Script 基礎知識」程式碼研究室播放清單的第二部分。先前的程式碼研究室著重於指令碼編輯器、巨集和自訂函式的概念。本程式碼研究室將深入探討 Spreadsheet 服務，您可以使用這項服務讀取、寫入及操控 Google 試算表中的資料。

課程內容

• 試算表、工作表和範圍在 Apps Script 中的表示方式。
• 如何使用 SpreadsheetApp 和 Spreadsheet 類別存取、建立及重新命名作用中 (開啟) 的試算表。
• 如何使用 Sheet 類別變更工作表名稱，以及範圍的欄/列方向。
• 如何使用 Range 類別指定、啟用、移動及排序一組儲存格或資料範圍。

事前準備

這是「Google 試算表 Apps Script 基礎知識」播放清單中的第二個程式碼研究室。開始前，請務必完成第一個程式碼研究室：巨集和自訂函式。

軟硬體需求

• 瞭解本播放清單先前程式碼研究室中探討的基本 Apps Script 主題。
• 熟悉 Apps Script 編輯器的基本概念
• 熟悉 Google 試算表的基本概念
• 可讀取試算表 A1 標記
• 熟悉 JavaScript 及其 String 類別

下一節將介紹試算表服務的核心類別。

2. Spreadsheet 服務簡介

以下四個類別涵蓋了試算表服務的基礎：SpreadsheetApp、Spreadsheet、Sheet 和 Range。本節將說明這些類別及其用途。

SpreadsheetApp 類別

深入瞭解試算表、工作表和範圍之前，請先查看其父項類別：SpreadsheetApp。許多指令碼都會先呼叫 SpreadsheetApp 方法，因為這些方法可提供 Google 試算表檔案的初始存取點。您可以將 SpreadsheetApp 視為 Spreadsheet 服務的主要類別。本文不會深入探討 SpreadsheetApp 類別。不過，本程式碼研究室稍後會提供範例和練習，協助您瞭解這個類別。

試算表、工作表和類別

在 Google 試算表中，試算表是指 Google 試算表檔案 (儲存在 Google 雲端硬碟中)，內含以資料列和資料欄整理的資料。有時試算表也稱為「Google 試算表」，就像文件稱為「Google 文件」一樣。

您可以使用 Spreadsheet 類別存取及修改 Google 試算表檔案資料。您也可以使用這個類別執行其他檔案層級作業，例如新增協作者。

「工作表」** 代表試算表的個別頁面，有時也稱為「分頁」。每個試算表可包含一或多個工作表。您可以使用 Sheet** 類別存取及修改工作表層級的資料和設定，例如移動資料列或資料欄。

重點：除了名稱之外，試算表也有專屬的 ID 字串。ID 會出現在試算表網址中，格式如下：

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

試算表中的每個工作表也有專屬的 ID 編號。這個 ID 號碼是隨機產生，但您也可以在試算表網址中看到，格式如下：

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=sheetID

您可以在這個範例網址中看到這兩個 ID：

由於試算表和工作表的 ID 是專屬的 (試算表名稱則不是)，因此建議盡可能使用 ID 指定試算表和工作表。舉例來說，您可以在 Google 雲端硬碟中建立多個同名試算表。使用 SpreadsheetApp.openById(id) 等指令碼方法開啟試算表，可避免誤改錯誤的檔案。

如要進一步瞭解 Google 試算表中的 ID，請參閱「Google 試算表簡介」。

總而言之，Spreadsheet 類別會對工作表集合執行作業，並在 Google 雲端硬碟中定義 Google 試算表檔案。Sheet 類別會在試算表中的個別工作表上運作。

Range 類別

大多數資料操作作業 (例如讀取、寫入或格式化儲存格資料) 都需要定義作業適用的儲存格。您可以使用 Range 類別，選取工作表中的特定儲存格集。這個類別的執行個體代表「範圍」，也就是試算表中的一或多個相鄰儲存格群組。您可以依據列號和欄號指定範圍，也可以使用 A1 標記法。

重要字詞：在 Apps Script 中，「作用中」工作表是指目前在 Google 試算表介面中開啟並顯示的工作表。同樣地，「有效範圍」是指目前在有效工作表中醒目顯示的一或多個儲存格群組。作用中儲存格是作用中工作表內以游標為焦點的醒目顯示儲存格。

許多類別方法都會傳回所要求類別的有效物件。舉例來說，執行 SpreadsheetApp.getActiveSpreadsheet() 會傳回使用者的現用或目前試算表。取得現用試算表後，您就能對該試算表套用作業。同樣地，Sheet.activate() 方法等方法可以變更現用物件。

啟動工作表、範圍或儲存格，等同於在 Google 試算表介面中點選該工作表、範圍或儲存格，因此游標焦點會移至該位置。

本程式碼研究室的其餘部分會顯示使用這些類別及其方法的指令碼範例。

3. 設定

繼續操作前，請先準備含有資料的試算表。我們已提供一份範本：按一下這個連結即可複製資料表，然後點選「建立副本」。

系統會在您的 Google 雲端硬碟資料夾中放置一份範例試算表副本，並命名為「無標題試算表副本」。請使用這個試算表完成本程式碼研究室的練習。

提醒您，如要從 Google 試算表開啟指令碼編輯器，請依序點選「擴充功能」>「Apps Script」。

首次在指令碼編輯器中開啟 Apps Script 專案時，指令碼編輯器會為您建立指令碼專案和指令碼檔案。

下一節將說明如何使用 Spreadsheet 類別改善這個試算表。

4. 存取及修改試算表

本節將說明如何使用 SpreadsheetApp 和 Spreadsheet 類別存取及修改試算表。具體來說，這些練習會教您如何重新命名試算表，以及如何在試算表中複製工作表。

這些都是簡單的作業，但通常是較大型、較複雜工作流程的一部分。瞭解如何使用指令碼程式碼自動執行這些工作後，您就能更輕鬆地學習如何自動執行更複雜的作業。

重新命名目前開啟的試算表

假設您想將預設名稱「Copy of Untitled spreadsheet」(未命名的試算表副本) 改成更貼切的名稱，您可以使用 SpreadsheetApp 和 Spreadsheet 類別執行這項操作。

1. 在指令碼編輯器中，將預設的 myFunction() 程式碼區塊替換為下列程式碼：

function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}

2. 如要儲存指令碼，請按一下「儲存」圖示 。
3. 如要重新命名 Apps Script 專案，請按一下「無標題專案」，輸入「Avocado prices」做為新專案名稱，然後按一下「重新命名」。
4. 如要執行指令碼，請從函式清單中選取 renameSpreadsheet，然後按一下「執行」。
5. 按照畫面上的指示授權巨集。如果看到「這個應用程式未經驗證」訊息，請按一下「進階」，然後按一下「前往 Avocado prices (不安全)」。在下一個畫面中，按一下「允許」。

注意：如果您使用 gmail.com 帳戶，首次使用指令碼時可能會看到未經驗證的應用程式對話方塊。Google 會使用這項資訊，警告可能使用不明或不受信任作者程式碼的使用者。如果您是指令碼作者，看到這個對話方塊時可以繼續操作。按照畫面上的提示繼續授權指令碼。

函式執行完畢後，試算表的檔案名稱應會變更：

請查看您輸入的程式碼。getActiveSpreadsheet() 方法會傳回代表有效試算表的物件，也就是您製作的練習試算表副本。這個試算表物件會儲存在 mySS 變數中。呼叫 rename(newName) 會將 Google 雲端硬碟中的試算表檔案名稱變更為「2017 年波特蘭和西雅圖的酪梨價格」。mySS

由於 mySS 變數是試算表的參照，因此您可以對 mySS 呼叫 Spreadsheet 方法，而非重複呼叫 getActiveSpreadsheet()，讓程式碼更簡潔有效率。

複製目前的工作表

您目前只有一個工作表。您可以呼叫 Spreadsheet.duplicateActiveSheet() 方法來複製工作表：

1. 在指令碼專案中現有的 renameSpreadsheet() 函式下方，新增下列函式：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}

2. 儲存指令碼專案。
3. 如要執行指令碼，請從函式清單中選取 duplicateAndOrganizeActiveSheet，然後按一下「執行」。

返回 Google 試算表，您會看到試算表新增了「Sheet_Original 的副本」工作表分頁。

在這個新函式中，duplicateActiveSheet() 方法會在試算表中建立、啟用並傳回重複的工作表。產生的工作表會儲存在 duplicateSheet 中，但程式碼尚未對該變數執行任何動作。

在下一節中，您將使用 Sheet 類別重新命名及設定重複工作表的格式。

5. 使用 Sheet 類別設定試算表格式

Sheet 類別提供的方法可供指令碼讀取及更新試算表。在本節中，您將瞭解如何使用 Sheet 類別的方法，變更工作表名稱和欄寬。

注意**：Spreadsheet** 和 Sheet 類別共用多種工作表專屬方法，例如 Spreadsheet.deleteColumn(columnPosition) 和 Sheet.deleteColumn(columnPosition)。

呼叫 Spreadsheet.deleteColumn(columnPosition) 時，這個方法會從試算表目前開啟的工作表中移除資料欄。呼叫 getActiveSheet()，然後呼叫 Sheet.deleteColumn(columnPosition)，即可獲得相同結果。Spreadsheet 版本可節省時間。

變更工作表名稱

重新命名工作表的方式與在 renameSpreadsheet() 中重新命名試算表一樣簡單。只需要單一方法呼叫。

1. 在 Google 試算表中，按一下 Sheet_Original 試算表即可啟用。
2. 在 Apps Script 中，修改 duplicateAndOrganizeActiveSheet() 函式，使其符合下列內容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}

3. 儲存並執行函式。

在 Google 試算表中執行函式時，系統會建立重複的工作表並重新命名：

在新增的程式碼中，setName(name) 方法會使用 getSheetID() 取得工作表的專屬 ID 編號，藉此變更 duplicateSheet 的名稱。+ 運算子會將工作表 ID 串連到 "Sheet_" 字串的結尾。

修改試算表的欄和列

您也可以使用 Sheet 類別設定工作表格式。舉例來說，我們可以更新 duplicateAndOrganizeActiveSheet() 函式，一併調整重複工作表的欄大小，並新增凍結列：

1. 在 Google 試算表中，按一下 Sheet_Original 試算表即可啟用。
2. 在 Apps Script 中，修改 duplicateAndOrganizeActiveSheet() 函式，使其符合下列內容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}

3. 儲存並執行函式。

在 Google 試算表中，系統會建立重複的工作表、重新命名、啟用並設定格式：

您新增的程式碼會使用 autoResizeColumns(startColumn, numColumns) 調整試算表欄的大小，方便閱讀。setFrozenRows(rows) 方法會凍結指定列數 (本例為兩列)，讓讀者向下捲動試算表時，標題列仍會顯示在畫面上。

注意：在 Apps Script 中處理陣列時，請遵循清單從索引 0 開始的慣例。不過，使用列和欄時，索引會從 1 開始。這項邏輯與傳統試算表格式一致，在 Google 試算表中，您會從第 1 列和 A 欄開始。

使用與欄和列相關的方法 (例如 autoResizeColumns(startColumn, numColumns)) 時，請留意這點。在目前的練習中，您會輸入 1 (而非 0) 做為參數，參照 duplicateSheet 的 A 欄。

下一節將介紹範圍和基本資料操作。

6. 使用 Range 類別重新排列資料

Range 類別及其方法提供 Spreadsheet 服務中的大部分資料操作和格式設定選項。

本節將介紹如何使用範圍進行基本資料操作。這些練習的重點在於如何使用 Apps Script 中的範圍，而播放清單中的其他程式碼研究室則會深入探討資料操控和資料格式設定。

移動範圍

您可以使用類別方法和 A1 標記 (用於識別試算表中特定儲存格集的簡寫)，啟用及移動資料範圍。如需複習，請參閱這篇文章，瞭解 A1 標記法。

現在更新 duplicateAndOrganizeActiveSheet() 方法，一併移動部分資料：

1. 在 Google 試算表中，按一下 Sheet_Original 試算表即可啟用。
2. 在 Apps Script 中，修改 duplicateAndOrganizeActiveSheet() 函式，使其符合下列內容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}

3. 儲存並執行函式。

執行這個函式時，系統會建立、啟用及格式化重複的工作表。F 欄的內容會移至 C 欄：

新程式碼會使用 getRange(a1Notation) 方法，找出要移動的資料範圍。輸入 A1 標記「F2:F」做為方法參數，即可指定 F 欄 (不含 F1)。如果指定範圍存在，getRange(a1Notation) 方法會傳回其 Range 執行個體。程式碼會將執行個體儲存在 myRange 變數中，方便使用。

識別範圍後，moveTo(target) 方法會擷取 myRange 的內容 (包括值和格式)，然後移動這些內容。目的地 (C 欄) 是使用 A1 標記「C2」指定。這是單一儲存格，而非資料欄。移動資料時，您不需要將大小與目標和目的地範圍相符。Apps Script 只會對齊每個範圍的第一個儲存格。

注意：撰寫自己的指令碼時，請務必徹底測試，確保資料會傳送到您預期的位置。變更儲存格值時，Apps Script 會覆寫現有的資料值。如果操作不慎，可能會覆寫您想保留的資料。在本例中，C 欄是空白的，但如果其中有資料，moveTo(target) 方法就會移除這些資料。

排序範圍

Range 類別可讓您讀取、更新及整理儲存格群組。舉例來說，您可以使用 Range.sort(sortSpecObj) 方法排序資料範圍：

1. 在 Google 試算表中，按一下 Sheet_Original 試算表即可啟用。
2. 在 Apps Script 中，修改 duplicateAndOrganizeActiveSheet() 函式，使其符合下列內容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}

3. 儲存並執行函式。

現在，除了先前的格式設定外，這項函式還會使用 C 欄中的價格資訊，排序表格中的所有資料：

新程式碼使用 getRange(a1Notation) 指定涵蓋 A3:D55 的新範圍 (整個表格，但不含資料欄標題)。然後，程式碼會呼叫 sort(sortSpecObj) 方法來排序表格。這裡的 sortSpecObj 參數是要排序的欄號。這個方法會排序範圍，使指定資料欄的值由低到高 (遞增值)。sort(sortSpecObj) 方法可以執行更複雜的排序需求，但這裡不需要。如要查看呼叫排序範圍的所有不同方式，請參閱方法參考說明文件。

恭喜，您已成功完成本程式碼研究室的所有練習。下一節將回顧本程式碼研究室的重點，並預覽這個播放清單中的下一個程式碼研究室。

7. 結語

您已完成本程式碼研究室。您現在可以在 Apps Script 中使用及定義 Spreadsheet 服務的基本類別和字詞。

您已準備好進行下一個程式碼研究室。

您覺得這個程式碼研究室實用嗎？

是 否

涵蓋內容

• 試算表、工作表和範圍在 Apps Script 中的表示方式。
• SpreadsheetApp、Spreadsheet、Sheet 和 Range 類別的一些基本用法。

重要概念：

1. 在 Apps Script 中，所有陣列都遵循清單從索引 0 開始的慣例。不過，資料列和資料欄的索引是從 1 開始。
2. 在 Apps Script 中，目前開啟的工作表、醒目顯示的範圍和儲存格都視為有效。許多 Apps Script 方法都會存取或修改現用元素。
3. Spreadsheet 和 Sheet 類別共用多個工作表專屬方法。Spreadsheet 類別中的重複方法僅為方便使用者，因為這些方法通常會在呼叫時以作用中的工作表為目標。
4. 每份試算表和工作表都有專屬 ID，您可以在瀏覽器網址中找到。試算表和工作表類別方法會使用 ID。如要進一步瞭解 ID 和網址，請參閱「Google 試算表簡介」。

重要字詞：

• 有效 (狀態)：表示使用者目前正在查看或醒目顯示指定的試算表、工作表、範圍或儲存格。
• 有效儲存格：有效工作表內以游標為焦點的單一醒目顯示儲存格。
• 有效範圍：目前在有效工作表中醒目顯示的一或多個儲存格群組。
• 授權：使用者授予權限的程序，允許 Apps Script 存取相關 Google 服務中的使用者資料，或代表使用者執行其他動作。
• 繫結至容器的指令碼：繫結至 Google Workspace 文件 (例如 Google 試算表或 Google 文件) 並從中建立的任何指令碼。
• 範圍：** 工作表內一或多個相鄰的儲存格群組。 Range** 類別可讓您讀取及更新工作表中的範圍。
• 指令碼編輯器：Apps Script 的瀏覽器內程式碼編輯器。
• 工作表**：試算表的單一頁面 (類似於 Microsoft Excel 的活頁簿)。有時用來指稱 Google 雲端硬碟中的 Google 試算表檔案。 Sheet** 類別可讓您存取及修改工作表。
• SpreadsheetApp：這個類別是試算表服務的父類別，可做為讀取或操控 Google 試算表資料的程式碼起點。
• 試算表：位於 Google 雲端硬碟中的 Google 試算表檔案。 Spreadsheet** 類別可讓您存取及修改試算表。
• 試算表服務：Apps Script 服務，可讓指令碼建立、存取及修改 Google 試算表檔案。

後續步驟

這個播放清單的下一個程式碼研究室會深入探討如何在試算表中讀取、寫入及修改資料。

請參閱「處理資料」瞭解下一個程式碼研究室。