Hantar Data ke Google Sheets | NodeMCU/ESP32

Dalam Tutorial ini, MatGyver akan menerangkan kaedah menghantar data dari NodeMCU / ESP32 ke Google Sheets menggunakan API dan Google Apps Script.

Ini merupakan satu kaedah mudah untuk anda menyimpan rekod data-data sensor atau project secara CLOUD di Online, dan anda boleh akses data pada bila-bila masa serta convert file Google Sheets TERUS kepada file .XLS

GOOGLE APPS SCRIPT

Carta-alir komunikasi Data ke Google Sheets

Sebagaimana Ms Excel boleh diprogramkan/automasikan menggunakan Macro/VB-Programming, Google Sheets juga boleh diprogramkan dimana Google Apps Script menjadi base programming platformnya.

Google Apps Script telah disediakan oleh Google sebagai Platform Programming Automasi untuk kegunaan kesemua Applikasi dalam Google Workspace, seperti, Gmail, Google Sheets, Google Meet, Google Calendar dan lain-lainnya.

Dalam carta-alir di atas, CLIENT atau NodeMCU akan menghantar API Request kepada Google Apps Script, dan Google Apps Script akan menerima dan menyusun data ke dalam Google Sheets.

HARDWARE – NODEMCU / ESP32

Dalam tutorial ini, kita akan menggunakan NodeMCU ESP8266, dan anda juga boleh menggunakan ESP32 dengan menggunakan kaedah yang sama diterangkan dalam artikel ini.

STEP 1: CIPTA SPREADSHEET

Bagi memulakan tutorial, kita akan mencipta SpreadSheet baru dalam Google Sheets.

NEW > Google Sheets > Blank Spreadsheet

Berikan nama Sheet ini DHTSensor atau nama yang anda mahukan.

Cipta 4 Nama Data; “name“, “id“, “temperature” dan “humidity“.

Ini hanyalah 4 contoh nama data sebagai contoh simulasi sahaja. Setelah anda memahami kaedah yang betul penghantaran data, anda boleh menggunakan apa nama sekali-pun.

Setelah selesai, pastikan anda savekan Spread Sheet.

STEP 2: SIMPAN URL SPREADSHEET

Pastikan anda menyimpan URL Spreadsheet anda yang bermula dengan docs.google.com dan berakhir dengan #gid=0.

URL ini diperlukan untuk kegunaan Google Apps Script selepas ini.

STEP 3: CIPTA NEW PROJECT – GOOGLE APPS SCRIPT

Seterusnya, kita akan mencipta New Project dalam Google Apps Script.

NEW > More > Google Apps Script

Paparan Apps Script adalah seperti di atas, dan anda boleh terus berikan Nama untuk Project Apps Script ini, contohnya sendData.

STEP 4: SETUP & CODE APP SCRIPT

Berikut adalah Coding Penuh yang anda perlu PASTE dalam App Script.

function doGet(e) { 

var ss = SpreadsheetApp.openByUrl("URL Google Sheets Anda");
var sheet = ss.getSheetByName("Sheet1");

  addData(e,sheet);
}

function doPost(e) { 
  var ss = SpreadsheetApp.openByUrl("URL Google Sheets Anda");
  var sheet = ss.getSheetByName("Sheet1"); 
  
  addData(e,sheet);
}


function addData(e,sheet) {
  var name = e.parameter.name ; 
  var id = e.parameter.id ;
  var temperature = e.parameter.temperature;
  var humidity = e.parameter.humidity;

  sheet.appendRow([name,id,temperature,humidity]);
}

Dalam code ini terdapat 2 Code Function API untuk fungsi POST dan GET.
Ini bermakna, Script ini boleh menerima samada POST Request atau GET Request.

Function ke-3, addData(e,sheet) akan menyusun data ke dalam Google Sheets.

“URL Google Sheets Anda”

function doGet(e) { 

var ss = SpreadsheetApp.openByUrl("URL Google Sheets Anda");
var sheet = ss.getSheetByName("Sheet1");

  addData(e,sheet);
}

