NodeMCU 教學 - 11：使用 NodeMCU 以及 Google script 寫入&讀取 Google sheets 資料

利用 GET URL 將資料寫入 Google sheet

首先要在 Google script 新增一個文件

function doGet(e){
  return handleResponse(e);  
}

//Recieve parameter and pass it to function to handle

function doPost(e){
  return handleResponse(e);
}  

// here handle with parameter

function handleResponse(request) {
  var output  = ContentService.createTextOutput();
  
  //create varibles to recieve respective parameters
  
  var message = request.parameter.message;
  var id = request.parameter.id;
  
  //open your Spread sheet by passing id
  
  var ss= SpreadsheetApp.openById(id);
  var sheet=ss.getSheetByName("Sheet1");
  
  //add new row with recieved parameter from client  
  var cell = sheet.getRange("A1");
  cell.setValue(message);
  
 
  var callback = request.parameters.callback;
  if (callback === undefined) {
    output.setContent(JSON.stringify("Success"));
  } else {
    output.setContent(callback + "(" + JSON.stringify("Success") + ")");
  }

  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;


}

接著點選 發佈 -> 佈署為網路應用程式 -> 任何人，甚至是匿名使用者

-> 點選授權

-> 複製 目前的網路應用程式網址 ，如以下

https://script.google.com/macros/s/AKfycbwtPotksGnb3lHnEwtbF7dS1axJFCpTKhGrfXyPyb4E7y911wk/exec

藍色是你的 Google script ID

接著新增一個 Google Sheet ，名稱可以隨便，但需要把 "工作表1" 更改為 "Sheet1"(最下方)

往 Google sheet 網址的地方看去，基本上會長這個樣子

https://docs.google.com/spreadsheets/d/1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ/edit#gid=0

紅字的部分就是你這個 Google sheet 的 ID，把它複製下來

接著再打開新的瀏覽器頁面，在網址處輸入

https://script.google.com/macros/s/AKfycbwtPotksGnb3lHnEwtbF7dS1axJFCpTKhGrfXyPyb4E7y911wk/exec?message=Write_test&id=1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ

也就是在目前的網路應用程式網址後面加上問號後，再加入一些參數

要加什麼參數已經在 google script 裡面寫好了

藍色是你 Google script 的 ID
紅色是 Google sheet 的 ID
粉色(Write_test) 是你要傳送到 A1 儲存格的 data

至於為什麼是傳送到 A1 儲存格呢，這是因為我們在 Google script 裡面寫的。上方程式碼第 27 及 28 行就是說把 message 這個資料傳送到 A1 儲存格的意思。

結果如上圖，我們利用了 URL 直接對 Google sheet 裡面的儲存格資料進行修改。

如果想要傳送的資料不僅僅是要更新 A1 儲存格，而是需要一直沿著 A2 、 A3 ... 等等下去的話，可以修改 Google script 內的程式，並使用 append 來進行。


Hint : 此法最厲害的地方在於就算你這個文件設定為 "知道連結的使用者僅可檢視" ，它還是可以透過 URL 直接去修改儲存格裡面的內容


利用 GET URL 從 Google sheet 讀取單一儲存格資料

Google script 部分

function doGet(request) {
  var output  = ContentService.createTextOutput(),
      data    = {},
      id      = request.parameters.id,
      ss      = SpreadsheetApp.openById(id);
  
  sheet = "Sheet1";  
  data.records = readData_(ss, sheet);
  ///////////////////////////////////////////////////////////////////
  var callback = request.parameters.callback;  
  if (callback === undefined) {
    output.setContent(JSON.stringify(data));
  } else {
    output.setContent(callback + "(" + JSON.stringify(data) + ")");
  }
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}


function readData_(ss, sheetname, properties) {

  if (typeof properties == "undefined") {
    properties = getHeaderRow_(ss, sheetname);
    //properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
  }
  
  ///////////////////////////////////////////////////////////
  var rows = getDataRows_(ss, sheetname),
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};

    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    
    data.push(record);

  }
  return data;
}

