On page 191 Book “MicroPython ESP32 ESP8266” v.1_1 I saw the code:
sensor_readings = {'value1':temp, 'value2':hum, 'value3':pres} print(sensor_readings)request_headers = {'Content-Type': 'application/json'} request = urequests.post( 'http://maker.ifttt.com/trigger/bme280/with/key/' + api_key, json=sensor_readings, headers=request_headers)
After some practice I tried to write ‘sensor_readings’ directly to Google Sheet:
def to_GSHEET ( google_script_code, payload, mail=None ): #import urequests if ( mail ): payload['mail'] = mail request = ureguests.post('https://script.google.com/macros/s/'+google_script_code+'/exec', json = payload) print('--------') print(request.text) request.close()
Some JS-code need to add in Tools->Script Editor in Google Sheets:
function doPost(d) { const SHEET_ID = "--- Take this ID from Google Sheet API ---"; const TIME_ZONE = "GMT+7"; const TITLE = ["Event","When","Temp","Hum","Press"] try { var ss = SpreadsheetApp.openById(SHEET_ID); var sheet = ss.getSheets()[0] // Use first Sheet in WorkSheet var data = JSON.parse(d.postData.contents); if (data == undefined) { return ContentService.createTextOutput("GSheetApp ERROR!\r\nWrong parameters!"); } else { var row = sheet.getLastRow() + 1; if (row == 1) { // append TITLE at first row if Sheet empty sheet.appendRow(TITLE) row += 1 } sheet.getRange(row, 1).setValue(data.Event || "no ID"); sheet.getRange(row, 2).setValue(Utilities.formatDate(new Date(), TIME_ZONE, "dd.MM.yyyy HH:mm:ss")); sheet.getRange(row, 3).setValue(data.Temp || "no data"); sheet.getRange(row, 4).setValue(data.Hum || "no data"); sheet.getRange(row, 5).setValue(data.Press || "no data"); SpreadsheetApp.flush(); if (data.mail !== undefined) { // Check Mail if (mail( data.mail, "Google Sheet msg", d.postData.contents) == false) { return ContentService.createTextOutput("GMailApp ERROR!\r\n" + e.message); } } //appendData(data); // Save data to GSheet/GSheet.csv file return ContentService.createTextOutput("OK"); } } catch(e) { return ContentService.createTextOutput("GSheetApp ERROR!\r\n" + e.message); } } function mail(recipient, subject, content) { try { GmailApp.sendEmail(recipient, subject, content); return true; } catch(e) { return false ; } }
Finally some part of main module looks like
... Connecting to WiFi, checking BME280, etc. gKey = '--- Take this key from Script Elitor Google Sheets ---' sensor_readings = {"Event":"BME280","Temp":float(temp[:-1]),"Hum":float(hum[:-1]),"Press":float(press[:-3])} to_GSHEET( gKey, sensor_readings, 'noname@nomail.com' ) #to_GSHEET( gKey, sensor_readings ) # if I not need Mail to me
So I not use IFTTT with their limitations and names ‘value1′,’value2′,’value3’
and many thanks to Ryi who initiate me to study Micropython!
Hi.
Thank you so much for sharing your project.
We used IFTTT in the eBook because it is a simpler way for beginners. However, some of the limitations can be a bit annoying. Your solution is definitely better!
Can we write a tutorial (free) in our blog for sharing your solution to publish directly to google sheets? I suspect there are a lot of people interested in this.
I’m glad you’re enjoying MicroPython.
Thank you once again.
Regards,
Sara
I’l be glad when you do it, thos solusion can really help to somebody in their practice