Google Apps 試算表基本概念 3：使用資料

Google Apps 試算表基本概念 3：使用資料

程式碼研究室簡介

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

作者：Google Workspace Developer Relations

1. 引言

歡迎來到 Apps Script 基礎課程的第三個部分 (含 Google 試算表的程式碼研究室播放清單)。

完成這個程式碼研究室後，您可以學習如何在 Apps Script 中使用資料操縱、自訂選單和公開 API 資料擷取，來改善試算表和使用體驗。您將繼續參考這份播放清單介紹的先前 SpreadsheetApp、Spreadsheet、Sheet 和 Range 課程。

課程內容

• 如何從 Google 雲端硬碟中的個人或試算表匯入資料。
• 如何使用 onOpen() 函式建立自訂選單。
• 如何剖析及操控 Google 試算表儲存格中的字串資料值。
• 如何從公開 API 來源提取及操控 JSON 物件資料。

事前準備

這是「Google Apps 基礎課程：Apps 基礎入門」播放清單中的第三個程式碼研究室。開始進行這個程式碼研究室之前，請務必完成先前的程式碼研究室：

1. 巨集和自訂函式
2. 試算表、試算表和範圍

軟硬體需求

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

2. 設定

這個程式碼研究室的練習需要使用試算表才能運作。請按照下列步驟建立試算表，以便在這些練習中使用：

1. 在 Google 雲端硬碟中建立試算表。在雲端硬碟介面中選取 [新增 Google 試算表] 即可執行這項操作。即可建立及開啟新試算表。系統會將檔案儲存到您的雲端硬碟資料夾。
2. 按一下試算表標題，然後將標題從「無標題試算表」改成「資料操弄和自訂選單」。您的工作表應如下所示：

3. 如要開啟指令碼編輯器，請按一下 [擴充功能] > [Apps 指令碼]。
4. 按一下 Apps Script 專案標題，然後將標題從「無標題專案」變更為「資料操弄和自訂選單」。按一下 [重新命名] 即可儲存標題變更。

只要使用空白的試算表和專案，即可開始進行這項研究室作業。前往下一節開始學習自訂選單。

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

1. 在「未經驗證的應用程式」對話方塊中，按一下 [進階]。
2. 按一下 [前往資料控管和自訂選單 (不安全)]。
3. 按一下 [Allow]。

在這個程式碼研究室中，您可能會收到數個權限提示。請參閱《Google 服務授權》，進一步瞭解這項程序。

3. 總覽：使用自訂選單項目匯入資料

Apps Script 可讓您定義 Google 試算表中顯示的自訂選單。您也可以使用 Google 文件、Google 簡報和 Google 表單的自訂選單。定義自訂選單項目時，您可以建立文字標籤，並將其連接至指令碼專案中的 Apps Script 函式。接著您就可以將選單新增至 UI，讓該選單顯示在 Google 試算表中：

當使用者點選自訂選單項目時，系統即會執行相關聯的相關聯 Apps Script 函式。如此一來，無需開啟指令碼編輯器就能快速執行 Apps Script 函式。試算表的其他人員也可執行您的程式碼，完全不需要瞭解其運作方式或 Apps Script 的運作方式。他們只會看到另一個選單項目。

自訂選單項目是由 onOpen() 簡易觸發條件功能所定義，我們會在下一節中說明。

4. onOpen() 函式

簡易指令碼可以針對特定情況或事件執行特定的 Apps Script 程式碼。建立觸發條件時，您定義了哪些觸發條件會觸發觸發條件，並提供為該事件執行的 Apps Script 函式。

onOpen() 是簡單的觸發條件範例。設定簡單明瞭 — 只需編寫名為 onOpen() 的 Apps Script 函式，且 Apps Script 會在每次開啟或重新載入相關試算表時執行該函式：

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

重要概念：簡易的觸發條件易於設定及使用，但有幾項限制。基於上述限制，onOpen() 只會用來建立菜單品項。

觸發條件是複雜的主題，因此不在本程式碼研究室的範圍內。目前您可以把 onOpen() 當成事件處理常式，它會在您每次載入試算表時新增選單項目。

導入作業

讓我們建立自訂選單。

1. 將指令碼專案內的程式碼替換為以下內容：

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}