function getDataRows_(ss, sheetname) {
  
  var sh = ss.getSheetByName(sheetname);

  var sh4 = sh.getRange(1, 1, 1, 1).getValues();

  return sh4;
}

function getHeaderRow_(ss, sheetname) {
  
  var sh = ss.getSheetByName(sheetname);
  var sh1 = sh.getRange(1, 1, 1, 1).getValues();

  return sh1;
}
             

瀏覽器網址輸入

https://script.google.com/macros/s/AKfycbzFROuEnBO8AbD7r-xJDVKReK2aiOAHCYfX-gGDUxS8YZ9Y-FN3/exec?id=1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ

藍色的部分要更改成你的 Google scrpit ID
紅色部分改成你的 Google sheet ID

結果如上，回傳是一個 json 格式的資料
事實上這個 google script 的程式是我從另一個程式中節錄並且修改的，更適合取得的是多筆資料的 json 回傳，例如下面這種


利用 GET URL 從 Google sheet 讀取連續儲存格資料

Google Script 部分

function doGet(request) {
  var output  = ContentService.createTextOutput(),
      data    = {},
      id      = request.parameters.id,
      number     = request.parameters.number,
      ss      = SpreadsheetApp.openById(id);
  
  sheet = "Sheet1";  
  data.records = readData_(ss, sheet, number);
  ///////////////////////////////////////////////////////////////////
  var callback = request.parameters.callback;  
  if (callback === undefined) {
    output.setContent(JSON.stringify(data));
  } else {
    output.setContent(callback + "(" + JSON.stringify(data) + ")");
  }
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}


function readData_(ss, sheetname, number, properties) {

  if (typeof properties == "undefined") {
    properties = getHeaderRow_(ss, sheetname);
    properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
  }
  
  ///////////////////////////////////////////////////////////
  var rows = getDataRows_(ss, sheetname, number),
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};

    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    
    data.push(record);

  }
  return data;
}

function getDataRows_(ss, sheetname, number) {
  
  var sh = ss.getSheetByName(sheetname);


  var sh4 = sh.getRange(2, 1, number, 2).getValues();

  return sh4;
}

function getHeaderRow_(ss, sheetname) {
  
  var sh = ss.getSheetByName(sheetname);
  var sh1 = sh.getRange(1, 1, 1, 2).getValues()[0];

  return sh1;
}

瀏覽器的部分

https://script.google.com/macros/s/AKfycbx32dxvaDkX_lCHYb-kYuWcZqe0Vb5-XdvJBEW8tHWtT5pOPEld/exec?id=1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ&number=5

粉色的部分(number)代表你要從 B2 開始往下取幾筆 data (含B2)

利用 GET URL 從 Google sheet 讀取非連續儲存格資料

Google script

function doGet(request) {
  var output  = ContentService.createTextOutput(),
      data    = {},
      id      = request.parameters.id,
      ss      = SpreadsheetApp.openById(id);
  
  sheet = "Sheet1";  
  data.records = readData_(ss, sheet);
  ///////////////////////////////////////////////////////////////////
  var callback = request.parameters.callback;  
  if (callback === undefined) {
    output.setContent(JSON.stringify(data));
  } else {
    output.setContent(callback + "(" + JSON.stringify(data) + ")");
  }
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}


function readData_(ss, sheetname, properties) {

  if (typeof properties == "undefined") {
    properties = getHeaderRow_(ss, sheetname);
    properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
  }
  
  ///////////////////////////////////////////////////////////
  var rows = getDataRows_(ss, sheetname),
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};

    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    
    data.push(record);

  }
  return data;
}

function getDataRows_(ss, sheetname) {
  
  var sh = ss.getSheetByName(sheetname);

  var str1 = "B" + ( 2 );
  var str2 = "B" + ( 3 );
  var str3 = "B" + ( 5 );
  var sh4 = CONCAT(sh.getRange(str1).getValues(),sh.getRange(str2).getValues(),sh.getRange(str3).getValues());

  return sh4;
}