function doPost(e) { 
  var ss = SpreadsheetApp.openByUrl("URL Google Sheets Anda");
  var sheet = ss.getSheetByName("Sheet1"); 
  
  addData(e,sheet);
}

Dalam Code Script, gantikan “URL Google Sheets Anda” kepada URL yang telah anda copy dalam STEP 2.

“Sheet1”

Dalam Code Script, pastikan “Sheet1” adalah SAMA dengan SHEET yang aktif dalam Google Sheets anda. Pastikan ejaan Huruf-Besar dan Huruf-Kecilnya sama.

CONTOH CODE PENUH APP SCRIPT

App Script anda perlu kelihatan seperti contoh di atas ini.

STEP 5: VARIABLE PENTING APP SCRIPT

Bahagian addData() adalah bahagian terpenting bagi membolehkan fungsi penerimaan data dari device, kemudian penghantaran data ke Google Sheets.

function addData(e,sheet) {
  var name = e.parameter.name ; 
  var id = e.parameter.id ;
  var temperature = e.parameter.temperature;
  var humidity = e.parameter.humidity;

  sheet.appendRow([name,id,temperature,humidity]);
}

Jika anda perhatikan, terdapat 4 Variable yang mewakili 4 data yang bakal kita hantar ke Google Sheets.

name, id, temperature, humidity

Setiap variable ini perlu wujud 3 kali dalam Function ini.

Berikut di bawah, adalah CONTOH sahaja, jika anda mempunyai tambahan variable lain, hanya untuk rujukan. Pastikan anda teliti dan perhatikan struktur perletakan setiap variable di 3 lokasi.

function addData(e,sheet) {
  var name = e.parameter.name ; 
  var id = e.parameter.id ;
  var temperature1 = e.parameter.temperature1;
  var temperature2 = e.parameter.temperature2;
  var humidity1 = e.parameter.humidity1;
  var humidity2 = e.parameter.humidity2;

  sheet.appendRow([name,id,temperature1,temperature2,humidity1,humidity2]);
}

STEP 6: SAVE & AKTIFKAN / DEPLOY SCRIPT

Setelah selesai membina coding App Script, teruskan untuk DEPLOY (aktifkan).
DEPLOY > NEW DEPLOYMENT

Dalam Window “New Deployment” klik butang konfigurasi dan pilih Web App sebagai kaedah deployment.

Di bahagian “Who has access” klik pilihan ANYONE.
Kemudian klik butang DEPLOY.

Seterusnya, AUTHORIZE ACCESS diperlukan sebagai sekuriti.

Pilih Google Account yang akan digunakan bersama App Script ini.

Di window Google Verification, teruskan dengan menekan ADVANCED, dan teruskan ke “Go to <Nama App Script> (unsafe)”.

Langkah terakhir deployment adalah dengan menekan butang ALLOW.

Setelah App Script berjaya di deploy, Window ini akan terpapar.
2 maklumat akan diberikan, iaitu:

Deployment ID
Web App URL

Copy dan simpan kedua-dua maklumat dalam Notepad, bagi kegunaan coding untuk NodeMCU.

STEP 7: SEMAK KEWUJUDAN APP SCRIPT PROJECT DALAM DRIVE

Setelah berjaya deploy App Script, anda boleh semak kesemua App Script Project yang telah anda cipta . Rujuk rajah berikut.

STEP 1 : Tekan logo App Script
STEP 2 : Tekan “My Projects”

Kemudian anda dapat lihat senara App Script Project yang telah anda cipta.

STEP 8: CODING NODEMCU / ESP32

Coding untuk NodeMCU ESP8266

Perhatikan 2 Library (Untuk NodeMCU ESP8266) :

ESP8266WiFi.h
WiFiClientSecure.h

Kedua-dua library ini TIDAK PERLU DIDOWNLOAD kerana mereka TELAH DI-INSTALL semasa Library Board NodeMCU di tambahkan dalam Arduino IDE.

