• 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

Send Sensor Reading via Email in “MicroPython directly to Google Sheet (page 191)

Q&A Forum › Category: Other › Send Sensor Reading via Email in “MicroPython directly to Google Sheet (page 191)
0 Vote Up Vote Down
zolandv asked 6 years ago

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!

Question Tags: #GMail, #Google Sheet, MicroPython
3 Answers
0 Vote Up Vote Down
Sara Santos Staff answered 6 years ago

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

0 Vote Up Vote Down
zolandv answered 6 years ago

I’l be glad when you do it, thos solusion can really help to somebody in their practice

0 Vote Up Vote Down
Sara Santos Staff answered 6 years ago

That’s great!
Thank you so much once again.
Have a great week.
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

  • [New Edition] Build ESP32-CAM Projects eBook – 2nd Edition April 16, 2025
  • [eBook Updated] Learn ESP32 with Arduino IDE eBook – Version 3.2 April 16, 2025

You must be logged in to view this content.

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

Copyright © 2013-2025 · 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.