Google 試算表適用的 Apps Script 基礎知識 #3：處理資料

1. 簡介

歡迎來到「Google 試算表適用的 Apps Script 基礎知識」程式碼研究室播放清單的第三部分。

完成本程式碼研究室後，您將瞭解如何使用資料操控、自訂選單和 Apps Script 中的公開 API 資料擷取功能，提升 Google 試算表的體驗。您將繼續使用這個播放清單中先前程式碼研究室介紹的 SpreadsheetApp、Spreadsheet、Sheet 和 Range 類別。

課程內容

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

事前準備

這是「Google 試算表 Apps Script 基礎知識」播放清單中的第三個程式碼研究室。開始本程式碼研究室前，請務必先完成下列程式碼研究室：

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

軟硬體需求

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

2. 設定

本程式碼研究室的練習需要使用試算表。請按照下列步驟建立試算表，以用於這些練習：

1. 在 Google 雲端硬碟中建立試算表。如要建立試算表，請前往 Google 雲端硬碟介面，然後依序選取「新增」>「Google 試算表」。即可建立並開啟新的試算表。檔案會儲存到雲端硬碟資料夾。
2. 按一下試算表標題，將「無標題試算表」變更為「資料操作和自訂選單」。試算表應如下所示：

3. 如要開啟指令碼編輯器，請依序點選「擴充功能」>「Apps Script」。
4. 按一下 Apps Script 專案標題，將「Untitled Project」變更為「Data Manipulation and Custom Menus」。按一下「重新命名」即可儲存標題變更。

有了空白試算表和專案，您就可以開始實驗室課程。請前往下一節，開始瞭解自訂選單。

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

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

在本程式碼研究室中，您可能會收到多個權限提示。如要進一步瞭解這項程序，請參閱「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 程式碼。建立觸發條件時，請定義觸發條件的觸發事件，並提供要為該事件執行的 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 選單項目會導致 Google 試算表嘗試執行 loadBookList() 函式 (該函式尚不存在)。

結果

現在執行這個函式，確認函式是否正常運作：

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

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

點選「Book-list」，即可查看結果選單：

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

5. 匯入試算表資料

自訂選單建立完成後，您就可以建立函式，只要點選選單項目即可執行。

目前，自訂選單 Book-list 只有一個選單項目：Load Book-list. 選取 Load Book-list 選單項目時呼叫的函式 loadBookList(), 不存在於指令碼中，因此選取「Book-list」>「Load 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 試算表中，選取「Book-list」>「Load book-list」，執行函式來填入試算表：

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

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

現在工作表上會顯示書籍資訊。每一列代表一本書，並在不同欄位中列出書名、作者和 ISBN 號碼。不過，您也可以看出這項原始資料的幾個問題：

1. 在某些列中，書名和作者會一起放在書名欄中，並以半形逗號或「by」字串連結。
2. 部分資料列缺少書籍的書名或作者。

在接下來的章節中，您將透過清理資料來修正這些問題。針對第一個問題，您將建立函式，讀取標題欄並在發現逗號或「by」分隔符時分割文字，然後將對應的作者和標題子字串放在正確的欄中。針對第二個問題，您將編寫程式碼，使用外部 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() 函式，可將字串分割成多欄。不過，資料集經常會出現問題，無法輕易透過 Google 試算表的內建函式解決。在這種情況下，您可以編寫 Apps Script 程式碼，執行清理及整理資料所需的複雜作業。

首先，請實作名為 splitAtFirstComma() 的函式，在發現逗號時將作者和標題分別劃分到各自的儲存格，藉此開始清理資料。

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

1. 取得代表目前所選儲存格的範圍。
2. 檢查範圍內的儲存格是否含有半形逗號。
3. 如果找到半形逗號，請在第一個半形逗號的位置將字串分割成兩個 (且僅兩個) 子字串。為簡化作業，您可以假設任何半形逗號都表示「[作者]、[書名]」字串模式。如果儲存格中出現多個半形逗號，您也可以假設以字串中的第一個半形逗號做為分隔依據。
4. 將子字串設為相應標題和作者儲存格的新內容。

導入作業

如要實作這些步驟，您會使用先前用過的 Spreadsheet 服務方法，但您也需要使用 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) 方法建立。程式碼需要這個擴展範圍，因為需要空間放置在「Title」欄中找到的任何作者。
• titleAuthorValues 是從 titleAuthorRange 擷取的 2D 資料陣列，使用 Range.getValues()。

2：檢查每個標題，並以找到的第一個半形逗號做為分隔符號

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

1. 儲存格字串值會複製到 titlesAndAuthors 變數。
2. 逗號位置是使用 JavaScript String indexOf() 方法判斷。
3. JavaScript 字串 slice() 方法會呼叫兩次，分別取得逗號分隔符號之前的子字串，以及分隔符號之後的子字串。
4. 子字串會複製回 titleAuthorValues 2D 陣列，覆寫該位置的現有值。由於我們假設採用「[authors], [title]」模式，因此兩個子字串的順序會反轉，將標題放在第一欄，作者放在第二欄。