Berikut adalah Library untuk ESP32:

WiFi.h
WiFiClientSecure.h

Juga, library TELAH DI-INSTALL semasa penambahan Board Library ESP32.

//Code Program Prepared by matgyver.my
//June 2022

//UNTUK ESP832
//#include <WiFi.h>
//#include <WiFiClientSecure.h>

//UNTUK NODEMCU ESP8266
#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>

const char* ssid = "SSID WiFi";                //tukar
const char* password = "Password Wifi";        //tukar
const char* host = "script.google.com";        
String SCRIPT_ID = "APP Script Deployment ID"; //tukar

WiFiClientSecure client;

void setup() {
  //SETUP SERIAL MONITOR COMMUNICATIONS
  Serial.begin(9600);

  //SETUP WIFI CONNECTION
  Serial.println();
  Serial.print("Connecting to WiFi ");
  Serial.println(ssid);

  //INITIALIZE WIFI RADIO
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  
  //CONNECT WIFI
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
    
}//END VOID SETUP

void loop() {
/*************SIMULASI SENSOR*****************/  
int temperature = random(25, 38); //simulasi temperature dengan random nombor 25-38
int humidity = random(55, 83);    //simulasi humidty dengan random nombor 55-83

/*************PENGHANTARAN DATA*****************/  
  //SET CONNECTION TO INSECURE HTTPS
  client.setInsecure();

  //CONNECT TO HOST SERVER
  Serial.print("Connecting to ");
  Serial.println(host);
  
  if (!client.connect(host, 443)) {      // HTTPS Connection pada Port 443
    Serial.println("Connection failed");
    return;                              //Stop Process jika connection failed
  }
  delay(10);

  //SET STRING UNTUK DATA
  String data1 = "Muhammad";
  String data2 = "767628-09-1999";
  String data3 = String(temperature); //convert temperature kepada format data String
  String data4 = String(humidity);    //convert humidity kepada format data String
  
  //KOMBINASI SEMUA DATA DALAM 1 STRING
  String data = "name="+data1+"&id="+data2+"&temperature="+data3+"&humidity="+data4;

  //SET STRING URL UNTUK REQUEST
  String url = "/macros/s/" + SCRIPT_ID + "/exec?" + data;
  Serial.print("Requesting URL: ");
  Serial.println(url);

  //HANTAR GET API Request
  client.print(String("GET ") + url + " HTTP/1.1\r\n" +
               "Host: " + host + "\r\n" +
               "User-Agent: ESP8266-Saya\r\n" +
               "Connection: close\r\n\r\n");

  Serial.println("Request sent");
  delay(300);

  //VARIABLE MENYIMPAN SERVER REPLY
  String fullResponse;
  String htmlResponse;
  String responseHeader;
  String responseBody;
  
  //WHILE/TUNGGU REPLY CONTENT DARI SERVER
  while (client.connected()) { //Response Code
    htmlResponse = client.readStringUntil('\r'); //Simpan Reply dalam String "line"
    break;
    //fullResponse = client.readString(); //Uncomment untuk lihat semua HTTP Response
  }//end While Client
  
  while (client.connected()) { //Response Header
    String responseHeader = client.readStringUntil('\n');
    if(responseHeader == "\r"){
      //Serial.println("Header diTerima");
      break;
    }
  }
  responseBody = client.readStringUntil('<'); //Response Body

  //CHECK/SEMAK RESPONSE CODE
  if(htmlResponse == "HTTP/1.1 200 OK"){
    Serial.println("Request diTerima Server");
  }
  else{
    Serial.println("Request ERROR");
  }

  client.stop(); //DISCONNECT HTTPS connection (Wajib Disconnect)

//DELAY UNTUK TIDAK FLOODING SERVER 
delay(10000);
  
}//END VOID LOOp

Struktur API GET yang di Hantar

GET /macros/s/<Script-ID>/exec?name=data1&id=data2.. HTTP/1.1

Host: script.google.com
User-Agent: ESP8266-Saya
Connection: close