function getHeaderRow_(ss, sheetname) {
  
  var sh = ss.getSheetByName(sheetname);
  var sh1 = sh.getRange(1, 1, 1, 2).getValues()[0];

  return sh1;
}
             
function CONCAT(range1,range2,range3){
  return (range1.concat(range2)).concat(range3);
}

https://script.google.com/macros/s/AKfycbwb6GDo6jj61pbXWCPg2Kr1H1ax9GHSGaWB5Yn6nSMx9R4NQ-q7/exec?id=1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ

Hint : 我對於 Google script 的寫法不甚熟悉，目前還不知道該如何修改才能正確讓資料數値與 No. 正確的對應。

得到如 

"A":33
"B":22 

如果對於 Header 沒有特別要求，只需要取得數據的話那上方的程式碼就可以用。


NodeMCU 的部分，目前有兩種方法可以取得 Google sheet 的資料

方法一是配合上面的 Google script 。這個方法的彈性可以很大，只要你 Google script 的地方寫好就可以取得任意儲存格的資料，無論是連續或是不連續的。

由於使用 Google script 取資料的話必須要走 https 的協議，所以我們常用的 WIFI 函式庫並不能使用，而必須使用這個函式庫

HTTPSRedirect

下面的程式碼一樣在 Arduino IDE 裡面進行編譯，並且我用讀取單一儲存格來做為範例

#include "ESP8266WiFi.h"
#include "HTTPSRedirect.h"

const char* ssid = "wifi帳號";
const char* password = "wifi密碼";
const char* host = "script.google.com";
const char *GScriptId = "AKfycbzFROuEnBO8AbD7r-xJDVKReK2aiOAHCYfX-gGDUxS8YZ9Y-FN3"; // Google script ID
const int httpsPort = 443;

HTTPSRedirect* client ;