2. 儲存指令碼專案。

程式碼審查

不妨閱讀這段程式碼，瞭解程式碼的運作方式。在 onOpen() 中，第一行使用 getUi() 方法取得 Ui 物件，表示這個指令碼所連結的有效試算表的使用者介面。

接下來的三行會建立選單 (Book-list)，將選單項目 (Load Book-list) 新增至該選單，然後將選單新增至試算表的介面。分別使用 createMenu(caption)、addItem(caption, functionName) 和 addToUi() 方法來完成上述操作。

addItem(caption, functionName) 方法會在選單項目標籤和選取選單項目時執行的 Apps Script 函式之間建立連線。在此情況下，如果選取 [Load Book-list] 選單項目，試算表就會嘗試執行 loadBookList() 函式 (目前不存在)。

結果

立即執行此函式，看看它可以正常運作：

1. 在 Google 試算表中重新載入試算表。注意：通常會關閉指令碼編輯器的分頁。
2. 選取 [工具] > [指令碼編輯器]，重新開啟指令碼編輯器。

試算表重新載入後，新的 Book-list 選單應會顯示在選單列中：

按一下 [Book-list]，即可查看產生的選單：

下一節將建立 loadBookList() 函式的程式碼，並介紹一種與 Apps Script 資料互動的方法：閱讀其他試算表。

5. 匯入試算表資料

現在您已經建立自訂選單，您可以建立可點選選單項目執行的函式。

自訂選單「Book-list」目前有一個選單項目：Load Book-list.，當您選取 Load Book-list 選單項目時，系統會呼叫 loadBookList(), 這個指令碼，因此 loadBookList(), 中沒有指令碼，因此選取 [Book-list > 正在載入 Book-list] 會擲回錯誤：

您可以導入 loadBookList() 函式來修正錯誤。

導入作業

你希望新的選單項目能填入試算表中的資料，因此你只需實作 loadBookList()，即可讀取其他試算表中的書籍資料並複製到以下試算表：

1. 將下列程式碼加進 onOpen() 的指令碼：

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}

2. 儲存指令碼專案。

程式碼審查

這項功能如何運作？loadBookList() 函式主要使用上述程式碼研究室推出的 Spreadsheet、Sheet 和 Range 類別方法。瞭解這些概念之後，您可以將 loadBookList() 程式碼細分為下列四個部分：

1：識別目的地資料表

第一行使用 SpreadsheetApp.getActiveSheet() 取得目前工作表物件的參照，並儲存在 sheet 變數中。這是您要複製資料的工作表。

2：識別來源資料

接下來的幾行行會建立四個變數來參照您要擷取的來源資料：

• bookSS 會儲存試算表讀取資料的試算表參照。這段程式碼會根據試算表的試算表 ID 找出試算表。在本範例中，我們提供了來源試算表的 ID 可供讀取，並透過 SpreadsheetApp.openById(id) 方法開啟試算表。
• bookSheet 會在 bookSS 中儲存一份包含所需資料的工作表。這段程式碼可識別 codelab-book-list 要讀取的工作表名稱。
• bookRange 會在 bookSheet 中儲存特定資料範圍的參照。Sheet.getDataRange() 方法會傳回工作表中所有包含非空白儲存格的範圍。這種簡單的方式是確保您能取得一個範圍的資料範圍，藉此涵蓋工作表中的所有資料，但不包括空白的列和欄。
• bookListValues 是一個 2D 陣列，其中包含從 bookRange 儲存格擷取的所有值。Range.getValues() 方法會從來源工作表讀取資料來產生這個陣列。

3：將資料從來源複製到目的地

下一個程式碼區段會將 bookListValues 資料複製到 sheet，然後重新命名工作表：

• Sheet.getRange(row, column, numRows, numColumns) 可用來識別 sheet 中複製資料的位置。
• Range.getHeight() 和 Range.getWidth() 方法是用來評估資料大小，並定義相同維度的目的地範圍。
• Range.setValues(values) 會將 bookListValues 的 2D 陣列複製到目標範圍，並覆寫任何現有資料。

4：設定目標工作表的格式

