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 Logging Google Script woked as coded; at Midnight, July 1, 2023.

Moved to new Google sheet deployment:  BME280 Logging features data logging greater than one year.

Live feed: BME280 Logging greater than one year logging.

Google Sheet App script 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.

Google Sheets App script created in parts by Google’s Bard; additional code by William Lucid 07/02/2023.

Gist of BME280 Logging 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

Hi William,
Out of curiosity, please allow me one question:
How did you solve the HTTPS authentication with Google?
Do you make use of IFTTT or is there another solution available by now?
Thanks,
LoetLuemmel

Hi Loet,

Basically with the help of Google’s Bard (AI); just used Google Scripting, no third party software!  First attempts were done using this article:  “ESP32 Data Logging to Google Sheets with Google Scripts.”

My current Google Script:  Gist of “New BME280 Logging.”
 
Regards,
William

Hi William,

Thank you for your superfast response and the details!
Food for thoughts over this hot weekend. – I just received a BME680 and this would be a nice project for testing the sensor.
Thanks again and happy coding,
LoetLuemmel

Hi Loet,

Check out my BME680 Gas Study
My other projects on Github  you might be interested in checking out.

Regards,
William

Hi William,
Yes thank you, I already swallowed your code on Github yesterday.   J
Regards,
Loet

Good Morning,

Roll over from July 2023, “Google Sheet” to August 2023, “Google Sheet” was successfull.  New sheet for August was created at 00:00:00.

Gist for “BME280 Logging” Google Sheet script

Live view “BME280 Logging” Google Sheet

William

It’s looking good. Thanks for sharing.
Regards,
Sara

Good Evening,

Updated “BME280 Logging” Google Sheet Script; logs data to a yearly Google Sheets, workbook; with monthly spread sheets.  Had an issue where data logging stopped at every twenty-nineth day of the month.  Took serveral months to catch the error.

Corrected Gist of “BME280 Logging” gs script

Regards,
William

Great.
Thanks for the follow-up.
Regards,
Sara