7.寫入Google試算表(三)

本程式套用新的函式HTTPSRedirect ，讓esp8266 不須透過第三方服務轉址直接寫入Google Sheet

Google Apps Script方Code

function doGet(e) {

var params = e.parameter;

var data = params.data;

var SpreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1AArKJnr5OzPvQMkhGTuRhmllu7HwIhJMoZRJnQJUAFI/edit#gid=0'); //此處填入Google試算表的網址

var Sheet = SpreadSheet.getSheetByName('1'); //此處填入試算表的標籤名稱

//var LastRow = Sheet.getLastRow(); //資料上舊下新

Sheet.insertRowBefore(1); //資料上新下舊

//寫入資料

data = data.split(',');

data.forEach(function(e,i){

//Sheet.getRange(LastRow+1, i+1).setValue(e); //(開啟資料上舊下新模式)

Sheet.getRange(1, i+1).setValue(e); //(開啟資料上新下舊模式)

});

return ContentService.createTextOutput(1); //接收成功以後回傳"1"(類似Tingspeak)

}

*註解

試算表的網址直接複製網址列的那一串

即可

Esp8266 方 Code

#include "ESP8266WiFi.h"

#include "HTTPSRedirect.h"

const char* ssid = "填入Wifi SSID";

const char* password = "填入Wifi 密碼";

const char* host = "script.google.com";

const char *GScriptId = "AKfycbx7ni19fQc1Gr6CCGjDKiSXQ9W2JKYloSodKv7yNP4df8jFWZMU"; // Google script ID*(請見後方註解)

const int httpsPort = 443;

int i = 0;

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() {

i++;

String url3 = String("/macros/s/") + GScriptId + "/exec?data=";

url3 += i; //你要傳送的第一筆資料

url3 += ",";

url3 += 2*i; //你要傳送的第二筆資料

Serial.println("GET Data");

if (client->GET(url3, host)){

Serial.println("GET SUCCESS");

}

delay(4000);

}

*註解

GScriptId 的來源請在 Google APPS Script中，請看下圖：

把上面那個網址複製下來後，複製紅色的位置(每個專案的GScriptId不同)

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