Google Spreadsheet（試算表）之 ESP8266 溫濕度紀錄與趨勢圖

網頁最後修改時間：2019/01/07

跟 Google 相處久了之後相信大家應該會發現，作業系統、瀏覽器、電子郵件、雲端硬碟、部落格、地圖、翻譯、YouTube等．．．，它提供的東西還真不少！

不過這篇網頁不是 Google 的推薦文，而是要來介紹它的試算表功能：用它所提供的 Google Apps Script 撰寫能操作試算表的網路應用程式（Web Apps），用來接收來自遠端（ESP8266）的 DHT11 溫濕度值，再寫入到試算表中繪製成即時圖表。

要達到這樣的目的，除了撰寫客戶端的程式之外，最重要的部分就是撰寫伺服器端處理 HTTPS GET / POST Request 的程式，也就是網路應用程式。

網路應用程式是將由 JavaScript 撰寫好的 Google Apps Script 掛載在 Google 試算表（類似 Microsoft Excel VBA 程式），經過發佈、部署為網路應用程式而成。

本文大致的流程如下：

• 申請 Google 帳號；
• 新增一個 Google 試算表檔案（檔案名稱：DHT11紀錄圖表）在 Google 雲端硬碟中；
• 開啟 DHT11紀錄圖表 試算表文件，擷取出網址列中的 Spreadsheet_Key；
• 選擇 Google 試算表選單 "工具 / 程式碼編輯器"，撰寫 Google Apps Script；
• 選擇程式碼編輯器選單 "發佈 / 部屬為網路應用程式"，取得網路應用程式的網址。
此處所撰寫的 Google Apps Scripr 必須符合其網路應用程式的發佈要求。
• 取得與安裝需要的 Arduino 函式庫；
• 撰寫 ESP8266 連線與資料上傳測試程式；
• 下載 openssl 取得與 script.google.com 的 fingerprint；

/*-/--*-*/*/*/*/***//-*-*-**-*/*-*-/*/*/*-*-/-////--/**/**--**/--///--//**----**//--**//**----***//*-**//*
有購買下面商品的使用者，網頁中所需相關資料已放置於雲端硬碟，請自行下載使用！

• V2.1 版 { 萬物皆聯網-ESP8266 IoT(Internet of Things)入門學習套件 }
• ESP8266 UNO Arduino IDE 開發板 (Type B1) 
• ESP8266 Nano Arduino IDE 開發板 (Type A1) 

其餘的使用者，請自行依照提供之連結下載相關資料，程式碼複製貼上使用！
/*-/--*-*/*/*/*/***//-*-*-**-*/*-*-/*/*/*-*-/-////--/**/**--**/--///--//**----**//--**//**----***//*-**//*

【撰寫能發佈為網路應用程式的 Google Apps Script】

Google Apps Script 是以 JavaScript 為基礎，用來撰寫搭配 G Suite 各項服務（它包含 Google 廣受歡迎的網路應用程式，包括 Gmail、Google 雲端硬碟、Google 環聊、Google 日曆和 Google 文件）應用的腳本語言。

使用過微軟的 Excel 應該自然而然地也會熟悉 Google 試算表操作；對於熟悉 Excel VBA 巨集指令的看倌，Google Apps Script 操作 Google 試算表的指令兩者也很類似！

「為什麼要撰寫 Google Apps Script 操作 Google 試算表呢？」

因為 Google 試算表是放在 Google 的雲端硬碟（伺服器）上面。要去存取它，需要 Google 伺服器上有能處理客戶端 HTTPS GET / POST request 的程式碼才行，而且必須符合其 Web Apps（網路應用程式）的程式撰寫規定才行。

一但撰寫的 Google Apps Script 成功發佈為網路應用程式後，最終就會得到一個目前的網路應用程式網址。HTTPS GET / POST Request 就是以這個網址為基礎做操作，使用方式可參考下面的部落格網頁：