Struktur URL untuk Request

https://script.google.com/macros/s/<Script-ID>/exec?name=Ahmad&id=9968&temperature=36.8

....exec?data1=nilai1&data2=nilai2&data3=nilai3&data4=nilai4

Struktur data adalah dalam URLselepas /exec? , dan anda perlu pastikan NAMA setiap data adalah MATCHING dengan nama data dalam App Script.

Sebagai contoh, yang kita gunakan untuk tutorial ini adalah:
name, id, temperature, humidity

5 SAAT – DATA INTERVAL

Seperti mana-mana applikasi IoT, penghantaran Data ke Google Sheets atau Cloud tidak boleh pada kadar yang tinggi.

Sebaiknya, hantarkan pada interval 5 saat atau 10 saat.

HASIL OUTPUT

Hasil di Google Sheets

Paparan Serial Monitor

CODING NODEMCU YANG LEBIH BERSIH

Penggunaan Sub-Routine Function

//Code Program Prepared by matgyver.my
//June 2022

//Untuk ESP32
//#include <WiFi.h>
//#include <WiFiClientSecure.h>

//Untuk NodeMCU ESP8266
#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>

const char* ssid = "SSID WiFi";                //tukar
const char* password = "Password WiFi";        //tukar
const char* host = "script.google.com";        
String SCRIPT_ID = "APP Script Deployment ID"; //tukar

WiFiClientSecure client;

int temperature; 
int humidity;  

void setup() {
  //SETUP SERIAL MONITOR COMMUNICATIONS
  Serial.begin(9600);

  //SETUP WIFI CONNECTION
  Serial.println();
  Serial.print("Connecting to WiFi ");
  Serial.println(ssid);

  //INITIALIZE WIFI RADIO
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  
  //CONNECT WIFI
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
    
}//END VOID SETUP

void loop() {
/*************SIMULASI SENSOR*****************/  
temperature = random(25, 38); //simulasi temperature dengan random nombor 25-38
humidity = random(55, 83);    //simulasi humidty dengan random nombor 55-83

/*************PENGHANTARAN DATA*****************/  
sendToGoogleSheets();  

//DELAY UNTUK TIDAK FLOODING SERVER 
delay(10000);
  
}//END VOID LOOP


void sendToGoogleSheets(){
  //SET CONNECTION TO INSECURE HTTPS
  client.setInsecure();

  //CONNECT TO HOST SERVER
  Serial.print("Connecting to ");
  Serial.println(host);
  
  if (!client.connect(host, 443)) {      // HTTPS Connection pada Port 443
    Serial.println("Connection failed");
    return;                              //Stop Process jika connection failed
  }
  delay(10);

  //SET STRING UNTUK DATA
  String data1 = "Muhammad";
  String data2 = "767628-09-1999";
  String data3 = String(temperature); //convert temperature kepada format data String
  String data4 = String(humidity);    //convert humidity kepada format data String
  
  //KOMBINASI SEMUA DATA DALAM 1 STRING
  String data = "name="+data1+"&id="+data2+"&temperature="+data3+"&humidity="+data4;

  //SET STRING URL UNTUK REQUEST
  String url = "/macros/s/" + SCRIPT_ID + "/exec?" + data;
  Serial.print("Requesting URL: ");
  Serial.println(url);

  //HANTAR GET API Request
  client.print(String("GET ") + url + " HTTP/1.1\r\n" +
               "Host: " + host + "\r\n" +
               "User-Agent: ESP8266-Saya\r\n" +
               "Connection: close\r\n\r\n");

  Serial.println("Request sent");
  delay(300);

  //VARIABLE MENYIMPAN SERVER REPLY
  String fullResponse;
  String htmlResponse;
  String responseHeader;
  String responseBody;
  
  //WHILE/TUNGGU REPLY CONTENT DARI SERVER
  while (client.connected()) { //Response Code
    htmlResponse = client.readStringUntil('\r'); //Simpan Reply dalam String "line"
    break;
    //fullResponse = client.readString(); //Uncomment untuk lihat semua HTTP Response
  }//end While Client
  
  while (client.connected()) { //Response Header
    String responseHeader = client.readStringUntil('\n');
    if(responseHeader == "\r"){
      //Serial.println("Header diTerima");
      break;
    }
  }
  responseBody = client.readStringUntil('<'); //Response Body

  //CHECK/SEMAK RESPONSE CODE
  if(htmlResponse == "HTTP/1.1 200 OK"){
    Serial.println("Request diTerima Server");
  }
  else{
    Serial.println("Request ERROR");
  }

  client.stop();//DISCONNECT HTTPS Connection
  
}//END sendToGoogleSheets

