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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

That’s great!

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

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

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