Sheet.setName(name) 可用來將目標工作表名稱變更為 Book-list。函式中的最後一行會使用 Sheet.autoResizeColumns(startColumn, numColumns) 調整目標工作表的前三欄大小，方便您查看新資料。

結果

您可以看到這個函式的實際運作情形。在 Google 試算表中，選取 [書籍清單 >載入書籍清單] 來執行函式，以填入試算表：

您的工作表現在會列出書名、作者和 13 位數的 ISBN 號碼。在下一節中，您將瞭解如何使用字串操控和自訂選單來修改及更新這本書清單中的資料。

6. 總覽：清除試算表資料

現在您的工作表中已有書籍資訊。每列分別列出特定書籍，將書名、作者和 ISBN 號碼分別列在不同資料欄中。不過，這個原始資料也可能出現一些問題：

1. 在某些資料列中，標題和作者要放在標題欄內，並以半形逗號或字串「&;」連結，
2. 部分資料列缺少書名或作者。

在接下來的部分中，您必須清除資料以解決這些問題。對於第一個問題，您會建立一個函式來讀取標題欄，並將文字各以逗號或「,」分隔文字。以「&」符號分隔，將對應的作者和標題子字串放在正確的欄中。對於第二個問題，您會編寫程式碼，使用外部 API 自動搜尋遺漏的書籍資訊，然後將這些資訊加入工作表。

7. 新增菜單品項

您將需要建立三個選單項目，以便控制要實作的資料清除作業。

導入作業

請將「onOpen()」更新為需要的額外選單項目。請執行下列步驟：

1. 在指令碼專案中，將 onOpen() 程式碼更新為與下列項目相符：

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}

2. 儲存指令碼專案。
3. 在指令碼編輯器中，從函式清單中選取 onOpen，然後按一下 [執行]。這會執行 onOpen() 以重建試算表選單，因此您不必重新載入試算表。

在這個新的程式碼中，Menu.addSeparator() 會在選單中建立一個水平分隔線，以組織相關的選單項目群組。接著，新選單下方會隨即顯示，並加上「Separate title/author at first comma」、「Separate title/author at last "by"」和「Fill in blank titles and author cells」標籤。

注意：選單項目會以 Google 試算表的順序顯示，就像您在 onOpen() 程式碼中一樣。

結果

在試算表中按一下「Book-list」選單，即可查看新的選單項目：

由於點擊這些新項目會導致無法執行相應的函式，因此發生錯誤，因此接下來要執行操作。

8. 以半形逗號分隔分隔符號文字

您匯入試算表的資料集含有幾個儲存格，其中作者和標題不正確在一個儲存格中誤用：

將文字字串分割成不同資料欄是常見的試算表工作。Google 試算表提供的 SPLIT() 函式可將字串分割成不同欄，但資料集常常會發生問題，例如試算表並無法輕易解決。在這些情況下，您可以撰寫 Apps Script 程式碼，執行複雜的資料整理和整理作業。

請先實作名為「splitAtFirstComma()」的函式來清理資料，只要找到逗號，作者和標題就會分隔成對應的儲存格。

splitAtFirstComma() 函式應執行下列步驟：

1. 取得目前所選儲存格的範圍。
2. 確認範圍內的儲存格是否有逗號。
3. 找到逗號時，將字串分割為第一個逗號位置中的兩個 (且只含兩個) 子字串。為了簡化一切，您可以假設任何逗號代表「<作者>, <名稱>」字串模式。您也可以假設儲存格包含多個逗號，表示適合在字串內使用第一個逗號分割。
4. 將子字串設為個別標題和作者儲存格的新內容。

導入作業

如要執行這些步驟，您和先前使用的試算表服務方法相同，但也需要使用 JavaScript 來操控字串資料。步驟如下：

1. 在 Apps Script 編輯器中，將以下函式新增至指令碼專案結尾：

/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. 儲存指令碼專案。

程式碼審查

我們先來看看包含以下三個主要部分的新程式碼：

1：擷取醒目顯示的標題值

前三行分別建立三個變數，用來參照試算表中目前的資料：