PENAMBAHAN TIMESTAMP – Untuk Maklumat Masa Data

TimeStamp atau maklumat masa data diperlukan untuk Data Acquisition yang tepat.

Terdapat beberapa kaedah mendapatkan maklumat masa untuk TimeStamp:

Menggunakan RTC Module sebagai Module yang menyimpan maklumat masa. Kemudian hantarkan masa tersebut dari NodeMCU.
Menggunakan Library Time NTP Server, di mana masa diambil dari Server NTP tanpa menggunakan RTC module, dan dihantar dari NodeMCU.
Menggunakan Google Server timeStamp yang boleh dipanggil terus dari AppScript.

Contoh berikut akan menggunakan kaedah ke-3, iaitu kita memanggil maklumat TimeStamp dari AppsScript.

STEP #1: Ubah Google Sheets

Tambahkan satu lagi COLUMN disebelah kiri Sheets untuk maklumat timeStamp dan berikan nama timeStamp.

STEP #2: Ubah Apps Script

Dalam script di bawah, hanya terdapat perubahan kecil sahaja di bahagian Function addData().

function doGet(e) { 

var ss = SpreadsheetApp.openByUrl("URL Google Sheets Anda");
var sheet = ss.getSheetByName("Sheet1");

  addData(e,sheet);
}

function doPost(e) { 
  var ss = SpreadsheetApp.openByUrl("URL Google Sheets Anda");
  var sheet = ss.getSheetByName("Sheet1"); 
  
  addData(e,sheet);
}


function addData(e,sheet) {
  var name = e.parameter.name ; 
  var id = e.parameter.id ;
  var temperature = e.parameter.temperature;
  var humidity = e.parameter.humidity;

  var timeStamp = Utilities.formatDate(new Date(), "Asia/Kuala_Lumpur", "yyyy-MM-dd HH:mm:ss"); 
  // "yyyy-MM-dd'T'HH:mm:ss'Z'"

  sheet.appendRow([timeStamp,name,id,temperature,humidity]);
}

timeStamp dalam code ini adalah untuk region Malaysia/Kuala Lumpur yang diambil dari server.

STEP #3: Deploy Semula Apps Script

Selepas RE-DEPLOY, ID Template Apps Script yang baru akan di jana.
Rujuk penerangan sebelum ini.

STEP #4: Coding NodeMCU

Gantikan Apps Script ID di Coding NodeMCU dengan ID yang baru.
Dan modifikasi selesai untuk TimeStamp.

CODING UNTUK ESP32

Bagi ESP32 pula, coding adalah sama.
Perubahan hanyalah pada penukaran #include <ESP8266WiFi.h> kepada #include <WiFi.h>

Coding yang lain adalah sama.

//Code Program Prepared by matgyver.my
//June 2022

#include <WiFi.h>
#include <WiFiClientSecure.h>

const char* ssid = "SSID WiFi";                //tukar
const char* password = "Password WiFi";        //tukar
const char* host = "script.google.com";        
String SCRIPT_ID = "APP Script Deployment ID"; //tukar

WiFiClientSecure client;

int temperature; 
int humidity;  