• 初遇 IoT ( Internet of Thing, 物聯網 ) - 使用 Arduino 控制 ESP8266 無線模組上傳資料到 IoT Server ( ThingSpeak )
• 使用 Arduino IDE 開發 ESP8266 物聯網應用 - ThingSpeak, HTTP GET / POST 資料上傳方法
• 使用 Arduino IDE 開發 ESP8266 物聯網應用 - 取回 ThingSpeak 特定 Channel 和 Field 最後一筆資料
• 當 ESP8266 遇上中華電信 IoT 智慧聯網大平台 { 入門 - 01 } - 如何使用 ESP8266 利用 AT 指令取回中華電信 IoT 智慧聯網大平台上的設備感測器數據

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//
* 新增 Google 試算表：

進入到 Google 雲端硬碟，左上角有一個 "新增" 的按鈕，按下 "Google 試算表" 建立一個叫做 DHT11紀錄圖表 的檔案（檔名填在開啟的試算表左上角），其中：

• 工作表命名為 #01；
• 欄位內容 A2 填入 Time、 B2 填入 Temperature (°C)、 C2 填入 Humidity (%)；
• 下方欄位內容 A3 填入 2018/12/11 下午 3:05:08、 B3 填入 20、 C4 填入 65；
  如果沒有照著在第三列輸入數值，折線圖就只會空白一片，無法在設定時看到相對應的反應。

在選單 "插入 / 圖表"，選擇圖表類型 折線圖。其中，在 資料 頁面：

• 指定 資料範圍 為 A2:C1000
  當輸入資料大於預設的範圍大小時，試算表會自動更新折線圖的輸入資料範圍，不需要自己新增表格列。
• 勾選 使用第2列作為標題 和 使用 A 欄作為標籤 這兩個選項

在 自訂 頁面：

• 標題文字 修改為 Temperature (°C), Humidity (%)

完成設定之後，就會看到類似下面折線圖的樣子。

此時，就可以將第三列的暫填內容刪除。

範例之 Google 試算表設定

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//

* 撰寫符合 Web Apps 的 Google Apps Script ：

在開啟的 Google 試算表上方的瀏覽器網址列中，擷取出在 /d/ 到 /edit 中間的那一大串文字，這是屬於該試算表的 Spreadsheet_Key，用它就可以在程式中進行操作。

Web Apps 需要 Google Apps Scripr 撰寫的內容符合它的要求，否則無法正確地被觸發

Google Web Apps

再者，程式碼雖是使用 JavaScript，但若是熟悉 Microsoft Excel VBA 語法就不難發現，其讀寫試算表所使用的程式碼兩者相當類似

Class Sheet

當然，撰寫 Google Web Apps 基本的語法部分，不可能在這小小的篇幅涵蓋，但是相關資料已經列在上面，接下來所用的觀念與語法大都能在上面連結中找到說明。

在試算表選單選擇 "工具 / 程式碼編輯器"，開啟程式碼編輯器畫面。左上角專案名稱填入 DHT11_HTTPGET，最後複製 / 貼上下面程式碼

gscript.gs

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

var SPREADSHEET_URL_KEY = 'Spreadsheet_Key'; // 試算表網址列上的 Spreadsheet_Key var SHEET_NAME = '#01'; // 試算表表格名稱 var SS = SpreadsheetApp.openById( SPREADSHEET_URL_KEY ); // 在伺服器端開啟試算表 var sheet = SS.getSheetByName( SHEET_NAME ); // 開啟試算表表格 #01 // 當接收到客戶端的 HTTP GET 要求 function doGet( e ) { var temperature = e.parameter.T; var humidity = e.parameter.H; var newRecord = e.parameter.newrecord; if( newRecord !== undefined && newRecord === "" ) { clear(); return ContentService.createTextOutput( "Successfully make a new record." ); } // 溫度和濕度值必須一起傳送 if( temperature !== undefined && humidity !== undefined ) { if( temperature < 0 || temperature > 50 || humidity < 20 || humidity > 90 ) return ContentService.createTextOutput( "Temperature or humidity out of range!" ); else { var row = sheet.getLastRow() + 1; // 取得可輸入資料的空白列 var t = new Date(); // 取得日期 sheet.getRange( "A" + row ).setValue( t ); // 輸入日期到 At sheet.getRange( "B" + row ).setValue( temperature ); // 輸入溫度到 Bt sheet.getRange( "C" + row ).setValue( humidity ); // 輸入濕度到 Ct } return ContentService.createTextOutput( "Successfully wrote: " + temperature + "°C and " + humidity + "%\ninto spreadsheet." ); } } function clear() { var lastrow = sheet.getLastRow(); //** 清空全部內容 sheet.clearContents(); //** 刪除 row 1000 之後的列 if( lastrow > 1000 ) sheet.deleteRows( 1001, lastrow - 1000 ); //** 填入 A2:C2 的標題 sheet.getRange( "A2" ).setValue( "Time" ); sheet.getRange( "B2" ).setValue( "Temperature (°C)" ); sheet.getRange( "C2" ).setValue( "Humidity (%)" ); // 完成動作的提示 SS.toast( 'Row data cleared', SHEET_NAME, 5 ); }

