Cannot connect to google script cannot write data into google sheet

I following tutorials online about how to log data to Google Sheet. They all use DHT sensor but I'm using MPU6050 (accelerometer & gyroscope). My MCU is NodeMCU.

After succeeded connect to network, I cannot connect to google script. Or maybe it connected. but cannot write the data to the Google Sheet. The serial monitor shows errors like this:

connecting to script.google.com
requesting URL: /macros/s/<GAS_ID>/exec?Acceleration_X=0.8128314614&Rotation_X=-0.0671485439
request sent
headers received
esp8266/Arduino CI has failed
reply was:

DOCTYPE html

Sometimes the code under 'reply was' changed to: 202 or e24.

I suspect my code that caused the error is in this part:

void loop() 
{
  /*
  float h = mpu.readAccelerometer();
  float t = mpu.read();
  Serial.print("Temp = ");
  Serial.print(t);
  Serial.print(" HUM= ");
  Serial.println(h);
   it = (int) t;
   ih = (int) h;
  sendData(it, ih);
 
 delay(2000);
 */

 /* Get new sensor events with the readings */
  sensors_event_t a, g, temp;
  mpu.getEvent(&a, &g, &temp);

  /* Print out the values */
  Serial.print("Acceleration_X: ");
  Serial.print(a.acceleration.x);
  /*Serial.print(", Y: ");
  Serial.print(a.acceleration.y);
  Serial.print(", Z: ");
  Serial.print(a.acceleration.z);*/
  Serial.println(" m/s^2");

  Serial.print("Rotation_X: ");
  Serial.print(g.gyro.x);
  /*Serial.print(", Y: ");
  Serial.print(g.gyro.y);
  Serial.print(", Z: ");
  Serial.print(g.gyro.z);*/
  Serial.println(" rad/s");
   float it = a.acceleration.x;
   float ih = g.gyro.x;
   
 /* Serial.print("try it: ");
  Serial.print(it);
   //ih = (int) h;*/

  Serial.println("");
  sendData(it, ih);
  delay(500);
}

// Function for Send data into Google Spreadsheet
// void sendData(int accX, int gyroX) ----> original
void sendData(float accX, float gyroX)
{
  Serial.print("connecting to ");
  Serial.println(host);
  if (!client.connect(host, httpsPort)) {
    Serial.println("connection failed");
    return;
  }

/* if (client.verify(fingerprint, host)) {
  Serial.println("certificate matches");
  } else {
  Serial.println("certificate doesn't match");
  }*/
  
 String string_Acceleration_X =  String(accX, DEC); 
 String string_Rotation_X =  String(gyroX, DEC); 
 String url = "/macros/s/" + GAS_ID + "/exec?Acceleration_X=" + string_Acceleration_X + "&Rotation_X=" + string_Rotation_X;
  Serial.print("requesting URL: ");
  Serial.println(url);

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

  Serial.println("request sent");
  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.println("reply was:");
  Serial.println("==========");
  Serial.println(line);
  Serial.println("==========");
  Serial.println("closing connection");
} 

The java script for the Google Sheet works fine as I tried to write by keying in data in the Web App URL.

Thank you in advance.

What is the client object? From which http client library? Do you use HTTPSRedirect or some other library? Please also provide the original link/s to the tutorial/s that you had followed. Please provide the google script code too. BTW, you should probably hide the GAS_ID from being displayed in your post.

Try editing your GoogleScript to allow anyone to access. Arduino could not login to your Google account, hence it will send back to you a login page (DOCTYPE html) file. As long as you don't expose your GAS_ID, it should be relatively secure, and since it had been exposed, you are better off creating a new deployment instead.

Hye Hadi, thank you for the reply. I attach the full code down below as I couldn't answer most of the questions. For accelerometer part, I take the code from Adafruit MPU6050 library > basic reading.


//-----------------------------------------------
// Author: Trieu Le
// Email: lethanhtrieuk36@gmail.com
// Publish date: 29-Oct-2017
// Description: This code for demonstration send data from ESP8266 into Google Spreadsheet
// Modifyed by Moz for Youtube changel logMaker360 for this video: https://youtu.be/fS0GeaOkNRw 24-02-2018
// update ssid, password and GAS_ID
//-----------------------------------------------
#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>
#include <Adafruit_MPU6050.h>
#include <Adafruit_Sensor.h>
#include <Wire.h>

