跳到主要內容區塊

ntuccepaper2019

專題報導

Google試算表與Google App Script的自動化應用
  • 卷期:v0019
  • 出版日期:2011-12-20

作者:周秉誼 / 臺灣大學計算機及資訊網路中心作業管理組碩士後研究人員


Google試算表的線上服務不但讓人隨時隨地都可以存取、編輯試算表文件,配合以Google App Script撰寫的客製化程式,更能夠增進文件和日常工作的自動化處理效率。Google也持續更新Google App Script的程式界面,讓Google試算表和Google App Script的功能更強大,也是雲端運算與日常工作最完美的結合。
 

前言

隨著網際網路成為日常生活中最方便的聯絡及資訊交流方式,利用網頁及電子郵件,是最方便、最快速的收集和散佈訊息的方式之一。以舉辦研討活動為例,可以透過投稿報名系統收集稿件及參加者的資訊、利用電子郵件將各項訊息通知傳達給報名者,再將與會人員的資料統整匯出,這樣的流程是現在最方便、最常見的活動報名方式,不論大小研討會、同學聚會都可以使用這個方式。然而,要建置報名系統,就需要使用PHP、Python等用來產生動態網頁 (dynamic web page) 的程式語言來撰寫系統頁面、透過MySQL等資料庫 (database) 儲存報名者的資訊,此外還需要有持續開啟的伺服器主機來接受網頁連線。在系統開發和伺服器管理上,都需要專業的人力來進行。
Google試算表 (Google Spreadsheet) 服務是Google文件 (Google Docs) 服務的一部份,由Google公司在2006年推出,功能和介面都與傳統的Numbers及Excel等試算表程式相似。不但可以用表格方式儲存及編輯各項數值和公式,並依公式計算與更新表格內的數值、產生圖表,還可以匯出匯入xls、csv、及ods等不同的試算表檔案格式,並發佈在網路上、或與特定使用者共享。

 

Google表單

以線上服務方式操作的Google試算表和傳統試算表軟體最大的不同,就是可以很方便地透過網路收集資料及數據。Google試算表提供了表單 (form) 介面,可以為每一個試算表檔案建立不同的表單,並發佈在網路上讓人填寫,收集到的資料會自動寫入該試算表中。要編輯修改表單,只要用滑鼠拖曳和點選功能按鈕,就可以選擇不同的問題類型。問題類型可分為文字、段落文字、單選按鈕、核取方塊、從清單中選擇、等級、和方格等不同類型。文字相當於HTML表單中的text、段落文字相當於HTML表單中的textarea、單選按鈕相當於radio、核取方塊相當於checkbox、從清單中選擇相當於select及option。等級和方格則是結合了多個問題及各等級的單選按鈕排列成橫向,方便進行多個問題的輸入,很適合用在問卷調查等問題較多的場合。表單的外觀也可以由不同的表單主題,來改變成符合情境的風格。


圖一 Google表單問題類型 (由Google表單服務產生)

 

每一個表單都會有一個獨特的網址 (URL),可以用瀏覽器直接連結到該表單,或是利用iframe將表單嵌入其他網頁。還可以用GET的方式,透過表單的網址和參數,將部份欄位預先填入數值和資料。欄位的參數名稱是entry_0、entry_1等,從0開始依序編號,如entry_0=abc,就會在第一個欄位預先填入abc的值、要預先多選的核取方塊可用「|」符號連接多個選項,如entry_3=dog|cat等。當表單資料被送出時,也會自動記錄該筆資料的時間戳記 (timestamp),供後續分析或核對使用。

 

Google App Script程式界面

Google試算表也提供了撰寫程式的界面,可以使用Google App Script開發程式,來使用或修改Google試算表裡面的資料。Google App Script是一個以JavaScript為基礎的伺服器端腳本語言 (server-side scripting language),不像一般以瀏覽器為主 (browser-based) 的JavaScript,Google App Script是運行在Google的伺服器上,所以能直接存取存放在Google伺服器中的資料。除了Google試算表外,Google App Script也可以跟Gmail聯絡人服務、Google日曆、Google地圖、Google Site等服務連結,透過Google提供的API (application programming interface) 來操作及存取這些服務的資料。

 