儲存檔案後，再選擇選單 "發佈 / 部屬為網路應用程式"。將上面的程式碼發佈為可使用 HTTP GET / POST 的網路應用程式。

首先，在第一個出現的畫面是最重要，進行下面修改後再按下 "部署" 按鈕：

• 專案版本：選擇 新增；在下面的欄位可寫下對於這個版本的描述，但建議使用數字；
  每一次修改程式之後，就算是存檔都不會使用存檔後的程式碼做執行，必須再重新發佈一次，並且新增一個版本號。
• 將應用程式執行為：我 (.......@gmail.com)；
• 具有應用程式存取權的使用者：任何人、甚至是匿名使用者；
  不要選擇其他選項，否則會無法執行！

部署 Google App Script 為網路應用程式 - 01

 如果是第一次部署，會出現 需要授權 的要求，按下 "核對權限" 按鈕進行授權。

部署 Google App Script 為網路應用程式 - 02

這會另外打開一個視窗，要求使用者進行 Google 帳號的登入，選擇要使用的帳號做登入。

部署 Google App Script 為網路應用程式 - 03

 出現警告提示畫面，選擇 "進階"。

部署 Google App Script 為網路應用程式 - 04

 再選擇要發佈的 Script 名稱："前往 [DHT11_HTTPGET]] (不安全)"。

部署 Google App Script 為網路應用程式 - 04

 點選 "允許" 完成授權。

部署 Google App Script 為網路應用程式 - 05

最後出現的視窗就是完成部署為應用程式的畫面。其中，目前的網路應用程式網址： 所包含的網址要複製下來，這是對部署為 Web Apps 的 Google Apps Script 的 HTTP GET / POST 網址。其中，GSCRIPT_ID 指的是介於網址 /s/ 至 /exec 之間的文字。

部署 Google App Script 為網路應用程式 - 06

OK，現在讓我們來手動輸入資料試試！

舉個使用的 HTTP GET 的實例：

https://script.google.com/macros/s/GSCRIPT_ID/exec?T=19.8&H=68.7

1. 另開一個獨立新視窗和 DHT11紀錄試算表 在瀏覽器上，輸入上面的網址在獨立視窗網址列中按下 ENTER；
2. 成功上傳之後，伺服器會回傳訊息到獨立視窗中；
3. 資料更新至試算表中；

HTTP GET 手動上傳資料

操作非常類似初遇 IoT ( Internet of Thing, 物聯網 ) ...裡面上傳 DHT11 到 ThingSpeak 的方式，只不過 HTTP GET 參數可以自行在 doGet(e) 函式裡定義和決定。

這裡我定義了三個參數：

• T：溫度值，限定範圍在 0 ~ 50 度C；
• H：濕度值，限定範圍在 20 ~ 90% 相對濕度；

** T 和  H 必須成對出現，缺一不可！

• newrecord：後面不接任何數據，用來清空試算表第二列之後的所有資料，作為一個新紀錄；

例如，輸入下面的網址後，會將試算表清空成最初始的狀態：

https://script.google.com/macros/s/GSCRIPT_ID/exec?newrecord

/*-/--*-*/*/*/*/***//-*-*-**-*/*-*-/*/*/*-*-/-////--/**/**--**/--///--//**----**//--**//**----***//*-**//*
注意，不要隨便把 Spreadsheet_Key 和網路應用程式網址給別人！
/*-/--*-*/*/*/*/***//-*-*-**-*/*-*-/*/*/*-*-/-////--/**/**--**/--///--//**----**//--**//**----***//*-**//*

【客戶端的測試程式】

「伺服端的程式搞定之後，那麼客戶端的程式是否就如其他部落格網頁那麼撰寫就可以？」