void setup() {
  Serial.begin(115200); 
  Serial.print("Connecting to wifi: ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
 
  client = new HTTPSRedirect(httpsPort);
  client->setPrintResponseBody(true);
  client->setContentTypeHeader("application/json");
  client->connect(host, httpsPort);
  
  Serial.print("Connecting to ");
  Serial.println(host);
}

void loop() {

  String url3 = String("/macros/s/") + GScriptId + "/exec?id=1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ";
  
  Serial.println("GET Data");
  if (client->GET(url3, host)){
    Serial.println("GET SUCCESS");
  }
  
  delay(4000);                          
}

使用上面的 code 就能從 Google sheet 上抓取到單一儲存格的資料了

要抓取多個儲存格的話也是一樣，就把 String url3 後面那個地方修改一下就可以

但不曉得是這個函式庫有點問題，還是 NodeMCU 不是很穩定，用這個方式抓取資料很容易造成版子一直重新啟動。

所以我在實作的部分還是沒有用這個方法來讀取資料，而是使用下面的方法

方法二

一樣是利用發送 GET request 的方式來抓 Gooel sheet 的資料

不過我們使用的是 Google sheet 本身就支援的 Google sheet api v3

現在最新的是 V4 ，而這版本我不用是因為它還要額外有一些 Oauth2 認證的問題，太麻煩了

Google sheet api v3 簡單的多，首先你必須把要讀取的 Google sheet 發佈到網路，如下圖

接著在 URL 網址的地方輸入

https://spreadsheets.google.com/feeds/cells/1zekq3JSBTRDbSUN-vvDWfSji4GjqMOet8Yfw1liUFBQ/1/public/values?alt=json&range=B2:B5

粉色是你的 Google sheet ID
range 的地方是你要抓的儲存格

會得到以下的結果

看起來很亂，我們放到 JSON editor online 裡面看一下

在 $t 的地方就藏著儲存格的資料

雖然這個方法會得到一堆我們不想要的資料，但該方法在 Nodemcu 上面運行良好，沒有讓版子有不穩定的問題(前面會不穩定不曉得是 library 還是 google script 沒寫好)

這個方法除了會得到一堆不想要的資料外，還有另外一個缺點是只能指定連續的儲存格

且經過我實測，在 NodeMCU 上只能連續取 三個儲存格 ，否則也會遇到版子不穩定的狀況發生


code 的部分參考如下，此為關鍵部分的 code。 Library 用 Arduino 本身就有自帶的 WiFiClient 就可以。

要怎麼使用可以參考此連結

《進階※應用篇》寫程式Arduino教學 - 11：使用 WeMos D1 mini NodeMCU 網路爬蟲 抓取 JSON 天氣資訊

// =====  EQ. setup =====
const char    *spreadsheetId = "Google sheet 上的 ID";
const char    *EQhost = "spreadsheets.google.com";
char          EQrespBuf[1024*8];
static int    EQ_Deep = 0;
static int    EQ_distance = 0;

/* =====================*/
// ===== get_EQ ====
/* =====================*/
bool get_EQ()
{
  WiFiClient client;

  Serial.print("\nConnecting to ");
  Serial.println(EQhost);
  if (!client.connect(EQhost, 80)) {
    Serial.println("Connection failed");
    return false ;
  }
  
  // ====== Create a URI for the request =====
  String url = "/feeds/cells/";
  url += spreadsheetId;
  url += "/1/public/values?alt=json&range=B2:B5";
  
  Serial.print("Requesting URL : ");
  Serial.println(url);
  
  client.print(String("GET ") + url + " HTTP/1.1\r\n" +
               "Host: " + EQhost + "\r\n" + 
               "Connection: close\r\n\r\n");
  client.flush();
  
  int EQrespLen = 0;
  bool skip_headers = true;
  
  while (client.connected() || client.available()) {
    if (skip_headers) {
      String aLine = client.readStringUntil('\n');
      if (aLine.length() <= 1) {
        skip_headers = false;
      }
    }
    else {
      int bytesIn;
      bytesIn = client.read((uint8_t *)&EQrespBuf[EQrespLen], sizeof(EQrespBuf) - EQrespLen);
      //Serial.print(F("bytesIn ")); Serial.println(bytesIn);
      if (bytesIn > 0) {
        EQrespLen += bytesIn;
        if (EQrespLen > sizeof(EQrespBuf)) EQrespLen = sizeof(EQrespBuf);
      }
      else if (bytesIn < 0) {
        Serial.print(F("read error "));
        Serial.println(bytesIn);
        return false;
      }
    }
    delay(10);
  }
  
  //Serial.println("\nClosing connection");
  client.stop();

  if (EQrespLen >= sizeof(EQrespBuf)) {
    Serial.print(F("EQrespBuf overflow "));
    Serial.println(EQrespLen);
    return false;
  }
  
  // Terminate the C string
  EQrespBuf[EQrespLen++] = '\0';
  //Serial.print(F("EQrespLen "));
  //Serial.println(EQrespLen);

  StaticJsonBuffer<1024> jsonBuffer;
  
  char *jsonstart = strchr(EQrespBuf, '{');
  if (jsonstart == NULL) {
    Serial.println(F("JSON data missing"));
    return false;
  }

  Serial.println("\njsonstart");
  Serial.println(jsonstart);

  JsonObject& root = jsonBuffer.parseObject(jsonstart);
  if (!root.success()) {
    Serial.println(F("jsonBuffer.parseObject() failed"));
    return false;
  }

  int temp = root["feed"]["entry"][0]["content"]["$t"];  
  EQ_Deep     = root["feed"]["entry"][1]["content"]["$t"];   
  EQ_distance = root["feed"]["entry"][2]["content"]["$t"];    
  
   
  return true;
}

上方 94~96 行是對收到的 JSON 資料進行解析，也就是對應到下圖(以 94 行舉例)



觀察並對應一下這個 $t 的路徑為 object > feed > entry > 0 > content > $t