• activeRange 代表使用者呼叫 splitAtFirstComma() 函式時，目前標明的範圍。為了簡化這個練習，我們只會在使用者將 A 欄的儲存格醒目顯示時，才會執行這項操作。
• titleAuthorRange 代表了與 activeRange 相同儲存格的新範圍，但是右側還有一欄。titleAuthorRange 是使用 Range.offset(rowOffset, columnOffset, numRows, numColumns) 方法建立。程式碼需要這個展開的範圍，因為需要一個地方才能把它找到的所有作者加到標題欄中。
• titleAuthorValues 是使用 Range.getValues() 從 titleAuthorRange 擷取的 2D 資料陣列。

2：檢查每個標題，並以第一個分隔符號分隔

下一節會檢查 titleAuthorValues 中的值以找出逗號。JavaScript 迴圈是用於檢查 titleAuthorValues 第一欄中的所有值。使用 JavaScript String indexOf() 方法找到逗號的子字串 (", ") 時，程式碼會執行下列作業：

1. 儲存格字串值會複製到 titlesAndAuthors 變數中。
2. 逗號位置是使用 JavaScript String indexOf() 方法來決定。
3. 系統會呼叫兩次 JavaScript String() 方法，取得逗號分隔符號之前的子字串，以及分隔符號之後的子字串。
4. 子字串會複製回 titleAuthorValues 2D 陣列中，並覆寫該位置的現有值。由於我們假設假設「<作者>, <標題>,」 模式，系統會對兩個子字串的順序反轉，並將該欄置於第一欄，而作者則是在第二欄。

注意：當程式碼找不到逗號時，資料列中的資料會維持不變。

3：將新值複製回工作表

檢查完所有標題儲存格值後，使用 Range.setValues(values) 方法將更新的 titleAuthorValues 2D 陣列複製到試算表。

重點提示：這個程式碼可能會逐一更新每個試算表儲存格，每當有需要變更儲存格時，程式碼都會逐一呼叫 Range.setValue(value)。不過，這不僅會使程式碼的執行速度變慢，因為每次 Range.setValue(value) 呼叫都需要進行伺服器通訊。

更新試算表資料時，最好先將值擷取到 2D 陣列中，接著更新陣列，然後透過單一 Range.setValues(values) 呼叫將資料推送回工作表中。

結果

您現在可以查看 splitAtFirstComma() 函式的實際運作情形。選取 [以逗號分隔的第一個標題/作者] 選單項目後，選取 [獨立標題/作者分隔] 選單執行...

...一個儲存格：

...或是多個儲存格：

注意：您可以在試算表中選取任何資料欄。使用此功能即可尋找逗號，並按照您設定的規則分割文字。不過，如果程式碼找到逗號，則會將儲存格覆寫至原始儲存格的右側。

您已建立 Apps Script 函式來處理試算表資料。接下來，您將執行第二個分割函式。

9. 以「分隔符號」分隔文字

從原始資料中看見另一個問題。如同資料儲存格中的標題和作者一樣，有些儲存格的格式是作者和作者，例如 [quos]、[title]"，其他儲存格的格式則是由作者和標題設定成 [quos;[title] by [authors]"]：

導入作業

如要解決這個問題，您可以使用上一節的相同技巧建立 splitAtLastBy() 函式。此函式的運作方式與 splitAtFirstComma() 類似，唯一的差別在於其所搜尋的文字模式略有不同。實作這個函式的方式如下：

1. 在 Apps Script 編輯器中，將以下函式新增至指令碼專案結尾：

/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. 儲存指令碼專案。

程式碼審查

這段程式碼與 splitAtFirstComma() 有幾個主要差異：

1. 子字串「by」是字串分隔符號，而不是「,」。
2. 這裡使用的是 JavaScript String.lastIndexOf(substring) 方法，而非 String.indexOf(substring)。這表示如果第一個字串中有多個「by」的子字串，除了最後一個「by」以外，其餘所有子字串都會假設為標題的一部分。
3. 分割字串後，第一個子字串會設為標題，第二個是作者 (與 splitAtFirstComma() 相反)。

結果

您現在可以查看 splitAtLastBy() 函式的實際運作情形。選取 [在最後的獨立標題/作者分隔] 選單項目選取後執行：

...一個儲存格：

...或是多個儲存格：

您已完成程式碼研究室的這個部分。您現在可以使用 Apps Script 讀取及修改工作表中的字串資料，並使用自訂選單執行不同的 Apps Script 指令。

