Mengirim Data Sensor dari NodeMcu ESP8266 ke Spreeadshets atau Google Sheets

 Mengirim Data Sensor dari NodeMcu ESP8266 ke Spreeadshets atau Google Sheets

    Upload data sensor apa pun ke Google Sheets melalui NodeMCU tanpa menggunakan perangkat lunak atau aplikasi pihak ketiga.

Komponen dan Software Yang Perlu  Disiapkan

1. NodeMcu ESP8266/Wemos D1 mini. klik untuk  beli Disini
2. Potensio atau Sensor
3. Software Arduino ide
4. Spreeadsheets atau Google Sheet

Langkah - Langkah

Langkah 1: Siapkan NodeMCU untuk Arduino IDE  

Catatan: Untuk menyiapkan Arduino IDE untuk NodeMCU, kunjungi di sini

Langkah 2: Menyiapkan Google Sheets

Langkah 2.1. 

    Buka Google Drive dan Buat Spreadsheet baru dan beri nama, setelah itu berikan bidang dengan parameter yang ingin Anda tentukan. atau bisa kunjungi Disini

Langkah 2.2.  

    Copy ID dari spresadsheets

Langkah 2.3.

    Sekarang masuk ke Tools-Script Editor:

Langkah 2.4.

    Script Editor Spreeadsheet

Langkah 2.5. 

    Masukan Scrip Code berikut ke Script editor Spreeadsheet

function doGet(e) { 
  Logger.log( JSON.stringify(e) );
  var result = 'Ok';
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = '1-bCnjh7zvTUR3ea6BWkiGObR80w2FjQD1W-EU2pQtDs'; 	// Spreadsheet ID
    var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;						
    var rowData = [];
    var Curr_Date = new Date();
    rowData[0] = Curr_Date; // Date in column A
    var Curr_Time = Utilities.formatDate(Curr_Date, "Asia/Jakarta", 'HH:mm:ss');
    rowData[1] = Curr_Time; // Time in column B
    for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'nilai':
          rowData[2] = value; // Temperature in column C
          result = 'Nilai Written on column C'; 
          break;
      }
    }
    Logger.log(JSON.stringify(rowData));
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }
  return ContentService.createTextOutput(result);
}
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

Catatan Ganti var sheet_id dengan ID Spreadsheet Anda 

Langkah 2.6.

    Buka Publikasikan - Terapkan sebagai Aplikasi Web:

Langkah 2.7.

    Sekarang ubah jenis akses ke siapa saja, bahkan anonim, dan terapkan:

Langkah 2.8.

    Buka Izin Ulasan:

Langkah 2.9.

    Pilih Lanjutan:

Langkah 2.10.

    Pilih Pergi ke (nama file) dan kemudian izinkan:

Langkah 2.11.

    Salin URL aplikasi web saat ini dan klik OK:

Langkah 2.12.

    Cek apakah script code tersebut dapat berjalan