Adafruit_MPU6050 mpu;

const char* ssid = "ssid";    
const char* password = "pw";     
 
const char* host = "script.google.com";
const int httpsPort = 443; //usually https port uses 443

// Use WiFiClientSecure class to create TLS connection, or create a wifiClientSecure object
WiFiClientSecure client;

String GAS_ID = "GAS service id"; 

void setup() 
{
  
  mpu.begin();  // sensor
/*  
  Serial.begin(115200); //Serial
  Serial.println();
*/
Serial.begin(115200);
  while (!Serial)
    delay(10); // will pause Zero, Leonardo, etc until serial console opens

  Serial.println("Adafruit MPU6050 test!");

  // Try to initialize!
  if (!mpu.begin()) {
    Serial.println("Failed to find MPU6050 chip");
    while (1) {
      delay(10);
    }
  }
  Serial.println("MPU6050 Found!");

   mpu.setAccelerometerRange(MPU6050_RANGE_2_G);
  Serial.print("Accelerometer range set to: ");
  switch (mpu.getAccelerometerRange()) {
  case MPU6050_RANGE_2_G: //RANGE_2_G: or 4_G: or 8_G: or 16_G:
    Serial.println("+-2G"); //println("+-2G"); or ("+-4G"); or ("+-8G"); or ("+-16G");
    break;
  }
  
  mpu.setGyroRange(MPU6050_RANGE_500_DEG);
  Serial.print("Gyro range set to: ");
  switch (mpu.getGyroRange()) {
  case MPU6050_RANGE_250_DEG: //RANGE_250_DEG: or 500_DEG: or 1000_DEG: or 2000_DEG:
    Serial.println("+- 250 deg/s"); //println("+- 250 deg/s"); or +- 500 deg/s or +- 1000 deg/s or +- 2000 deg/s
    break;
  }

  mpu.setFilterBandwidth(MPU6050_BAND_21_HZ);
  Serial.print("Filter bandwidth set to: ");
  switch (mpu.getFilterBandwidth()) {
  case MPU6050_BAND_260_HZ: //BAND_260_HZ: or 184_HZ: or 94_HZ: or 44_HZ: or 21_HZ:
    Serial.println("260 Hz"); //println("260 Hz"); or 184 Hz or 94 Hz or 44 Hz or 21 Hz or 5 Hz
    break;
  }

   Serial.println("");
  delay(100);
  
  //connecting to internet
  Serial.print("connecting to ");
  Serial.println(ssid);
  WiFi.mode(WIFI_STA);
  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.setInsecure();
 void loop() 
{
 /* Get new sensor events with the readings */
  sensors_event_t a, g, temp;
  mpu.getEvent(&a, &g, &temp);

  /* Print out the values */
  Serial.print("Acceleration_X: "); //try with x-axis only first
  Serial.print(a.acceleration.x);
  /*Serial.print(", Y: ");
  Serial.print(a.acceleration.y);
  Serial.print(", Z: ");
  Serial.print(a.acceleration.z);*/
  Serial.println(" m/s^2");

  Serial.print("Rotation_X: ");
  Serial.print(g.gyro.x);
  /*Serial.print(", Y: ");
  Serial.print(g.gyro.y);
  Serial.print(", Z: ");
  Serial.print(g.gyro.z);*/
  Serial.println(" rad/s");
   float it = a.acceleration.x;
   float ih = g.gyro.x;
   
 /* Serial.print("try it: ");
  Serial.print(it);
   //ih = (int) h;*/

  Serial.println("");
 { sendData(it, ih);
  delay(500);


// Function for Send data into Google Spreadsheet
// void sendData(int accX, int gyroX) ----> original
void sendData(float accX, float gyroX)}

  Serial.print("connecting to ");
  Serial.println(host);
  if (!client.connect(host, httpsPort)) {
    Serial.println("connection failed");
    return;
  }

 String string_Acceleration_X =  String(accX, DEC); 
 String string_Rotation_X =  String(gyroX, DEC); 
 //String url = "/macros/s/" + GAS_ID + "/exec?Acceleration_X=" + string_Acceleration_X + "&Rotation_X=" + string_Rotation_X;
  Serial.print("requesting URL: ");
 //Serial.println(url);
 
client. Print(String("GET ") + url + " HTTPS/1.1\r\n" +
         "Host: " + host + "\r\n" +
         "User-Agent: BuildFailureDetectorESP8266\r\n" +
         "Connection: close\r\n\r\n");

  Serial.println("request sent");
  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.println("reply was:");
  Serial.println("==========");
  Serial.println(line);
  Serial.println("==========");
  Serial.println("closing connection");
} 
}

