• Skip to main content
  • Skip to primary sidebar

RNTLab.com

The Ultimate Shortcut to Learn Electronics and Programming with Open Source Hardware and Software

  • Courses
  • Forum
    • Forum
    • Ask Question
  • Shop
  • Account
  • Blog
  • Login

Creating Google Sheet of sensor data; by month

Q&A Forum › Category: ESP32 › Creating Google Sheet of sensor data; by month
0 Vote Up Vote Down
William Lucid asked 3 months ago

Following  “ESP32 Publish Sensor Readings to Google Sheets (ESP8266 Compatible)” tutorial; I see in the image for “adding a row to Google Sheet”  a new Google Sheet is created after 2000 rows.  Can a new Google Sheet be created before 2000 lines?

Project collects data every 15 minutes for 24 hours, then creates a new file for the next 24 hours at the end of the week; the seven days of files are deleted from the web server.  I would like to save files in cvs format to Google Sheets maintaining a year of files; by month for a year.

Any thoughts on determining the end of the month?  Project uses NTP time server.

Regards,

William

19 Answers
0 Vote Up Vote Down
William Lucid answered 3 months ago

Have resolved finding the number of days in a month using NTP server time.

Gist page for NTP and Days in month code…

Will be able to use conditional statement ; if (days == DATE) and it is Midnight call next month’s Google Sheet drive folder path name.

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 3 months ago

Hi.
That’s great.
Thanks for sharing your solution. And I’m sorry for taking so long to get back to you.
Regards,
Sara

0 Vote Up Vote Down
William Lucid answered 3 months ago

Hi Sara,

No worries, thank you.

Can the process of creating a new, Google sheet for each month be “automated?”

Thinking of creating a Google Sheet for each month; problem is how switch resource url in the applet.  Looks to me it uses a fixed single variable for path to Google Sheet.

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 3 months ago

I’m not sure.
It’s been a long time since I’ve worked with google sheets and the ESP32.
I’ve found this library: https://github.com/mobizt/ESP-Google-Sheet-Client
In the description, it mentions that it can create new spreadsheets.

I hope this helps.

Regards,
Sara

0 Vote Up Vote Down
William Lucid answered 3 months ago

Happy New Year Sara,
 
Thank you for the link to the mobizt/ESP-Google-Sheet-Client library!  Will see what it can do; sounds promising…
 
Regards,
William

0 Vote Up Vote Down
William Lucid answered 3 months ago

I could create 12 Google Sheets on my Google Drive; then in the sketch, pass a drive folder name –resource variable with the current month drive folder name..

Problem is in completing IFTT, Webhooks action field for drive folder path; I do not see a way to use a variable to pass drive folder name for a given month.

Any way to use a variable for drive folder path in completing drive folder path action field?

 

Regards,

William

0 Vote Up Vote Down
Sara Santos Staff answered 3 months ago

Hi.
You can pass the spreadsheet name on the path where you make the requests.

const char* resource = "/trigger/bme280_readings/with/key/nAZjOphL3d-ZO4N3k64-1A7gTlNSrxMJdmqy3";

But I’m not sure if you need to have that “ingredient” already created on IFTTT.
 
For all the things you want to do, I think it would be better to use the library I suggested before because it would allow more customization and other options to manipulate the spreadsheets.
 
Regards,
Sara

0 Vote Up Vote Down
William Lucid answered 3 months ago

Hi Sara,

Checking the tutorial I found:

bme280_readings is an Event Name

BME280_Readings is a Google Sheet Name.

I suspect the Google Sheet name is encoded in the unique API key.

The issue is being able to switch to a new Google Sheet filename and path; not in creating spreadsheet.   I do not think the “Webhooks” action “Add Row to Google Sheet” is able to accomplish this task

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 3 months ago

Yes, you’re right.
Take a look at this library https://github.com/mobizt/ESP-Google-Sheet-Client.
It seems to be able to accomplish the task.
Regards,
Sara

0 Vote Up Vote Down
William Lucid answered 3 months ago

Hi Sara,

Appreciate your follow-up;  I looked the library over you linked;  found a method of using Google scripting to accomplish the task with no third-party cloud services needed!  Here is the article that describes the method:

ESP32 Data Logging to Google Sheets with Google Scripts

Google script used to in test sending dat to Google Sheet:

var sheet_id = “removed for security”;
var sheet_name = “Weather1”;
function doGet(e){
var ss = SpreadsheetApp.openById(sheet_id);
var sheet = ss.getSheetByName(sheet_name);
var dtstamp = String(e.parameter.dtstamp);
var temp = Number(e.parameter.temp);
var heatindex = Number(e.parameter.heatindex);
var humidity = Number(e.parameter.humidity);
var dewpoint = Number(e.parameter.dewpoint);
var pressure = Number(e.parameter.pressure);
sheet.appendRow([dtstamp,temp,heatindex,humidity,dewpoint,pressure]);
}