「代誌沒這甘單！」

用瀏覽器來做很簡單，輸入網址和參數就可以；但是，要用微控制器來做這些動作就有點麻煩！

原因是，當送出 GET https://script.google.com/... 時，的確執行了 doGet(e) 函式裡面的程式碼，可是當程式碼執行完畢所應回傳的內容卻沒有在裡面，取而代之的是 HTTP 狀態碼 302 Moved Temporarily，它裡面有一個轉址的網址

https://script.googleusercontent.com/macros/echo?lib=<落落長lib key >&user_content_keu=<落落長 data key>

微處理器要再 GET 這個網址才能得到回覆的資料，下面來深入看看這個東西！

在 Chrome 瀏覽器打開一個新視窗，按下 F12 打開 "開發人員工具"，輸入下面網址後按下 ENTER，就會出現類似下面的畫面

https://script.google.com/macros/s/GSCRIPT_ID/exec?T=19.8&H=68.7 

Google Web Apps 資料回傳 - 01

按下 "exec?T=19.8&H=68.7"，旁邊就會出現一個視窗。視窗中，可以看到 General 下面出現跟上面描述的一樣的 Status Code: 302 Moved Temporarily；在 Response Headers 下面有一個  Location:，所包含以 https://script.googleusercontent.com/... 開頭一長串的東西就是轉址的地址，要再前往該地址才可以得到 doGet() 執行之後結果。

Google Web Apps 資料回傳 - 02

接著按下 "echo?user_content_key=..." 這一串文字，它代表轉址之後所接收到的資料，也就是視窗左邊網頁中所出現的文字內容

Successfully wrote: 19.8 °C and 68.7%
into spreadsheet.

Google Web Apps 資料回傳 - 03

所以說，面對瀏覽器進行手動測試的時候，除非像我們這樣做，否則不會看到中間轉址的過程。若現在換成使用微控制器來完成這個動作的話，若無轉址的處理只會得到 Google Web Apps 資料回傳 - 02 畫面上的 Request Headers，完全得不到任何想要的回應內容。

轉址的問題，可以使用第三方網站（例如 PushingBox，有每天 1000 次的限制）幫忙轉址再取回資料，或是安裝有這功能的函式庫就能解決（本文採用的方式）。

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//
* 軟硬體環境：

• Windows 10 64-bit；
• Arduino IDE v1.8.8；
• ESP8266 Core for Arduino v2.4.2；
  v 2.4.0 以上的應該都可以，但是現在先不要使用 v2.5.# 版本的，否則函式庫或是程式碼會不相容！
• HTTPSRedirect v2.0；
  現在、不要、使用新於此版本的函式庫，否則程式碼可能會不相容！
• Adafruit Unified Sensor v1.0.3；
• DHT11 sensor library v1.3.0；
• ---------------------------------------------------
• ESP8266 WiFi 模組；
  各系列都可以用。
• ESP8266 ESP-01/01S WiFi 無線溫溼度（DHT11）節點底板；
  單獨 DHT11 加上 ESP8266 也可以。

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//
* 電路接線：

下圖是 ESP8266 ESP-01 / 01S 無線溫溼度（DHT11）節點底板的參考電路圖，程式碼可直接使用於此底板。

若無此底板，只要有 ESP8266 模組就可以用，記住 DHT11 的 <DATA> 是要接到 ESP8266 晶片的 <GPIO2> 接腳就可以，其他電路的部分不需要理會。

ESP8266 ESP-01/01S WiFi 無線溫溼度（DHT11）節點底板參考電路圖

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//
* 程式碼：

程式碼下載

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//
* fingerprint 的取得：

因為跟 script.google.com 使用的是 https 安全性的連線，而且 ESP8266 的 Arduino 套件函式庫 WiFiClientSecure 支援與遠端伺服器進行 TLS v1.2 和 X.509 證書的指紋驗證（fingerprint verification），因此可以藉此驗證雙方通訊的安全性。

當然，就算執行過程不驗證或不成功也沒關係，只不過不能保證連線的安全性而已！

不過對於有強迫症的看倌，可以往下看看怎麼獲得指紋。

經過實際的測試，有兩個方法可以得到與 script.google.com:443 連線的 fingerprint：

