程式碼研究室簡介
1. 歡迎使用 Apps Script
什麼是 Apps Script？
Apps Script 是一款快速的應用程式開發平台，可讓您自動設定、自訂及擴充 Google Workspace 服務。Apps Script 可讓您在 Google Workspace 中簡化內部或複雜的工作，既省時又省力。
「Apps 指令碼」功能包括：
- Apps Script 的內建服務可讓您使用指令碼來讀取、更新及控管 Google Workspace 應用程式資料。
- 您可以使用 Apps Script 的瀏覽器內程式碼編輯器建立指令碼，因此不需安裝或執行程式碼開發軟體。
- 您可以設計 Google Workspace 編輯器的使用者介面，直接透過選單項目、對話方塊和側欄啟用這些編輯器的指令碼。
「Google Apps 試算表基本知識」程式碼研究室播放清單是關於 Apps Script 的基本概念，以及如何利用此程式改善「Google 試算表」的服務品質。本程式碼研究室著重於教導 Apps Script 基本知識。
試算表服務
您可以使用 Apps Script 擴充 Google 試算表，節省時間和精力。Apps Script 提供的試算表服務可讓指令碼與您的 Google 試算表檔案及其包含的資料進行互動。您可以使用這項服務自動處理下列常見的試算表工作：
- 建立或修改試算表。
- 讀取及更新儲存格資料、公式和格式設定。
- 建立自訂按鈕和選單。
- 匯入及匯出其他 Google 應用程式或第三方來源的資料。
- 共用及控管試算表存取權。
課程內容
這份播放清單涵蓋了透過 Google 試算表使用 Apps Script 的所有主題：
此播放清單中的程式碼研究室旨在按順序閱讀，因此請先從此開始，按照順序完成，以獲得最佳學習體驗。
請參閱下一節，進一步瞭解這個程式碼研究室的內容。
2. 引言
歡迎來到這個播放清單的第一個程式碼研究室。在本程式碼研究室中，您將瞭解搭配使用 Apps Script 與 Google 試算表的基本概念。具體來說，這個程式碼研究室將重點放在兩個主要概念：巨集和自訂函式。
巨集是在 Google 試算表中記錄的一系列動作。記錄完畢後，您可以啟用巨集，以便在日後使用選單項目或快速鍵來重複這些動作。您可以在 Google 試算表和 Apps Script 程式碼編輯器中建立及更新自己的巨集。
您也可以在 Apps Script 程式碼編輯器中建立自訂函式。和 Google 試算表提供的內建函式 (例如 SUM 或 AVERAGE) 類似，您可以使用 Apps Script 撰寫自訂函式，藉此執行簡易和小眾作業 (例如轉換或字串串連)。建立完畢後，您就可以在 Google 試算表中呼叫這些函式，就像內建函式一樣。自訂函式也可以用於您編寫的儲存格公式中，並視需要與其他函式結合。
請閱讀以下內容，瞭解這個程式碼研究室的概念和需求。
課程內容
- 如何為 Google 試算表建立指令碼。
- 如何瀏覽 Apps 指令碼編輯器。
- 如何建立及更新巨集。
- 如何建立第一個試算表自訂函式。
軟硬體需求
- JavaScript 基本知識
- Google 試算表的基本概念
- 可讀取試算表 A1 標記法
您已完成簡介。前往下一節開始使用巨集。
3. 在試算表中建立巨集
一般來說，使用試算表時，您可以重複執行重複的動作 (包括複製儲存格值、設定格式、建立公式等等)，進而增加繁瑣的處理錯誤。Google 試算表可提供巨集，以自動執行重複動作。巨集可讓您在工作表中「記錄」一系列動作。有了錄製巨集，只要按下幾個簡單的按鍵，就能在試算表中重複執行相同的操作。
本節將說明如何在 Google 試算表中建立巨集。在下一節中，我們將向您說明如何使用 Apps Script 建立巨集。
事前準備
如要繼續操作，請先建立一份內含部分資料的試算表。我們為您建立一個：請按一下這個連結來複製資料工作表，然後按一下 [建立副本]。
試算表範例檔案會存放在您的 Google 雲端硬碟資料夾中，並命名為「10 大最佳海報影片 (2018)」。
建立巨集
使用試算表時，您可以記錄 Google 試算表中的巨集。在此範例中，您會建立一個巨集來為資料的標題列設定格式。只要按照下列步驟進行即可：
- 按一下儲存格 A1 即可將遊標放到資料列中。這是您的標題列。
- 在選單中，依序選取 [擴充功能] > [巨集] > [錄製巨集]。
開始錄製後，Google 試算表會記住您在試算表內完成的所有操作，包括醒目顯示儲存格、新增資料、切換至其他工作表、格式化等等。這些動作會成為「指令碼'」，您會在儲存後立即啟用巨集。
- 在「巨集」對話方塊中，選取 [相對參照]。
- 選取第 1 列。
- 重新調整第一列的填滿顏色，從白色變成深洋紅色 3。
- 將第一列的 [文字顏色] 從黑色改成白色。
- 如要將文字設為粗體，請按下 Ctrl+B 鍵 (如果是 macOS，請按 Cmd+B 鍵)。
- 如要凍結第一列，請選取 [檢視 > 凍結 > 1 列]。
- 按一下巨集對話方塊中的 [儲存]。新的對話方塊會要求您為巨集命名。輸入「&Headert」名稱，然後按一下 [儲存]。
使用試算表'使用者介面可讓您建立巨集格式專屬的巨集。
啟用巨集
如要在 Google 試算表中套用新的巨集，請按照下列指示操作：
- 如要建立工作表，請按一下「新增工作表」圖示
。
- 在新工作表中，新增「A1:C2」的文字。您可以依照下列範例輸入：
- 醒目顯示第一列。
- 若要將巨集套用至所選區域，請按一下 [擴充功能] > [巨集] > [標頭]。
- 按照畫面上的指示授權巨集。
- 重複執行步驟 4，再次執行巨集 (授權巨集會停止第一次執行)。
恭喜！你已瞭解如何在 Google 試算表中套用巨集。您的試算表應如下所示：
巨集可讓您更有效率地建立試算表，在本程式碼研究室的後續部分，您將學習如何讓巨集發揮更大效用。秘密就是你做的事：錄製巨集時，你真正的就是編寫 Apps Script 程式碼。Google 試算表會在幕後建構符合巨集動作的程式碼。在下一節中，您將瞭解如何使用 Apps Script 的編輯器編輯器直接修改程式碼。
4. 指令碼編輯器中的巨集
當您建立巨集時，Google 試算表會將您的動作儲存為 Apps Script 函式。啟用這個巨集後，Google 試算表會呼叫 Apps Script 函式，以相同的順序套用這些動作。
指令碼編輯器
建立巨集後，您可以查看其程式碼。如要查看巨集指令碼，請依序按一下 [擴充功能] > [Apps Script]，開啟 Apps Script 的瀏覽器程式碼編輯器。
指令碼編輯器可讓您以 Apps Script 編寫程式碼，並在 Google 伺服器上執行這些指令碼。
「macro.gs」的分析
檢查目前的指令碼。Google 試算表會在您記錄 Header 巨集時建立 macros.gs 指令碼檔案，並在其中填入名為 Header 的對應 Apps Script 函式。啟用 Header 巨集後，Google 試算表會執行此函式。
請參閱下圖，瞭解 Apps Script 巨集函式的結構。如果您以不同順序記錄步驟，或是在記錄過程中點擊試算表，您的程式碼看起來可能會稍有不同。
第一行是會影響授權的註解註解：
/** @OnlyCurrentDoc */
多數指令碼會在使用者執行前要求某些權限。這些權限控制使用者允許指令碼執行的動作。當指令碼專案顯示 @OnlyCurrentDoc 註解時，Apps Script 只會要求存取及更新目前的試算表。如果沒有這個註解，Apps Script 會要求存取並更新使用者「所有」試算表。當您只使用單一檔案時，最好也加入這個註解。巨集錄音工具會自動為您新增這則留言。
如要瞭解 Apps Script 如何呈現您的巨集指示，請查看函式：
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, sheet.getMaxColumns()).activate();
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
};
這個程式碼會在您啟用 Header 巨集時執行。在 function 之後，標籤 Header() 定為函數名及其其數據。辨識 Header() 不需要參數，因為 Apps Script 中的巨集函式不需輸入。大括號一律使用 Apps Script 函式，
本播放清單中的程式碼研究室將介紹建立巨集的相關課程和概念。現在，您可以查看下方的程式碼說明，以瞭解有關該元件的組成元素及其在建構巨集時扮演的角色。請看第一行：
var spreadsheet = SpreadsheetApp.getActive();
在這裡，getActive() 會傳回一個物件，用於代表試算表目前的有效試算表檔案，並設定為新的變數 spreadsheet。
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, sheet.getMaxColumns()).activate();
這些線條會對應到點選第一列以反白顯示的動作。這就是所謂的 activate。第一行會將目前的工作表儲存在 sheet 變數中，第二行則會使用 getRange() 方法取得整個資料列，然後呼叫 activate() 加以啟用。第一列是用特定的列數和欄數來指定。spreadsheet.getCurrentCell().getRow() 呼叫會傳回目前資料列的數目，sheet.getMaxColumns() 則傳回工作表中的欄數上限。
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
這一段程式碼變得更複雜了。為了以 spreadsheet 有效呼叫方法，程式碼會將三種方法堆疊至 getActiveRangeList()，以防止程式碼重複呼叫此 spreadsheet 方法。透過 Apps Script 編寫程式碼時，您會更熟悉一個在單類別呼叫多個方法的慣例 (也稱為方法鏈)。目前，如需瞭解各項方法的簡單說明，請參閱程式碼區塊：
getActiveRangeList()會在spreadsheet中傳回目前的有效RangeList。在此情況下，它只是前一行啟用的第一列。setBackground(color)和setFontColor(color)方法都會變更有效區域中儲存格的顏色屬性。setFontWeight(fontWeight)會針對有效範圍中的儲存格調整字型寬度。
最後，最後一行會凍結巨集的第一列：
spreadsheet.getActiveSheet().setFrozenRows(1);
這就是您在記錄巨集時產生的指令碼。您不必擔心上述任何問題或方法。說明的用意在於讓您瞭解 Apps Script 針對一般巨集功能所關注的點子，以及未來程式碼研究室所探討的主題。
下一節將主要介紹操控 Header() 函式的程式碼，示範如何使用指令碼編輯器進一步自訂巨集。
使用 Apps Script 自訂巨集
Apps Script 編輯器會顯示您先前在 Google 試算表中建立的巨集。透過調整函式內文的內容，您可以進一步自訂巨集的指示，以便執行其他或額外動作。下列練習示範如何透過指令碼編輯器操控巨集的各種方法。
變更受影響的儲存格
假設您想修改巨集，讓它只影響第一列的前 10 欄，而不是整個資料列。您可以刪除並重新記錄這個巨集。不過，您可以使用 Apps Script 編輯器直接進行變更。方法如下：
- 在指令碼編輯器中，將
sheet.getMaxColumns()替換為10。這項編輯會變更試算表中巨集所套用的儲存格範圍。
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
/* sheet.getMaxColumns() replaced with 10.*/
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- 如要儲存指令碼，請按一下「儲存」圖示
。 - 如要重新命名專案，請輸入「巨集」和「自訂函式」做為新專案名稱，然後按一下 [重新命名]。
- 如要建立試算表，請按一下「新增試算表」圖示 。
- 在指令碼編輯器中，從函式清單中選取 [
Header]，然後按一下 [執行]。
新的工作表中會顯示下列結果：
巨集會修改有效或目標範圍，現在只會影響第一列的部分內容。許多 Apps Script 方法都會使用範圍或 A1 標記法做為參數，指定要對哪些儲存格執行動作。
接下來，讓我們來瞭解如何自訂巨集顏色。
變更巨集的顏色
為了協助您設計試算表中的巨集或其他元素的顏色配置，Apps Script 可以修改範圍或文字的顏色範圍。請依照下列指示，瞭解如何自訂巨集的顏色。
以下操作說明將說明如何變更巨集的背景顏色：
- 在 Google 試算表中，切換回包含原始資料的工作表 (工作表 1)。
- 按一下第一列即可反白顯示。
- 在指令碼編輯器中，將背景色彩
#4c1130替換為#afeeee。這些值使用十六進位三角標記法代表不同的顏色。
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
spreadsheet.getActiveRangeList().setBackground('#afeeee')
/* #4c1130 replaced with #afeeee.*/
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- 如要儲存指令碼，請按一下「儲存」圖示 。
- 從函式清單中選取 [
Header]，然後按一下 [執行]。
在 Google 試算表中，第一列前 10 欄的背景填滿顏色會變更為自訂綠燈的顏色：
將 setBackground(color) 參數中的十六進位顏色標記從 #4c1130 (深洋紅色 3) 切換為 #afeeee (淡綠圖，在試算表上無法存取選項) 時，您可以變更巨集背景顏色的色彩屬性。
您已修改巨集設定的背景顏色。如要同時變更文字顏色，請變更第二個顏色代碼。
- 在 Google 試算表中，按一下第一列，確認該欄仍處於醒目顯示狀態。
- 在指令碼編輯器中，將字型
#ffffff替換成#191970。這樣巨集就會設定海軍藍的字型顏色。
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
spreadsheet.getActiveRangeList().setBackground('#afeeee')
.setFontColor('#191970')/* #ffffff replaced with #191970.*/
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- 如要儲存指令碼，請按一下「儲存」圖示 。
- 從函式清單中選取 [
Header]，然後按一下 [執行]。
返回「試算表」以查看標題列的文字顏色現在變成海軍藍。
現在，您已經瞭解巨集實際上是將試算表動作記錄為 Apps Script 程式碼。下一節將說明 Google Apps 試算表的其他用途：自訂函式。
5. 編寫第一個指令碼：自訂函式
與大多數的試算表應用程式一樣，Google 試算表內建 =SUM() 等多個公式函式，可迅速計算試算表資料。自訂函式是指您使用 Apps Script 指定的函式。定義自訂函式後，您就可以在試算表的任何位置使用此功能，就像內建函式一樣。
本節將說明如何在 Apps Script 中建立可進行金錢轉換的自訂函式。
建立指令碼檔案
請依「巨集」一節中的相同試算表和指令碼專案，依照以下指示瞭解如何建立新指令碼 (最終可以用來建立第一個自訂函式)：
- 如要建立 Apps Script 檔案，請返回指令碼編輯器。
- 在 [Files] (檔案) 旁邊，依序按一下 [Add a file] (新增檔案)
[> Script]。 - 為新指令碼檔案命名
customFunctions，然後按下 Enter。(Apps Script 會自動將.gs副檔名附加到指令碼的檔案名稱)。
編輯器中會隨即顯示名為「customFunctions.gs」的新分頁。
現在您已經建立了自訂函式專用的指令碼，現在可以使用程式碼來填入指令碼。
將美元轉換成瑞士法郎
假想您想要修改“2018' 10 大雜貨 10 大數據）的數據；不以英鎊美元和瑞士法郎的全球數據，不超值全球。您可以透過自訂函式輕鬆完成這項工作。以下練習示範如何建立自訂函式，將美元值轉換成金額。
在編寫第一個自訂函式之前，請修改資料集，讓函式展示適當的輸出結果。步驟如下：
- 在試算表中的 H 欄上按一下滑鼠右鍵。
- 在出現的選單中，按一下 [向右插入 1 列]。
- 在儲存格 I1 中加上「Worldwide_Gross (瑞士法郎)」標籤。
您現在可以儲存一個資料欄，用來儲存轉換自訂函式的結果。接下來，您可以使用指令碼編輯器建立第一個自訂函式。
- 在
customFunctions.gs中，將myFunction()的程式碼替換成下列程式碼：
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The converted total of Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return swissFrancs;
}
這組代碼會將美元轉換成瑞士法郎。請試試下列操作說明，並瞭解如何在工作表中執行自訂函式。
- 如要儲存指令碼，請按一下「儲存」圖示 。
- 在 Google 試算表中，選取 [I2] 儲存格。
- 在函式列中輸入
=USDTOCHF(H2)。
如何將公式套用到資料欄的其他儲存格：
- 將滑鼠遊標移至 I2 儲存格右下角，然後選取小型藍色方塊 (遊標指向藍色方塊時，遊標應轉換成 )。
- 將藍色方塊向下拖曳以醒目顯示範圍 I3：I11。
我現在已經在 H 欄中列出以美元計價的瑞士美元轉換。
恭喜！您已建立第一個自訂函式。下一節將說明包含「USDTOCHF()」的程式碼。
「USDTOCHF()」的分析
初步註解詳細說明瞭程式碼的用途：
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
這類留言區塊經常用在程式設計中解釋。
在這個註解中，您可以指明兩個部分：函式說明 (用於換算金額) 以及說明函式參數和傳回類型的註解。
有了註解，Apps Script 就能運用 JSDoc 記錄及建立程式碼的自動完成提示。您可以查看 USDTOCHF() 中的每個註解對 Apps Script 開發作業有何幫助：
@param：您可以使用@param註解來說明傳送至函式的每個參數。@return：您可以使用@return註解來說明函式傳回的內容。@customfunction：請一律將@customfunction新增至自訂函式的文件註解。當您在註解中輸入函式名稱時，這個註解會通知 Google 試算表自訂自訂函式和內建函式，如下所示：
請注意，自動完成彈出式視窗中顯示的文字，會與您在留言區塊中所做的說明文字完全相符。為了讓自訂功能更易於使用，請確保您建立的說明均完整撰寫且完整。
接著，聚焦於 USDTOCHF() 函式中的程式碼：
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return swissFrancs;
}
如前所述，USDTOCHF() 將數值變數設為美元，再乘以固定匯率，然後在數值變數 swissFrancs 中傳回換算成瑞士法郎的值。輸入參數是將自訂函式加入儲存格時所指定的儲存格值。在此範例中，輸入金額來自 H 欄。輸出值 swissFrancs 會放在函式的儲存格中 (在此範例中為 I 欄)。
自訂函式可以使用數字或字串值，如下一節所示。
串連字串前置字串
假設您希望函式 USDTOCHF() 的數值輸出包含瑞士法郎前置字串 CHF。您可以使用以下服務運算子 (+), 來使用 Apps Script 功能：
- 在指令碼編輯器中，將
@return註解更新為傳回字串而非數字。 - 將
return swissFrancs變更為return 'CHF' + swissFrancs。
+ 運算子會將 CHF 字串附加到 swissFrancs 包含的值前方。您的程式碼現在應如下所示：
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {string} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return 'CHF' + swissFrancs;
}
- 如要儲存指令碼，請按一下「儲存」圖示 。
瑞士法郎字串現在會將 I 欄的值前面加上：
您的自訂函式現在不只會將美元換算成瑞士法郎，也會以字串前置字串輸出貨幣。
進階：擷取外部資料
這是基本的自訂函式使用入門範例，但此範例假設美元與瑞士法郎的匯率是固定的。假設您想要使用「目前」的匯率，因此每次重新載入工作表時，系統都會重新計算這些值來代表目前的轉換。要這麼做的話，您得先找出最新的匯率，也就是 Google 試算表可以立即取得的資訊。但很抱歉，您可以使用 Apps Script 來取得這項資訊。
您可以使用下方的程式碼，將瑞士元換算成美元目前的匯率：
function USDTOCHF(dollars){
// Gets a cache that is common to all users of the script.
var cache = CacheService.getScriptCache();
// Accesses the memory location (rates.CHF) of the script cache.
var rate = cache.get('rates.CHF');
// If a cache miss occurs, the program fetches the current
// CHF rate from an API and stores the rate in the cache
// for later convenience.
if (!rate) {
var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
var result = JSON.parse(response.getContentText());
rate = result.rates.CHF;
cache.put('rates.CHF', rate);
}
// Converts dollars to CHF according to the latest rate.
var swissFrancs = dollars * rate;
// Returns the CHF value.
return 'CHF' + swissFrancs;
}
這個程式碼會使用第三方匯率 API，從金融資訊伺服器擷取目前的匯率。方法是使用 Apps Script 服務 (例如 UrlFetchApp 和 CacheService)。這些進階概念不在這個特定程式碼研究室的範圍內，不過您可以開始瞭解 Apps Script 的自動化功能，以便自動化 Google 試算表中的複雜工作。
自訂函式指南
恭喜您完成自訂功能的練習！您在專案中使用自訂函式時，請務必瞭解這些限制。下列清單概略說明 Google 試算表的自訂函式指南詳細說明：
- 不得建立需要使用者授權的自訂函式。請改為建立自訂函式，以便執行範例資料計算、文字編輯等簡單工作。請參閱使用 Apps Script 服務。
- 請勿將自訂函式與其他內建函式的名稱相同，或是將名稱結尾加上底線。查看命名規範。
- 請勿將變數引數傳送至自訂函式。您只能將確定性 (固定) 值傳送至引數做為自訂函式。傳送變數引數 (例如
=RAND()的結果) 會導致自訂函式毀損。請參閱引數規範。 - 請勿建立時間超過 30 秒的函式。如果作業時間較長，請讓函式的程式碼保持精簡，並且限制範圍。我們會盡可能簡化自訂函式的計算作業。請參閱傳回值指南。
現在，您可以使用指令碼編輯器改善巨集及建立自訂函式，以改善您的試算表。下一節將說明您學到的知識，以及接下來可以採取哪些改善做法。
6. 結語
您已完成 Google 試算表的第一堂 Apps Apps 基礎程式碼研究室。透過建立及編輯試算表巨集和自訂函式，您已瞭解 Apps Script 的基本概念。您可以在下一次程式碼研究室中進一步拓展您的 Apps Script 知識。
這個程式碼研究室是否對您有幫助？
報表主題
- Apps Script 基本概念。
- 如何瀏覽指令碼編輯器。
- 如何建立及更新試算表巨集。
- 如何為 Google 試算表建立自訂函式。
後續步驟
此播放清單中的下一個程式碼研究室介紹了 Apps Script 試算表服務的核心類別和術語。這項服務可讓您使用 Apps Script 精確地控管 Google 試算表中的資料值和呈現方式。
前往試算表、試算表和範圍尋找下一個程式碼研究室。