void setup() {
  //SETUP SERIAL MONITOR COMMUNICATIONS
  Serial.begin(9600);

  //SETUP WIFI CONNECTION
  Serial.println();
  Serial.print("Connecting to WiFi ");
  Serial.println(ssid);

  //INITIALIZE WIFI RADIO
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  
  //CONNECT WIFI
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
    
}//END VOID SETUP

void loop() {
/*************SIMULASI SENSOR*****************/  
temperature = random(25, 38); //simulasi temperature dengan random nombor 25-38
humidity = random(55, 83);    //simulasi humidty dengan random nombor 55-83

/*************PENGHANTARAN DATA*****************/  
sendToGoogleSheets();  

//DELAY UNTUK TIDAK FLOODING SERVER 
delay(10000);
  
}//END VOID LOOP


void sendToGoogleSheets(){
  //SET CONNECTION TO INSECURE HTTPS
  client.setInsecure();

  //CONNECT TO HOST SERVER
  Serial.print("Connecting to ");
  Serial.println(host);
  
  if (!client.connect(host, 443)) {      // HTTPS Connection pada Port 443
    Serial.println("Connection failed");
    return;                              //Stop Process jika connection failed
  }

  //SET STRING UNTUK DATA
  String data1 = "Muhammad";
  String data2 = "767628-09-1999";
  String data3 = String(temperature); //convert temperature kepada format data String
  String data4 = String(humidity);    //convert humidity kepada format data String
  
  //KOMBINASI SEMUA DATA DALAM 1 STRING
  String data = "name="+data1+"&id="+data2+"&temperature="+data3+"&humidity="+data4;

  //SET STRING URL UNTUK REQUEST
  String url = "/macros/s/" + SCRIPT_ID + "/exec?" + data;
  Serial.print("Requesting URL: ");
  Serial.println(url);

  //HANTAR GET API Request
  client.print(String("GET ") + url + " HTTP/1.1\r\n" +
               "Host: " + host + "\r\n" +
               "User-Agent: ESP8266-Saya\r\n" +
               "Connection: close\r\n\r\n");

  Serial.println("Request sent");
  delay(300);

  //VARIABLE MENYIMPAN SERVER REPLY
  String fullResponse;
  String htmlResponse;
  String responseHeader;
  String responseBody;
  
  //WHILE/TUNGGU REPLY CONTENT DARI SERVER
  while (client.connected()) { //Response Code
    htmlResponse = client.readStringUntil('\r'); //Simpan Reply dalam String "line"
    break;
    //fullResponse = client.readString(); //Uncomment untuk lihat semua HTTP Response
  }//end While Client
  
  while (client.connected()) { //Response Header
    String responseHeader = client.readStringUntil('\n');
    if(responseHeader == "\r"){
      //Serial.println("Header diTerima");
      break;
    }
  }
  responseBody = client.readStringUntil('<'); //Response Body

  //CHECK/SEMAK RESPONSE CODE
  if(htmlResponse == "HTTP/1.1 200 OK"){
    Serial.println("Request diTerima Server");
  }
  else{
    Serial.println("Request ERROR");
  }
}//END sendToGoogleSheets

Selamat Mencuba!

Jika anda menyukai artikel-artikel keluaran MatGyver.my, kami mohon jasa baik tuan/cikpuan untuk sumbangkan like pada page FaceBook kami,
dan juga Subscribe ke YouTube Channel kami !

Dapatkan Tips-tips DIY dan Arduino dari FB Page Kami.

Konten Video Tips DIY Hobby MatGyver Official Channel.

end

2 thoughts on “Hantar Data ke Google Sheets | NodeMCU/ESP32”

Nadzar Nad berkata:

Disember 2, 2022 at 4:57 pm

untuk step ke 7 tu, app skript project saya tak ada apa2 dah cari dekat my project dan all project pun tak ada apa2

LikeLike

Balas
1. Mat Gyver berkata:
  
  Disember 2, 2022 at 5:20 pm
  
  Untuk step 7, perlu open dulu App Script.
  +New > more > Google Apps Script
  
  Lepas dah launch Apps Script, baru pergi ke folder my projects
  
  LikeLike
  
  Balas