在下一節中，您將學習如何在公開儲存格中填入從公開 API 擷取的資料，進一步改善這個資料集。

10. 總覽：從公開 API 取得資料

到目前為止，您已修正資料集並修正部分標題和作者格式問題，但資料集仍缺少部分資訊 (如下面的儲存格所示)：

您可以針對現有的資料使用字串運算，藉此取得遺失的資料。您必須改為從其他來源取得遺失的資料。在 Apps Script 中，您可以透過要求提供額外資料的外部 API 來提供這項資訊。

API 是應用程式設計介面。這只是一般的詞彙，但基本上，您的程式和指令碼可以呼叫服務要求資訊或執行特定動作。在本節中，您要呼叫一個公開的 API 來要求書籍資訊，以便插入工作表中的空白儲存格。

本節將說明如何：

• 向外部 API 來源要求書籍資料。
• 從傳回的資料中擷取標題和作者資訊，並寫入試算表。

11. 使用 UrlFetch 擷取外部資料

在您實際使用與試算表直接搭配使用的程式碼之前，不妨先瞭解如何針對公開的程式庫 API 要求書籍資訊，以便在 Apps Script 中使用外部 API。

注意：開放式程式庫是網際網路封存檔，旨在為每本書籍發布作業建立專頁。如果您有興趣，歡迎造訪他們的官方網站，進一步瞭解他們的 API 說明文件。

我們的輔助函式 fetchBookData_(ISBN) 會將書籍的 13 碼 ISBN 參數當做參數，傳回書籍的相關資料。這項功能會連線至及擷取來自 Open Library API 的資訊，然後剖析傳回的 JSON 物件。

導入作業

執行此輔助程式函式的方式如下：

1. 在 Apps Script 編輯器中，將下列程式碼加到指令碼的結尾：

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}

2. 儲存指令碼專案。

注意：在 Apps Script 中，結尾為 _ 的函式名稱 (底線) 會被視為「私人」。其他指令碼不得出現在資料庫中，或是在用戶端伺服器通訊期間呼叫這些函式。這些是進階主題，所以如果您知道某個函式只會由目前的指令碼使用，那麼最佳做法就是使用 _ 結束函式的名稱。

程式碼審查

這段程式碼分為兩大部分：

1：API 要求

在前兩行中，fetchBookData_(ISBN) 會使用 API 的網址端點和 Apps Script's 網址擷取服務連線至公開的 Open Library API。

url 變數只是網址字串，例如網址。指向開放程式庫伺服器上的位置。其中也包含三個參數 (bibkeys、jscmd 和 format)，可讓 Open Library 伺服器知道您要求的資訊，以及如何建構回應結構。在這種情況下，您必須提供書籍的 ISBN 號碼，並要求以 JSON 格式傳回詳細資訊。

注意：每個公用 API 會定義自己在要求和傳回結果時採用的格式。如要讓程式碼與 API 有效率地進行通訊，請務必遵循其定義的格式。如需開放式程式庫的請求和回應格式，請參閱 Open Library Books API 說明文件。回應格式是 JSON (JavaScript Object Notation)，它是整理資料的特殊方式。JSON 與 HTML 或 XML 類似，但一般使用者比較容易閱讀。

建立網址字串後，程式碼就會將要求傳送至該位置並收到回應。您可以使用 UrlFetchApp.fetch(url, params) 方法來執行這項作業。系統會將資訊要求傳送到您提供的外部網址，並將產生的回應儲存在 response 變數中。除了網址之外，程式碼會將選用參數 muteHttpExceptions 設為 true。這項設定表示，如果要求導致 API 錯誤，您的程式碼將會停止。而是傳回錯誤回應。

此要求會傳回儲存在 response 變數中的 HTTPResponse 物件。HTTP 回應包含回應代碼、HTTP 標頭和主要回應內容。這裡的資訊是主要的 JSON 內容，因此程式碼必須擷取該內容，然後剖析 JSON，找出並傳回想要的資訊。

2：剖析 API 回應並傳回感興趣的資訊

