Sending data to Google Sheets with an Arduino UNO w/ESP32 breakout board

I am very new to working with Arduino projects in general. I was oringally following this project, but I could not get the HTTPClient libary to work, so instead I am using the ArduinoHttpClient library, but it doesn't have the "strict follow redirects" option.
I think I am having trouble following the redirect to get the google script to run. I tried to read the location header but readHeaderValue() comes up with nothing for the Location header. Here is the code and thanks for any help you can provide:

#include <ArduinoHttpClient.h>
#include <SPI.h>
#include <WiFiNINA.h>
#include "arduino_secrets.h"
///////please enter your sensitive data in the Secret tab/arduino_secrets.h
char ssid[] = SECRET_SSID;  // your network SSID (name)
char pass[] = SECRET_PASS;  // your network password (use for WPA, or use as key for WEP)
int keyIndex = 0;           // your network key Index number (needed only for WEP)
int count = 0;
int status = WL_IDLE_STATUS;
String location;
// if you don't want to use DNS (and reduce your sketch size)
// use the numeric IP instead of the name for the server:
//IPAddress server(74,125,232,128);  // numeric IP for Google (no DNS)

char server[] = "www.google.com";
String GOOGLE_SCRIPT_ID = "AKfycbxar4l-GbQ5oDQesS__1nI-kgEQLEmEaRlQbViZbCezmDAQO8c8dgBhqyWiqt2PkxJu";  // name address for adafruit test
char address[] = "script.google.com";
char path[] = "";
int port = 443;
// Initialize the Ethernet client library
// with the IP address and port of the server
// that you want to connect to (port 80 is default for HTTP):
WiFiSSLClient wifi;
HttpClient client = HttpClient(wifi, address, port);


void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(9600);
  while (!Serial) {
    ;  // wait for serial port to connect. Needed for native USB port only
  }

  // check for the WiFi module:
  if (WiFi.status() == WL_NO_MODULE) {
    Serial.println("Communication with WiFi module failed!");
    // don't continue
    while (true)
      ;
  }

  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
    Serial.println("Please upgrade the firmware");
  }

  // attempt to connect to WiFi network:
  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network. Change this line if using open or WEP network:
    status = WiFi.begin(ssid, pass);

    // wait 10 seconds for connection:
    delay(10000);
  }
  Serial.println("Connected to WiFi");
  printWiFiStatus();
}

void loop() {
  // if there are incoming bytes available
  // from the server, read them and print them:
  if (status == WL_CONNECTED) {

    Serial.println("\nMaking get request...");
    // if you get a connection, report back via serial:
    client = HttpClient(wifi, address, port);
    String urlFinal = "/macros/s/" + GOOGLE_SCRIPT_ID + "/exec";
    Serial.println(urlFinal);
    client.get(urlFinal);
    int statusCode = client.responseStatusCode();
    if (statusCode == 302 || statusCode == 303) {
      String location;
      Serial.println(" Redirection");
      while (client.headerAvailable()) {       
        Serial.println(client.readHeaderName());
        
        Serial.println(client.readHeaderValue());

        if (client.readHeaderName() == "Location") {
          location = client.readHeaderValue();
          Serial.println(location);
          break;
        }
      }
      if (location.length()) {
        uint16_t path_begin = location.substring(8).indexOf('/');
        String server = location.substring(8, path_begin + 8);
        String path = location.substring(path_begin + 8);
        client = HttpClient(wifi, server, port);
        client.get(path);
        statusCode = client.responseStatusCode();
        Serial.print("Status code: after Redirect is: ");
        Serial.println(statusCode);
      }
    }

    String response = client.responseBody();
    Serial.print("Status code: ");
    Serial.println(statusCode);
    Serial.print("Response: ");
    Serial.println(response);
  }

  // if the server's disconnected, stop the client:
  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting from server.");
    client.stop();

    // do nothing forevermore:
    while (true)
      ;
  }
}

What is the final purpose of having the data stored?
After the data has been stored what are you doing with the data then?
Depending on this final purpose / data processing different options can be suggested.
Some of these options might be easier to use.
Some examples:

  • sending the data to thingSpeak
  • sending the data to your local PC as an UDP-message and store the messages as a *.CSV-file