注意：如果程式碼找不到半形逗號，系統就不會變更資料列中的資料。

3：將新值複製回工作表

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

重點：程式碼可能會在找到每個半形逗號時，個別更新每個試算表儲存格，並在每次需要變更儲存格時呼叫 Range.setValue(value)。不過，這樣會導致程式碼執行速度變慢，因為每次呼叫 Range.setValue(value) 都需要與伺服器通訊。

更新 Google 試算表資料時，最好先將值放入 2D 陣列，更新陣列，然後透過單一 Range.setValues(values) 呼叫將資料推回試算表。

結果

您現在可以看到 splitAtFirstComma() 函式的實際效果。選取「Separate title/author at first comma」選單項目，然後選取...

...一個儲存格：

...或多個儲存格：

注意：你可以選取試算表中的任何文字欄。使用這項函式找出半形逗號，並根據您設定的規則分割文字。不過，如果程式碼找到逗號分隔符，就會覆寫原始儲存格右側的儲存格。

您現在已建構可處理 Google 試算表資料的 Apps Script 函式。接著，您將實作第二個分割器函式。

9. 根據「by」分隔符號分割文字

查看原始資料時，您會發現另一個問題。如同某些資料格式會將標題和作者放在單一儲存格中，並以「[作者], [標題]」格式呈現，其他儲存格則會以「[作者] 的 [標題]」格式呈現作者和標題：

導入作業

您可以使用上一節的相同技巧解決這個問題，也就是建立名為 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() 函式的實際效果。選取...後，選取「Separate title/author at last "by"」(最後以「by」分隔標題/作者) 選單項目，嘗試執行這項操作。

...一個儲存格：

...或多個儲存格：

您已完成本程式碼研究室的這一節。您現在可以使用 Apps Script 讀取及修改試算表中的字串資料，並透過自訂選單執行不同的 Apps Script 指令。

下一節將說明如何從公開 API 擷取資料，填入空白儲存格，進一步改善這個資料集。

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

到目前為止，您已修正部分書名和作者格式問題，但資料集仍缺少一些資訊，如下方儲存格中醒目顯示的內容：

您無法對現有資料執行字串作業，以取得缺少的資料。您必須從其他來源取得缺少的資料。您可以在 Apps Script 中向外部 API 要求資訊，藉此取得額外資料。

API 是應用程式設計介面。這是個通用術語，但基本上是指程式和指令碼可呼叫的服務，用來要求資訊或採取特定動作。在本節中，您將呼叫公開 API，要求提供書籍資訊，並插入試算表中的空白儲存格。

本節將說明如何：

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

11. 使用 UrlFetch 擷取外部資料

在深入瞭解如何直接使用試算表的程式碼之前，您可以先建立輔助函式，專門用於從公開的 Open Library API 要求書籍資訊，藉此瞭解如何在 Apps Script 中使用外部 API。

注意： Open Library 是一個網路檔案庫，致力於為每本出版書籍建立專屬頁面。如要瞭解詳情，請前往官方網站參閱 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 的 網址擷取服務，連線至公開的 Open Library API。

url 變數只是網址字串，例如網址。這會指向 Open Library 伺服器上的位置。其中也包含三個參數 (bibkeys、jscmd 和 format)，可告知 Open Library 伺服器您要求哪些資訊，以及如何建構回應。在本例中，您提供書籍的 ISBN 編號，並要求以 JSON 格式傳回詳細資訊。

注意：每個公開 API 都會定義專屬格式，用於提出要求和傳回結果。如要讓程式碼與 API 有效通訊，您必須遵守 API 定義的格式。如為 Open Library，要求和回應格式請參閱 Open Library Books API 說明文件。我們使用的回應格式為 JSON (JavaScript 物件標記法)，這只是一種特定的資料整理方式。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，請參閱 JSON 相關 JavaScript 文件或 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. 該列的標題或作者儲存格為空白。

如果條件為 true，程式碼會使用您先前實作的 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 範圍，然後選取「Book-list」>「Separate title/author at first comma」(在第一個半形逗號處分隔書名/作者)，解決半形逗號問題。
2. 如果尚未這麼做，請醒目顯示工作表中的 A2:A15 範圍，然後選取「Book-list」>「Separate title/author at last "by"」(書單 > 在最後一個「by」處分隔書名/作者)，清除「by」問題。
3. 如要填入所有剩餘儲存格，請依序選取「書單」>「填入空白的書名和作者儲存格」：

13. 結語

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

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

是 否

您學到的內容

• 如何從 Google 試算表匯入資料。
• 如何在 onOpen() 函式中建立自訂選單。
• 如何剖析及操控字串資料值。
• 如何使用 URL Fetch 服務呼叫公開 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 函式相關聯的事件。當事件 (例如開啟試算表) 發生時，觸發條件會「觸發」，並自動執行相關聯的 Apps Script 函式。
• 網址擷取服務：這項 Apps Script 服務可讓指令碼連線至網址端點，藉此發出要求及接收回應。

後續步驟

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

請參閱資料格式化，瞭解下一個程式碼研究室。