• Salin URL pada langkah 2.11.(https://script.google.com/macros/s/AKfycbyMc8GEAarXuRrTteyV4wOkS8mpJ3KZwlTbm2mkpn4patApumQ/exec)
• kemudian tambah variabel dan nilai yang akan diisikan di kolom C(?nilai=30)
• Gabung URL dan nilai yang akan ditampilkan
• https://script.google.com/macros/s/AKfycbyMc8GEAarXuRrTteyV4wOkS8mpJ3KZwlTbm2mkpn4patApumQ/exec?nilai=30   

Langkah 3: Arduino Kode

    Dibawah ini adalah source code nya

/*****************************************************************************
 *                                                                           *
 * Source Code By : Lukman Amirul Fatah ( dengan bantuan kpas kono kene :v)  *
 * Project        : Kirim data dari Nodemcu ESP8266 ke Spreadsheets          *
 *                                                                           *
 ****************************************************************************/

//----------------------------------------Include the NodeMCU ESP8266 Library
//----------------------------------------see here: https://www.youtube.com/watch?v=8jMr94B8iN0 to add NodeMCU ESP12E ESP8266 library and board (ESP8266 Core SDK)
#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>
//----------------------------------------


const int pot = A0;

#define ON_Board_LED 2  //--> Defining an On Board LED, used for indicators when the process of connecting to a wifi router

//----------------------------------------SSID dan Password wifi mu gan.
const char* ssid = "lkmn"; //--> Nama Wifi / SSID.
const char* password = "12345432"; //-->  Password wifi .
//----------------------------------------

//----------------------------------------Host & httpsPort
const char* host = "script.google.com";
const int httpsPort = 443;
//----------------------------------------

WiFiClientSecure client; //--> Create a WiFiClientSecure object.

String GAS_ID = "AKfycbyMc8GEAarXuRrTteyV4wOkS8mpJ3KZwlTbm2mkpn4patApumQ"; //--> spreadsheet script ID

//============================================================================== void setup
void setup() {
  // put your setup code here, to run once:
  Serial.begin(115200);
  delay(500);

  
  WiFi.begin(ssid, password); //--> Connect to your WiFi router
  Serial.println("");
    
  pinMode(ON_Board_LED,OUTPUT); //--> On Board LED port Direction output
  digitalWrite(ON_Board_LED, HIGH); //--> 

  //----------------------------------------Wait for connection
  Serial.print("Connecting");
  while (WiFi.status() != WL_CONNECTED) {
    Serial.print(".");
    //----------------------------------------Make the On Board Flashing LED on the process of connecting to the wifi router.
    digitalWrite(ON_Board_LED, LOW);
    delay(250);
    digitalWrite(ON_Board_LED, HIGH);
    delay(250);
    //----------------------------------------
  }
  //----------------------------------------
  digitalWrite(ON_Board_LED, HIGH); //--> Turn off the On Board LED when it is connected to the wifi router.
  //----------------------------------------If successfully connected to the wifi router, the IP Address that will be visited is displayed in the serial monitor
  Serial.println("");
  Serial.print("Successfully connected to : ");
  Serial.println(ssid);
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());
  Serial.println();
  //----------------------------------------

  client.setInsecure();
}
//==============================================================================
//============================================================================== void loop
void loop() {

  unsigned int nilai = 0;
  nilai = analogRead(pot);
  
  
    sendData(nilai);
 
  
  
}
//==============================================================================
//============================================================================== void sendData
// Fungsi  untuki Kirim data to Google Sheets
void sendData(unsigned int value) {
  Serial.println("==========");
  Serial.print("connecting to ");
  Serial.println(host);
  
  //----------------------------------------Connect ke Google host
  if (!client.connect(host, httpsPort)) {
    Serial.println("connection failed");
    return;
  }
  //----------------------------------------

  //----------------------------------------Proses dan kirim data  
  String string_nilai =  String(value, DEC); // fungsi DEC mengakhiri value terakhir  
   
  String url = "/macros/s/" + GAS_ID + "/exec?nilai=" + string_nilai; // variabel disamakan dengan yang di script google sheets
  Serial.print("requesting URL: ");
  Serial.println(url);

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

  Serial.println("request sent");
  //----------------------------------------

  //----------------------------------------Check data terkirim atau tidak 
  while (client.connected()) {
    String line = client.readStringUntil('\n');
    if (line == "\r") {
      Serial.println("headers received");
      break;
    }
  }
  String line = client.readStringUntil('\n');
  if (line.startsWith("{\"state\":\"success\"")) {
    Serial.println("esp8266/Arduino CI successfull!");
  } else {
    Serial.println("esp8266/Arduino CI has failed");
  }
  Serial.print("reply was : ");
  Serial.println(line);
  Serial.println("closing connection");
  Serial.println("==========");
  Serial.println();
  //----------------------------------------
} 
//==============================================================================

Catatan :  GAS_ID adalah ID spreeadsheet

Lebih Jelasnya Anda bisa Tonton Video Berikut :