The results the Google Script; web app URL in Brave Browser:

Test result sending web data to Goole Sheet

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 3 months ago

That’s great. Thanks for sharing.
Let me know if you need further help.

0 Vote Up Vote Down
William Lucid answered 3 months ago

Having an issue with logging data to Google Sheets; attaching Gist with googleSheets function that sends data from ESP32 sketch, Google Sheet Script, and a description of the problem at the bottom of the Gist in a comment.

I am able to send returned “urlFinal” from ESP32 sketch; sending “urlFinal” using web browser address field and pasting “urlFinal,” Google Sheets appends row successfully and populates.  Running “urlFinal” from ESP32 sketch fails to populate appended row.   Error message from this step is included in the Gist.

Gist of googleSheets function

Regards,
William

0 Vote Up Vote Down
William Lucid answered 3 months ago

Sara, you may mark this resolved!

BME280 ChatGPT6 –live data feed

Issue has been resolved!  Turns out the URL formatting of dtStamp (time) was incorrect HTTP does not like spaces in URL; use dtStamp.replace(“”, -) to format dtStamp.

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 3 months ago

Great.
I’m glad you were able to solve the issue.
I’ll mark this issue as resolved. If you need further help, you just need to open a new question in our forum.
Regards,
Sara

0 Vote Up Vote Down
William Lucid answered 2 months ago

FYI,
Ran into an issue of Http code 400 –malformed request or bad request; I had spaces in the url request; spaces are not permitted in url’s..   Used urlFinal.replace(” “, “%20”); to remove spaces.  This turned the Http code 400 into a successful Http code of 200 with data populating the Google Sheet of project.

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 2 months ago

That’s great!

0 Vote Up Vote Down
William Lucid answered 2 months ago

Update 01/22/2023:
Learned of ChatGPT; used this OpenAI research project to build a Google Sheet script to use data from ESP32, BME280 sensor creates a new Google Sheet every month for year and if appendRow produces an error, creates a new Google Shhet for the same month’

Gist of BME280 Chat6 Google Sheets script

Read more about ChatGPT

Regards,
William

0 Vote Up Vote Down
William Lucid answered 2 months ago

Sharing that BME280 ChatGPT6 Google Script woked as coded; at Midnight, January 31, 2023 script created new Febuary 2023 sheet.

Live feed data to BME280 ChatGPT6 GS script

Web app server offline 02/01/2023 @ 10:35 EST; moved to new Google sheet deployment:  BME280 ChatGPT features data logging greater than one year.

Live feed: BME280 ChatGPT greater than one year logging.

Added testForNextYear function to first Google Sheet App script that collects data from the web server of the project. Script will now keep creating Google Sheet for more than a year.

This is a Google Script that retrieves environmental data (datetime, temperature, heat index, humidity, dewpoint, pressure, and a difference value) from a web interface and appends it to a Google Spreadsheet. The sheet name is based on the current month and year, and a new sheet will be created with an additional number if the sheet with the same name already exists. If it’s the last day of the current month, a new sheet for the next month will be created. The script has several functions, including getting the name of the current month, creating a new sheet with the specified name, adding headers to the new sheet, and appending the data to the sheet. Script is designed to create a new sheet at the end of each month with the name of the month and the year. The year will be incremented by 1 if the “flag” variable is set to 1. The “testForNextYear” function is supposed to update the “flag”. The script checks if the current time is equal to the first day of the next year. If it is, the flag is set to 1.

Google Sheets App script created in parts by OpenAI.com, ChatGPT. Additional code by William Lucid 02/01/202

Gist of BME280 ChatGPT Google Sheets App script

Regards,
William

0 Vote Up Vote Down
Sara Santos Staff answered 2 months ago

Hi.
Thanks for sharing.
I took a look at your live feed. Everything seems to be working as planned. That’s great. Thanks for documenting the project.
Regards,
Sara

Primary Sidebar

Login to Ask or Answer Questions

This Forum is private and it’s only available for members enrolled in our Courses.

Login »

Latest Course Updates

  • [eBook Updated] SMART HOME with RPi, ESP32, and ESP8266 (version 1.2) March 8, 2023
  • [eBook Updated] ESP32-CAM Projects (version 1.3) January 7, 2023

You must be logged in to view this content.

Contact Support - Refunds - Privacy - Terms - MakerAdvisor.com - Member Login

Copyright © 2013-2023 · RandomNerdTutorials.com · All Rights Reserved

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.