Google 試算表的 Apps Script 基礎概念 #2：試算表、試算表和範圍

Google 試算表的 Apps Script 基礎概念 #2：試算表、試算表和範圍

程式碼研究室簡介

上次更新時間：4月 4, 2022

作者：Google Workspace Developer Relations

1. 引言

歡迎來到「Google 試算表」的「Apps Script 基礎課程」第二部分，其中有程式碼研究室播放清單。先前的程式碼研究室將重點放在指令碼編輯器、巨集和自訂函式的概念上。本程式碼研究室深入介紹試算表服務，可用來讀取、寫入及操控 Google 試算表中的資料。

課程內容

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

事前準備

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

軟硬體需求

• 上一次在這個播放清單研究室中探討的 Apps Script 基本主題。
• 熟悉 Apps Script 編輯器的基本功能
• Google 試算表的基本概念
• 可讀取試算表 A1 標記法
• 熟悉 JavaScript 及其 String 類別

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

2. 試算表服務簡介

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

SheetApp 類別

深入檢視試算表、工作表和範圍前，請先檢查其上層類別：SpreadsheetApp。許多指令碼一開始會呼叫 SpreadsheetApp 方法，因為這類檔案可以提供 Google 試算表檔案的初始存取權。您可以將 SpreadsheetApp 視為試算表服務的主要類別。「SpreadsheetApp」課程並未深入深入探討。但您可以在本程式碼研究室中，找到範例和練習來協助您瞭解這個課程。

試算表、工作表及其課程

以「試算表」來說，「試算表」是 Google 試算表檔案 (儲存在 Google 雲端硬碟中的資料)，內含以資料列和資料欄彙整的資料。試算表有時也稱為「Google 試算表」及「Google 文件」。

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

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

重點：除了名稱之外，試算表還具有該工作表專屬的 ID 字串。這個編號在試算表的網址中，格式如下：

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

試算表中的每個工作表都有專屬的試算表編號。這組 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 雲端硬碟資料夾中，並命名為「未命名試算表的副本」。請使用這份試算表完成此程式碼研究室的練習。

提醒您，您可以按一下 [擴充功能] > [Apps 指令碼]，開啟 Google 試算表中的指令碼編輯器。

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

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

4. 存取及修改試算表

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

這類作業雖然簡單，但通常也是更複雜的工作流程。透過指令碼程式碼瞭解如何自動化這些工作之後，您將更容易瞭解如何進行更複雜的作業。

重新命名使用中的試算表

假設您想要變更預設名稱，也就是「無標題試算表的副本」；這個名稱最好能夠更準確地反映試算表的用途。方法是使用 SpreadsheetApp 和 Spreadsheet 課程。

1. 在指令碼編輯器中，以預設的程式碼取代預設的 myFunction() 程式碼區塊：

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

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

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

函式執行完畢後，試算表的檔案名稱應如下所示：

讓我們看看您輸入的代碼。getActiveSpreadsheet() 方法會傳回代表有效試算表的物件，也就是您製作的運動試算表副本。這個試算表物件儲存在 mySS 變數中。在 mySS 上呼叫 rename(newName) 即可將 Google 試算表檔案的名稱變更為「2017 Avocado Prices in Seattle, Seattle」。

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

複製使用中的工作表

您目前的試算表只有一個工作表。您可以呼叫 Spreadsheet.duplicateActiveSheet() 方法來建立工作表副本：

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

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

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

返回「試算表」以查看新的「工作表複本：」已新增至試算表。

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

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

5. 使用工作表類別設定工作表格式

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) 方法會變更 duplicateSheet 的名稱，並使用 getSheetID() 取得工作表的唯一識別碼。+ 運算子會串連工作表 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 開始。這個邏輯與傳統的試算表格式設定一致，可以從試算表的第 1 列及 A 欄開始。

使用與資料欄和資料列相關的方法 (例如 autoResizeColumns(startColumn, numColumns)) 時，請注意這一點。在目前的運動中，您輸入 1 (不是 0) 做為參照 duplicateSheet 的參數。

在下一節中，您將會瞭解範圍和基本資料操控。

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

Range 類別及其方法會在試算表服務中提供大部分的資料操控和格式設定選項。

本節將介紹範圍限定的資料基本操作。這些練習的重點在於如何在 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 的試算表服務，定義和定義必要的類別和字詞。

以便進入下一個程式碼研究室。

這個程式碼研究室是否對您有幫助？

是否

報表主題

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

重要概念：

1. 在 Apps Script 中，所有陣列都會遵循清單從索引 0 開始的慣例。但是，資料列和資料欄是從 1 開始建立索引。
2. 在 Apps Script 中，目前開啟的工作表 (醒目顯示範圍) 的儲存格為有效。許多 Apps Script 方法會存取或修改使用中的元素。
3. Spreadsheet 和 Sheet 類別會共用數種工作表專屬方法。Spreadsheet 類別的重複方法只為了協助使用者方便而使用，因為呼叫時通常要指定使用中的工作表。
4. 每個試算表和工作表都有專屬的 ID，您可以在瀏覽器網址中找到這個 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 試算表檔案。

後續步驟

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

如要尋找下一個程式碼研究室，請參閱使用資料。