which option fits best to your project highly depends on what you want to do.

best regards Stefan

Thanks for responding so quickly. The final purpose is to have the data in a spreadsheet where it is shared with others, who may then export as a a CSV or just generate some charts in sheets itself, depending on their needs.

-Stephen

unprecise description.

You are not telling if you need this data online = available through internet or not.

A local solution with sending UDP-messages to a computer that is connected to the same network as the ESP32 is this.

If you want better support. Post better = much more detailed information.

// start of macros dbg and dbgi
#define dbg(myFixedText, variableName) \
  Serial.print( F(#myFixedText " "  #variableName"=") ); \
  Serial.println(variableName);
// usage: dbg("1:my fixed text",myVariable);
// myVariable can be any variable or expression that is defined in scope

#define dbgi(myFixedText, variableName,timeInterval) \
  do { \
    static unsigned long intervalStartTime; \
    if ( millis() - intervalStartTime >= timeInterval ){ \
      intervalStartTime = millis(); \
      Serial.print( F(#myFixedText " "  #variableName"=") ); \
      Serial.println(variableName); \
    } \
  } while (false);
// end of macros dbg and dbgi


// I wrote some basic documentation about receiving the UDP-messages with python at the end of the file
#include <WiFi.h>
#include <SafeString.h>

#define MaxMsgLength 1024
createSafeString(UDP_Msg_SS,MaxMsgLength); 
uint8_t UDP_Msg_uint8_Buffer[MaxMsgLength + 1]; // for some strange reasons on ESP32 the udp.write-function needs an uint8_t-array

#define MaxHeaderLength 32 
createSafeString(Header_SS,MaxHeaderLength);

#define MaxTimeStampLength 64 
createSafeString(TimeStamp_SS,MaxTimeStampLength);


char HeaderDelimiter = '$'; // must match the delimiter defined in the python-code 


const char *ssid     = ""; 

const char *password = "";

IPAddress    remoteIP     (192, 168, 178, 160); // receiver-IP
unsigned int remotePort = 4210;                 // receiver port to listen on must match the portnumber the receiver is listening to

WiFiUDP Udp;

const char* ntpServer = "fritz.box";
const long  gmtOffset_sec = 0;
const int   daylightOffset_sec = 7200;

#include <time.h>                   // time() ctime()
time_t now;                         // this is the epoch
tm myTimeInfo;                      // the structure tm holds time information in a more convient way


boolean TimePeriodIsOver (unsigned long &expireTime, unsigned long TimePeriod) {
  unsigned long currentMillis  = millis();
  if ( currentMillis - expireTime >= TimePeriod )
  {
    expireTime = currentMillis; // set new expireTime
    return true;                // more time than TimePeriod) has elapsed since last time if-condition was true
  }
  else return false;            // not expired
}

const byte OnBoard_LED = 2;
int BlinkTime = 500;

void BlinkHeartBeatLED(int IO_Pin, int BlinkPeriod) {
  static unsigned long MyBlinkTimer;
  pinMode(IO_Pin, OUTPUT);

  if ( TimePeriodIsOver(MyBlinkTimer, BlinkPeriod) ) {
    digitalWrite(IO_Pin, !digitalRead(IO_Pin) );
  }
}

unsigned long TestTimer;
unsigned long UDP_SendTimer;

int myCounter = 0;
int HeaderNr  = 0;


void PrintFileNameDateTime()
{
  Serial.print("Code running comes from file ");
  Serial.println(__FILE__);
  Serial.print(" compiled ");
  Serial.print(__DATE__);
  Serial.println(__TIME__);
}

void showTime() {
  time(&now);                       // read the current time
  localtime_r(&now, &myTimeInfo);           // update the structure tm with the current time
  Serial.print("year:");
  Serial.print(myTimeInfo.tm_year + 1900);  // years since 1900
  Serial.print("\tmonth:");
  Serial.print(myTimeInfo.tm_mon + 1);      // January = 0 (!)
  Serial.print("\tday:");
  Serial.print(myTimeInfo.tm_mday);         // day of month
  Serial.print("\thour:");
  Serial.print(myTimeInfo.tm_hour);         // hours since midnight  0-23
  Serial.print("\tmin:");
  Serial.print(myTimeInfo.tm_min);          // minutes after the hour  0-59
  Serial.print("\tsec:");
  Serial.print(myTimeInfo.tm_sec);          // seconds after the minute  0-61*
  Serial.print("\twday");
  Serial.print(myTimeInfo.tm_wday);         // days since Sunday 0-6
  if (myTimeInfo.tm_isdst == 1)             // Daylight Saving Time flag
    Serial.print("\tDST");
  else
    Serial.print("\tstandard");
    
  Serial.println();
}


void StoreTimeStampIntoSS(SafeString& p_RefToSS, tm p_myTimeInfo) {

  time(&now);                               // read the current time
  localtime_r(&now, &myTimeInfo);           // update the structure tm with the current time

  //p_RefToSS = " ";
  p_RefToSS  = myTimeInfo.tm_year + 1900;
  p_RefToSS += ".";

  // month
  if (p_myTimeInfo.tm_mon + 1 < 10) {
    p_RefToSS += "0";
  }  
  p_RefToSS += myTimeInfo.tm_mon + 1;

  p_RefToSS += ".";

  // day
  if (p_myTimeInfo.tm_mday + 1 < 10) {
    p_RefToSS += "0";
  }    
  p_RefToSS += myTimeInfo.tm_mday;

  p_RefToSS += "; ";

  // hour
  if (p_myTimeInfo.tm_hour < 10) {
    p_RefToSS += "0";
  }    
  p_RefToSS += myTimeInfo.tm_hour;
  p_RefToSS += ":";

  // minute
  if (p_myTimeInfo.tm_min < 10) {
    p_RefToSS += "0";
  }    
  p_RefToSS += myTimeInfo.tm_min;
  
  p_RefToSS += ":";

  // second
  if (p_myTimeInfo.tm_sec < 10) {
    p_RefToSS += "0";
  }    
  p_RefToSS += myTimeInfo.tm_sec;
  //p_RefToSS += ",";  
}


void connectToWifi() {
  Serial.print("Connecting to "); 
  Serial.println(ssid);

  WiFi.persistent(false);
  WiFi.mode(WIFI_STA);

  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    BlinkHeartBeatLED(OnBoard_LED, 333);
    delay(332);
    Serial.print(".");
  }
  Serial.print("\n connected.");
  Serial.println(WiFi.localIP() );

}

void synchroniseWith_NTP_Time() {
  Serial.print("configTime uses ntpServer ");
  Serial.println(ntpServer);
  configTime(gmtOffset_sec, daylightOffset_sec, ntpServer);
  Serial.print("synchronising time");
  
  while (myTimeInfo.tm_year + 1900 < 2000 ) {
    time(&now);                       // read the current time
    localtime_r(&now, &myTimeInfo);
    BlinkHeartBeatLED(OnBoard_LED, 100);
    delay(100);
    Serial.print(".");
  }
  Serial.print("\n time synchronsized \n");
  showTime();    
}

void setup() {
  Serial.begin(115200);
  Serial.println("\n Setup-Start \n");
  PrintFileNameDateTime();
  Serial.print("InitSensor() done \n");
  
  connectToWifi();
  synchroniseWith_NTP_Time();
  Header_SS = "Header"; 
}

void PrintMsg() {
  Serial.print("UDP_Msg_SS #");
  Serial.print(UDP_Msg_SS);
  Serial.println("#");
}

void loop() {
  BlinkHeartBeatLED(OnBoard_LED, BlinkTime);

  if (TimePeriodIsOver(UDP_SendTimer, 2000) ) {
    Serial.print("Send Message to #");
    Serial.print(remoteIP);
    Serial.print(":");
    Serial.println(remotePort);

    UDP_Msg_SS = "";

    UDP_Msg_SS = Header_SS;
    UDP_Msg_SS += HeaderDelimiter;

    StoreTimeStampIntoSS(TimeStamp_SS,myTimeInfo);
    UDP_Msg_SS += TimeStamp_SS;

    UDP_Msg_SS += ",my Testdata1,";
    UDP_Msg_SS += 123;
    UDP_Msg_SS += ",my Testdata2,";

    UDP_Msg_SS += 789;
    UDP_Msg_SS += ",";
    
    UDP_Msg_SS += myCounter++; 

    dbg("Send UDP_Msg #",UDP_Msg_SS);
    dbg("length:",UDP_Msg_SS.length());
    Udp.beginPacket(remoteIP, remotePort);
    Udp.write((const uint8_t*)UDP_Msg_SS.c_str(), UDP_Msg_SS.length() );  
    Udp.endPacket();
  }    
}

/*

This is a democode that demonstrates how to send TCP/UDP-messages with a timestamp 
The time is synchronized using a NTP-server. Most local routers like Fritz!Box can be used as the NTP-timer-server

The message has a userdefinable header which could be used for identifying the sender on the recieverside
There is a user-definable Header-delimiter that can be used to identify which characters of the
UDP-message belong to the header and which to the userdata

The code makes use of the SafeString-library. SafeStrings don't cause memory-problems like datatype "Strings"
and are easier to use than arrays of char. Example adding an integer to a SafeString-variable is as easy as
MySafeString = myInteger;

The userdata has commas between each data so you can import the textfile 
into table-calculation-software or databases as CSV-file comma separated values

the code has some additional useful functions as there are
- PrintFileNameDateTime() printing the path and filename of sourcecode file this program was generated with

- boolean TimePeriodIsOver  a non-blocking timing-function based on millis which is suitable for 
  timed execution in a regular manner (repeat every n milliseconds)

- BlinkHeartBeatLED() blinks the onboard-LED of ESP32 nodeMCU-boards. Gives visual feedback if the code is running  

The lines of code are grouped by functionality into several functions
The functions name says what the function does

I use this code for easy datalogging on a computer with a python-code that acts as the UDP-listener for 
incoming messages. Inside your ESP32-code you have to adjust the IP-adress to the receiver 
and the portnumber must be the same on sender (ESP32) and receiver-side 

In the python-code The header is used to create a file with the header as filename and extension ".txt" or if file 
is already existant to append the actual received UDP-message at the end of the file.

here is the python-code that does this. I tested it with python 3.9.2 for windows
#Start of python-code
# very simple and short upd-receiver based on python-code I found here
# https://www.studytonight.com/network-programming-in-python/working-with-udp-sockets#

import socket

sock = socket.socket(socket.AF_INET,socket.SOCK_DGRAM)      # For UDP

udp_host = socket.gethostname()            # Host IP
udp_port = 4210                     # specified port to connect

sock.bind((udp_host,udp_port))
print ("Waiting for client...")

HeaderDelimitChar = "$"

while True:
  data,addr = sock.recvfrom(1024)         #receive data from client
  print("data #",data,"#")
  Msg = data.decode('utf-8')
  print ("Received Message: #",Msg,"# from",addr)
  EndOfHeader = Msg.find(HeaderDelimitChar)
  HeaderBytes = Msg[0:EndOfHeader]
  FileName = HeaderBytes + ".txt"
  print("Filename #",FileName,"#")
  myFile = open(FileName, "a+")
  EndOfStr = data.find(0)
  MsgToWrite = Msg[EndOfHeader + 1 :1024] + '\r'
  myFile.write(MsgToWrite);
  myFile.close()
  print ("Data #",MsgToWrite,"#")

#End of python-code

For learning more about python just google with keyword "python" "your keyword of interest"  
 */

Ok. Online means people will access it in the typical way through a web browser by going to Google. In my original post, I thought I posted a link to the project I was trying to copy, which is sending data from a sensor hooked up to the Arduino to a Google sheet stored not locally, but on their servers. I set up a script at Google so that when I go to this URL:
https://script.google.com/macros/s/AKfycbxar4l-GbQ5oDQesS__1nI-kgEQLEmEaRlQbViZbCezmDAQO8c8dgBhqyWiqt2PkxJu/exec
in a web browser, it will add data to the spreadsheet.

I agree that more detail is better, but I will need help in providing that detail, as I am very new to this. Thanks for any help you can provide. If you find my questions too annoyingly imprecise, please feel to disregard this post.

Thanks,

Stephen

1 Like

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