在最後三行程式碼中，HTTPResponse.getContentText() 方法會以字串的形式傳回回應的主要內容。此字串採用 JSON 格式，但 Open Library API 會定義確切的內容與格式。JSON.parse(jsonString) 方法會將 JSON 字串轉換為 JavaScript 物件，以便輕鬆擷取資料的不同部分。最後，函式會傳回與書籍 ISBN 號碼相對應的資料。

注意：如果您想熟悉 JSON，請參閱 JavaScript 說明文件：使用 JSON 或 JSON 類別參考資料。

結果

您已經實作了 fetchBookData_(ISBN)。您可以在程式碼中透過其他功能尋找 ISBN 號碼的相關資訊。使用此功能有助於填補試算表中的儲存格。

12. 將 API 資料寫入試算表

您現在可以實作 fillInTheBlanks() 函式來執行下列作業：

1. 識別有效資料範圍內的遺失標題和作者資料。
2. 使用 fetchBookData_(ISBN) 輔助程式方法呼叫 Open Library API，以擷取特定書籍缺少的資料。
3. 更新個別儲存格中遺失的標題或作者值。

導入作業

執行這項新功能的方式如下：

1. 在 Apps Script 編輯器中，將下列程式碼加進指令碼專案結尾：

/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

2. 儲存指令碼專案。

程式碼審查

這段程式碼分為三個部分：

1：閱讀現有書籍資訊

函式的前三行定義一個常數，讓程式碼更容易閱讀。在接下來的兩行中，bookValues 變數是用來保存工作表的書籍資訊副本。這個程式碼會讀取 bookValues 中的資訊、使用 API 填入遺漏的資訊，然後將這些值寫入試算表。

注意：本安排與 splitAtFirstComma() 和 splitAtLastBy() 不同。在這些函式中，程式碼只檢查目前醒目顯示的標題和其列。在這裡，您將使用 Spreadsheet.getDataRange() 檢查整個工作表。

2：使用輔助函式擷取遺漏的資訊

這個程式碼會在 bookValues 的每一列中循環顯示，以找出遺失的書名或作者。為了減少 API 呼叫的數量並提升效率，程式碼只會在下列情況相符時呼叫 API：

1. 資料列的 ISBN 欄含有值。
2. 這一列的標題或作者儲存格空白。

如果條件符合，程式碼就會使用您先前實作的 fetchBookData_(isbn) 輔助函式呼叫 API，並將結果儲存在 bookData 變數中。現在，您應該要在工作表中插入遺漏的資訊。

目前只須在試算表中加入 bookData 資訊即可。但是有點需要注意。很抱歉，Open Library Book API 等公開 API 有時並未取得您要求的資訊，有時可能會發生其他問題，導致系統無法提供這些資訊。假如您的 API 要求成功，程式碼就會不夠完善，無法處理非預期的錯誤。

為了確保程式碼能夠處理 API 錯誤，程式碼必須先確認 API 回應有效，再嘗試使用。一旦代碼具有 bookData，它在嘗試讀取它之前就執行一個簡單的檢查以驗證您的 bookData 和 bookData.details。如果缺少上述兩者，表示 API 沒有您需要的資料。在這種情況下，continue 指令會告知程式碼略過該列，您可以填補遺漏的儲存格，但至少讓您的指令碼不會當機。

3：將更新後的資訊寫回工作表

程式碼的最後一個部分含有類似的檢查，以確認 API 傳回的標題和作者資訊。只有在原始標題或作者儲存格為空白，且 API 傳回您可放置的值時，程式碼才會更新 bookValues 陣列。

檢查工作表中的所有資料列後，該迴圈會隨即結束。最後一步是使用 Range.setValues(values) 將現在更新的 bookValues 陣列寫入試算表。

結果

現在您可以完成書籍的清理作業。請執行下列步驟：