在Google試算表的工具選單中可以找到指令碼編輯器 (Script Editor),就可以直接使用這個編輯器的介面開發自己的Google App Script程式。指令碼編輯器就像一般整合開發環境 (IDE、integrated development environment) 的程式碼編輯器,會針對程式碼中不同的保留字及變數顯示不同的顏色,方便程式設計師分辨不同的程式區段。保留字var、function、或while、if等程式結構的保留字,會被顯示為紫紅色;變數名稱會顯示為亮藍色,物件的方法 (method) 及函數呼叫會顯示為黑色,註解會顯示為棕色等等。指令碼編輯器還可以設定指令碼的觸發程序 (trigger)、設定指令碼的時區 (timezone)、執行指令碼及函式、查閱指令碼執行記錄和錯誤訊息、以及檢視指令碼的版本 (revision) 和修改記錄等等。甚至還有除錯器 (debugger),可以在指令碼中設定中斷點 (breakpoint) 並即時顯示變數的數值,功能與一般整合開發環境十分相似。


圖二 Google App Script指令碼編輯器

 

Google App Script提供了許多API,讓程式設計師可以很方便地呼叫使用。Base Service裡提供使用者及session的資訊;Cache Service讓程式設計師可以使用Google伺服器的memcache來暫存需要花時間運算的結果;Calendar Service可以存取Google日曆、新增行程項目;Charts Service可以利用Google圖表工具 (charts tool) 服務繪製圖表;Contacts Service可以存取使用者在Gmail裡的聯絡人;Mail Service可以用來寄送信件;Soap Service提供了以簡單物件存取協定 (SOAP, simple object access protocol) 使用其他Web服務 (web service)的介面;Spreadsheet Service可以對Google試算表中的資料讀取和操作;Ui Service可以建立客製化的使用者介面,包括各種按鈕和對話框 (dialog box);UrlFetch Service可以和網路上的其他網站連線,存取其他網站的資源。Google App Script配合這些API,就可以完成很多後端系統的工作,或與其他Web服務介接。

 

Google試算表與App Script的應用

以先前提到的活動報名系統為例,可以先建立報名的表單,將需要填寫的各個欄位以適當的問題類型設定好,也可在每個問題的說明欄位增加詳細的描述。並在表單主題清單裡,選一個合適的主題風格,增加報名頁面的美觀。完成後可用電子郵件通知及散佈報名表單的網址,邀請其他朋友來報名,也可以利用iframe把報名表單直接嵌入活動的網頁。報名截止後,就能用Google App Script提供的Mail Service寄送活動時程等資訊給已報名的人。


圖三 活動報名表單範例 (由Google表單服務產生)
 

要依照Google試算表中的內容寄送信件,可以分為三個部份。第一個部份是主程式,用來初始設定、填入參數,並呼叫其他程式部份、串接整個寄信流程,是寄信時直接叫用的函數;第二個部份用來把Google試算表中的資料欄位轉換為JavaScript的物件 (object),以方便其他程式部份使用;第三個部份會將不同收信人的信件內容填入信件範本裡。sendEmails函數就是主程式部份,一開始先設定收件人資料的工作表和範圍、及信件範本標題等資訊;再呼叫getRowsData函數,取得試算表中的收件人資料,轉換為JavaScript物件;最後,針對收件人清單中的每一筆資料,呼叫fillInTemplateFromObject函數把信件內容填入信件範本,並利用MailApp.sendEmail函數將信件寄出。getRowsData函數會先呼叫normalizeHeaders函數建立欄位名稱的列表,再用getObjects函數沿著每一列把每個欄位的資料,依欄位名稱放入物件的陣列裡。fillInTemplateFromObject函數利用match函數找出信件範本中需要代換的變數名稱,到收件人物件的欄位名稱列表找出相對應的值,再用replace函數代換,就可以產生每個收件人不同的信件內容了。類似的流程和程式碼也可以使用在電子報及各類信件通知上。


表一 sendEmails函數指令碼

 


表二 getRowsData函數 (節錄自Google App Script Tutorial範例)

 


表三 fillInTemplateFromObject函數 (節錄自Google App Script Tutorial範例)
 

除了前述的應用外,Google App Script的教學文件還有許多應用方式的範例和指令碼可以參考。另外還有指令碼庫,裡面收集了各種不同類型的指令碼函數,也可以直接安裝使用。Google App Script也不斷更新,推出新的API服務介面,甚至還可以自行客製化使用者介面,未來應該會有更多新奇的應用方式。

 

結語

近年的瀏覽器大戰使得JavaScript引擎越來越有效率,配合HTML5的新效果,讓網頁版線上服務變得更強大、能完成更多工作。此外也讓JavaScript擁有成為新一代網站後端程式語言的潛力,一些以JavaScript為基礎的伺服器端程式架構也被提出來。以Google試算表加上Google App Script的後端程式,不只是一種以JavaScript為基礎的伺服器端程式架構,更可以讓對技術不熟悉的人也能自己開發客製化的小程式,充分利用雲端運算時代的新技術。