I just started in Arduino and programming. So I just followed the tutorial video from YT

and here: Google Spreadsheet or Google Sheets with ESP8266 Nodemcu for Data Logging

and download the codes from the description of the video.

Meanwhile, the google script is as follows:

//-----------------------------------------------
// Author: Trieu Le
// Email: lethanhtrieuk36@gmail.com
// Publish date: 07-Oct-2015
// Description: This code for demonstration send data from ESP8266 into Google Spreadsheet
// GET request syntax:
// https://script.google.com/macros/s/<gscript id>/exec?header_here=data_here
// https://script.google.com/macros/s/Gas_ID/exec?Acceleration_X=3&Rotation_X=5
// Error in executes run script. document here :https://developers.google.com/apps-script/api/how-tos/execute
// Modifyed by Moz for Youtube changel logMaker360 for this video: https://youtu.be/fS0GeaOkNRw 24-02-2018


//-----------------------------------------------
/**
* Function doGet: Parse received data from GET request, 
  get and store data which is corresponding with header row in Google Spreadsheet
*/
function doGet(e) { 
  Logger.log( JSON.stringify(e) );  // view parameters
  var result = 'Ok'; // assume success
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = 'Spreadsheet ID'; 		// Spreadsheet ID
    var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();		// get Active sheet
    var newRow = sheet.getLastRow() + 1;						
    var rowData = [];
    rowData[0] = new Date(); 											// Timestamp in column A
    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 'Acceleration_X': //Parameter
          rowData[1] = value; //Value in column B
          result = 'Written on column B';
          break;
        case 'Rotation_X': //Parameter
          rowData[2] = value; //Value in column C
          result += ' ,Written on column C';
          break;  
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));
    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }
  // Return result of operation
  return ContentService.createTextOutput(result);
}
/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}
//-----------------------------------------------
// End of file
//-----------------------------------------------

/*
void setup() 
    


void loop() 
    */

Oh, thank you for informing me about the privacy. I will make new deployment.

But, yes. I did allow access to anyone.

        Serial.print("connecting to ");
        Serial.println(host);
        if (!client.connect(host, httpsPort))
        {
            Serial.println("connection failed");
            return;
        }

        String string_Acceleration_X = String(accX, DEC);
        String string_Rotation_X = String(gyroX, DEC);

        String request = "GET https://"+host+"/macros/s/" + GAS_ID + "/exec?Acceleration_X=" + string_Acceleration_X + "&Rotation_X=" + string_Rotation_X+ " HTTP/1.1";
        client.println(request);
        client.println("Host:"+host);
        client.println("Connection: close");
        client.println();

        Serial.println("request sent");
        while (client.connected())
        {
            String line = client.readStringUntil('\n');
            if (line == "\r")
            {
                Serial.println("headers received");
                break;
            }
        }

I have not tested this code, but based on the example, for secure and insecure connection has to be done this way.

Additionally, you could simplify the google script this way:

var sheet_id = "14...UpY";

function doGet(e) { 
  var ss = SpreadsheetApp.openById(sheet_id);
  var dataSheet = ss.getSheetByName("esp_data");
  var loggerSheet = ss.getSheetByName("logger");

  loggerSheet.appendRow([e]); //


  var result = 'Ok'; // assume success
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    if(!e.parameter.Acceleration_X || !e.parameter.Rotation_X){
      return ContentService.createTextOutput("Unsupported query!");
    }
    loggerSheet.appendRow([e]);
    dataSheet.appendRow([
      new Date(),
      e.parameter.Acceleration_X,
      e.parameter.Rotation_X,
    ]);
  }
  // Return result of operation
  return ContentService.createTextOutput(result);
}

All you have to do is create two sheets on the google spreadsheet as follows:

Hi Hadi! Thank you so much for providing the codes.

Sorry for responding so long as I need some time to understand the codes.

So, I tried to merge your Arduino codes into mine like follows:

// Function for Send data into Google Spreadsheet
// void sendData(int accX, int gyroX) ----> original
  void sendData(float accX, float gyroX){

  Serial.print("connecting to ");
  Serial.println(host);
  if (!client.connect(host, httpsPort)) {
    Serial.println("connection failed");
    return;
  }

 String string_Acceleration_X =  String(accX, DEC); 
 String string_Rotation_X =  String(gyroX, DEC); 

 String request = "GET https://" + host + "/macros/s/" + GAS_ID + "/exec?Acceleration_X=" + string_Acceleration_X + "&Rotation_X=" + string_Rotation_X+ " HTTP/1.1";
        client.println(request);
        client.println("Host:"+host);
        client.println("Connection: close");
        client.println();

        Serial.println("request sent");
        while (client.connected())
        {
            String line = client.readStringUntil('\n');
            if (line == "\r")
            {
                Serial.println("headers received");
                break;
            }
        }

And it came out with an error:

invalid operands of types 'const char [13]' and 'const char*' to binary 'operator+'

I still troubleshoot by myself, but hope to convey this to you to hear your opinions and more suggestions too. Thank you =)

Try with this code:

//  String request = "GET https://" + host + "/macros/s/" + GAS_ID + "/exec?Acceleration_X=" + string_Acceleration_X + "&Rotation_X=" + string_Rotation_X+ " HTTP/1.1";
  client.print("GET https://");
  client.print(host);
  client.print("/macros/s/");
  client.print(GAS_ID);
  client.print("/exec?Acceleration_X=");
  client.print(accX);
  client.print("&Rotation_X=");
  client.print(gyroX);
  client.println(" HTTP/1.1");
  
  client.print("Host:");
  client.println(host);
  client.println("Connection: close");
  client.println();

Hi again Mr. Hadi! and Assalammualaikum.

I think there must be more underlying issues in this project.

it goes back to the main error which is FAILED TO CONNECT TO GOOGLE SCRIPT. Error in serial monitor, Refer below:

connecting to script.google.com
Adafruit MPU6050 test!
MPU6050 Found!
Accelerometer range set to: +-2G
Gyro range set to: Filter bandwidth set to: 
connecting to MyWifi
.....
WiFi connected
IP address: 
192.XXX.XXX.XXX
Acceleration_X: -0.07 m/s^2
Rotation_X: -0.07 rad/s

connecting to script.google.com
connection failed
Acceleration_X: -0.06 m/s^2
Rotation_X: -0.07 rad/s

connecting to script.google.com
connection failed
Acceleration_X: -0.05 m/s^2
Rotation_X: -0.07 rad/s


Side Note:: I am sorry. I did not notice that my reply has not yet been posted.

So, the problem is still cannot be solved.

Please provide the full code.

Sure, Dr. HADI! I will provide as soon ai reach my room.

I've tried the following simulation and its works: ESP32 - WiFiClientSecure - Wokwi Arduino and ESP32 Simulator
The core of the code is this:

float rotation = 60.0;
float acceleration = 0.7;

void setup() {
  ...

  client.setInsecure();
}

void loop() {
  ...

  client.stop();
  if(client.connect("script.google.com",443)){
    Serial.println("Connection successful");
    client.print("GET https://script.google.com/macros/s/AKfycbwtMpomiZSdIk3N3ubk-SdPF6mWrM7xeCDRDmQFWj1H1HFmwCb_5k-d2VbwRBs47C5");
    client.print("/exec?Acceleration_X=");
    client.print(rotation);
    client.print("&Rotation_X=");
    client.print(acceleration);
    client.println(" HTTP/1.1");
    
    client.print("Host:");
    client.println("script.google.com");
    client.println("Connection: close");
    client.println();
    while (client.connected()) {
      String line = client.readStringUntil('\n');
      if (line == "\r") {
        break;
      }
    }
    String line = client.readStringUntil('\n');
    Serial.println(line);
  }
  else{
    Serial.println("Connection failed");
  }
  delay(2000);
  rotation = rotation +0.1;
  acceleration = acceleration+0.01;
  Serial.println("Next ...");
}

You have to provide a valid GAS_ID of course.

Dr. Hadi. Noted. Hence, the codes are sent to your inbox to avoid revealing my GAS_ID to public.

Thank you Dr. I will get back to you really soon!

You forgot to put

void setup() 
{
  ...
  client.setInsecure();
}

You'll get connection fail if you don't specify the client.setInsecure() at the end of the setup code.

1 Like

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.