• 網站：GRC | SSL TLS HTTPS Web Server Certificate Fingerprint
• 使用 openssl 指令

網站 GRC 是一個比較簡單取得 fingerprint 的方式。首先輸入欲連線的網址（這裡是 script.google.com），再按下 "Fingerprint Site" 按鈕

GRC 取得 fingerprint - 01

開啟的另一個視窗中，欄位 Security Certificate's Authentic Fingerprint 下方以冒號隔開的一長串 16 進制就是 GRC 網站所提供的 fingerprint。以空白取代其中的冒號，就是程式中的 FINGERPRINT。

GRC 取得 fingerprint - 02

OpenSSL 取得 fingerprint 的方式，依作業系統不同，指令稍有差異。

取得 Windows OS下最新版的 OpenSSL（Linux 和 Mac OS 應已內建，毋須安裝）

** openssl 指令 - Linux, Mac OS：

echo | openssl s_client -connect script.google.com:443 |& openssl x509 -fingerprint -noout

Linux, Mac OS - openssl

** openssl 指令 - Windows：

先進入到 openssl / bin 目錄下，再執行下面指令

echo | openssl s_client -connect script.google.com:443 | openssl x509 -fingerprint -noout

Windows - openssl

/*-/--*-*/*/*/*/***//-*-*-**-*/*-*-/*/*/*-*-/-////--/**/**--**/--///--//**----**//--**//**----***//*-**//*
取得與 Google 的 fingerprint 會跟所在的地理環境和時間有關係！

雖然經過實際測試，不管使用上述何種方法取得的 fingerprint 都是相同的，但在許可的情況下，應該都要以 openssl 取得的 fingerprint 為主。

另外一點，fingerprint 的取得是有時效性的，可能幾個小時或是幾天就會失效，但相比使用第三方網站轉址所提供的免費限制，就需要定期更新新的 fingerprint，兩者各有利弊！

一個不會在這裡討論的是直接進行  root certificate 的驗證，取得的證書時效可達幾年或幾十年以上，幾乎不需要時常做更換，缺點是很佔 ESP8266 的記憶體空間！
/*-/--*-*/*/*/*/***//-*-*-**-*/*-*-/*/*/*-*-/-////--/**/**--**/--///--//**----**//--**//**----***//*-**//*

/*--*//**---/*///**---*-*////***--*/*///***----*///--*/*///**--*/*//**--**/*//
* 程式執行結果：

程式執行結果分為兩個部分：UART 輸出和 Google 試算表。

程式一開始會連線至預先定義好的 SSID，接著進行與 script.google.com 的安全性驗證。預設驗證的成功與否，只會輸出相對應的文字提示，即便驗證失敗也不影響繼續的往下執行，可自行添加驗證成功或失敗處理的程式碼在其中。

指紋驗證執行完後會進入一個 5 秒鐘計時的迴圈。只要在設定的時間區隔內偵測到 GPIO0 接地，則會發送一個 HTTP GET 要求至 Google 伺服器，要求清除 Google 試算表中的資料以建立一個新的表格。如果要執行這個動作，只要在通電開機後將 GPIO0 接地即可，但不可在通電的同時接上！

程式到此之後就進入 loop() 函式。會定時上傳 DHT11 溫溼度資料，並且輸出接收到 Google 伺服器執行 doGet() 回傳的訊息。

展示程式執行時的一部分輸出

 經過一段時間的資料蒐集，Google 試算表呈現的樣子如下圖所示。

展示程式執行一段時間之後的 Google 試算表格數據與 DHT11 溫濕度趨勢圖

【結論】

早前所介紹的溫溼度都是利用 IoT Server 來做紀錄，本文則是改用類似 Microsoft Excel 的 Google 試算表來做，雖然多了必須撰寫 Google Apps Script 的動作，但是這部分的程式碼卻是不難理解，而且擴展參數也相當容易，依樣畫葫蘆就可以實現另外不同的功能出來。

由於記錄的參數不需要很隱密且數量也不多，所以本文只用 HTTP GET Request 來完成，HTTP POST 的方式可在後續部落格網頁看到相關的應用。

Google 所提供的服務很多，本文只是使用了其中的非常小的一個部分，還有很多值得發覺和使用的，更多的資料可查閱 Google Apps Script。