1. 如果尚未標示，請在工作表中醒目顯示 A2:A15 範圍，然後選取 [書籍清單 > [第一個書名]/[在第一個逗號分隔]，以清除逗號相關問題。
2. 如果尚未標示，請在工作表中醒目顯示 A2:A15 範圍，然後選取 [書籍清單 > [在最後一頁分隔的書名]] 來清除「依」排序的問題。
3. 如要填入剩餘的儲存格，請選取 [Book-list > 填入空白標題和作者儲存格]：

13. 結語

恭喜您完成這個程式碼研究室。您已瞭解如何建立自訂選單來啟用 Apps Script 程式碼的不同部分。此外，您也已經瞭解如何使用 Apps Script 服務和公開 API 將資料匯入 Google 試算表。這是試算表處理作業的常見作業，Apps Script 可讓您從各種來源匯入資料。最後，您已瞭解如何使用 Apps Script 服務和 JavaScript 讀取、處理及插入試算表資料。

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

是否

您學到的內容

• 如何從 Google 試算表匯入資料。
• 如何在 onOpen() 函式中建立自訂選單。
• 如何剖析及處理字串資料值。
• 如何使用網址擷取服務呼叫公開 API。
• 如何剖析從公開 API 來源擷取的 JSON 物件資料。

重要概念：

1. 「onOpen()」是一個簡易觸發條件，可用來建立選單項目。簡單的觸發條件必須遵守各種限制。
2. 讀取或寫入試算表儲存格資料時，建議您透過 Range.getValues() 或 Range.setValues(values) 一次讀取或寫入整個範圍。請勿個別讀取或設定每個儲存格，因為儲存格的速度較慢。
3. 以 _ 結尾的 Apps Script 函式名稱 (底線) 會被視為私人。其他指令碼不得出現在資料庫中，或是在用戶端透過伺服器與用戶端通訊呼叫這些函式。如果您發現函式只會由目前的指令碼使用，那麼最佳做法就是使用 _ 結束函式的名稱。
4. 使用外部 API 傳回的資料前，請先驗證資料再嘗試使用。如果 API 傳回非預期的結果，就可以減少程式碼中的錯誤。

重要詞彙：

• 有效 (狀態)：表示試算表、工作表、範圍或儲存格是試算表使用者目前查看或標明的項目。
• 使用中的儲存格：在工作表中，以滑鼠遊標醒目顯示單一焦點的儲存格。
• 有效範圍：一或多個有效儲存格在當前工作表中醒目顯示的位置。
• API：應用程式設計介面服務，可讓程式或指令碼呼叫以呼叫資訊或執行特定動作。
• 授權：使用者授權讓 Apps Script 存取相關 Google 服務中的資料，或代表使用者採取行動。
• 容器容器指令碼：所有透過 Google Workspace 文件 (例如 Google 試算表或 Google 文件) 建立繫結的指令碼。
• 輔助函式：通常為私人函式的函式，也就是另一個呼叫的函式，用來完成小型的子工作。需要在不同位置完成子工作時，使用輔助函式。
• JSON：用來定義資料物件及其文字特性的檔案格式。
• onOpen()：如果透過指令碼定義，這會在簡單的檔案開啟或重新載入時觸發這個簡易觸發條件。
• 私人函式：函式庫中的函式，無法由伺服器用戶端通訊呼叫該函式。Apps Script 私人函式的名稱結尾為 _。
• 範圍**：試算表中一或多個相鄰的儲存格群組。Range** 類別可讓您讀取及更新工作表內的範圍。
• 指令碼編輯器：用於瀏覽器內的 Apps Script 程式碼編輯器。
• 簡易觸發條件：Apps Script 中的觸發條件的子類型，包括保留的觸發條件函式名稱和特定限制。
• 工作表**：試算表的單頁。有時也會用來參照 Google 雲端硬碟中的 Google 試算表檔案。Sheet** 類別可讓您存取及修改工作表。
• SpreadsheetApp：這個類別會成為試算表服務的父項類別，並提供讀取或操控 Google 試算表資料的程式碼。
• 試算表**：存放在 Google 雲端硬碟中的 Google 試算表檔案。Spreadsheet** 類別可讓您存取及修改試算表。
• 試算表服務：這項 Apps Script 服務可讓指令碼建立、存取及修改 Google 試算表檔案。
• 觸發條件：觸發 Apps Script 函式的事件。事件 (例如開啟試算表) 發生時，觸發條件會觸發「fires」，並自動執行相關的 Apps Script 函式。
• 網址擷取服務：Apps Script 服務可讓指令碼連線至網址端點，以便傳送要求及接收回應。

後續步驟

這份播放清單中的下一個程式碼研究室會深入探討如何設定試算表中的資料格式。

在 Data format 中尋找下一個程式碼研究室。