Introduction: CloudyData - ESP8266 to Google Sheets Made Simple

By

I've been looking for cloud data storing for a long time during last years: it is interesting to monitor data from any kind of sensor, but it is more interesting if these data are available everywhere without any storage difficulty such as using SD cards or similar, in local storing.
I used to local store on SD cards wind speed data years ago, before IoT and cloud services begun to be simple to use: now one step beyond is possible with particular difficulties, even if you aren't an IoT expert or a developer.

In this instructable I will describe how I'm monitoring my air quality inside home, expecially referring to dust and particulate concentration near my 3D printer, trying to understand if 3D printing process is dangerous in terms of PM2.5, and how I'm using Google Sheets to store data, without any third part service needed.

Step 1: General Goal

I want to know if living in presence of a 3D printer can be dangerous.

To do this I need data, and data have to be stored in the cloud.

I want to use Google Sheets since it is simple and effective.

I want privacy, too: so sharing data with Google is not my first choice but it is better than using third party services, as many bloggers use to do.

Using Google Sheets is a step toward uploading data to a personal local storage such as Nextcloud on a simple NAS: this will be described in a future instructable.

Step 2: First Step: Sensors

I use 2 sensors to monitor my home air quality:

Step 3: Second Step: Connecting to Microcontroller

Wemos D1 mini is probably the best way to prototype around ESP8266: microUSB connector, onboard led, nice shields ready to use available.

I connected SHT30 shield on Wemos D1 mini directly (take care of orientation!), then I connected Nova Air Sensor to Wemos D1 mini as follow:

Wemos GND pin --> Nova Air sensor GND

Wemos 5V pin --> Nova Air sensor 5V

Wemos D5 pin (RX pin) --> Nova Air sensor TX

Wemos D6 pin (TX pin) --> Nova Air sensor RX

You can have a look here for more info:

https://www.hackair.eu/docs/sds011/

https://www.zerozone.it/tecnologia-e-sicurezza/nov...

https://www.instructables.com/id/Make-one-PM25-mon...

Step 4: Third Step: Building a Sketch

Now you need to build a sketch: we are lucky, some guys developed specific libraries for Nova Air Sensor so you can write down easily your software.

Mine uses SHT30 library too, to measure and upload temperature and humidity data.

I remixed some sketch I found online, expecially the one from nishant_sahay7, whose tutorial is complete and full of info. You can find it here.

I used this library: https://github.com/lewapek/sds-dust-sensors-ardui...

I will comment only a few lines in the sketch I generated:

  • line 76-77: waking up the dust sensor for a while, then it will go to sleep again, since datasheets states it is intended to work for around 8000 hours, which is more than enough, but not infinite
sds.wakeup();
delay(30000); // working 30 seconds
  • line 121: data sent are temperature, humidity, PM2.5 and PM10
sendData(t, h, pm2_5, pm10);
  • line 122-123: I don't use ESP.deepSleep, I will try in the future; by now a simple delay(90000) will be enough to have data sending every 30s + 90s = 2 minutes, more or less
//ESP.deepSleep(dataPostDelay);
  
delay(90000);
  • line 143:

this is the most important line, the order you create the String_url to upload data have to be the same you will use in Google Script (see next steps)

String url = "/macros/s/" + GAS_ID + "/exec?temperature=" + string_x + "&humidity=" + string_y + "&PM2.5=" + string_z + "&PM10=" + string_k;

Attachments

Step 5: Fourth Step: Preparing Google Sheet and Its Script

Credits goes to nishant_sahay7, as I told.

I simply re-publish here his work, adding some tips for future improvements and modding:

  1. Setting Up Google Sheets
    1. Open Google Drive and Create new Spreadsheet and name it, after that give the fields with the parameters you want to define.
    2. Sheet ID is shown in figure 2
    3. Go to Tools-Script Editor (figure 3)
    4. Give the name same as of Spreadsheet(figure 4)
    5. Pick the code from here and paste in the Script Editor Window (figure 5)
      • Replace the var sheet_id with your Spreadsheet ID from step 2
    6. Go to Publish - Deploy as Web App (figure 6)
    7. Change the access type to anyone, even anonymous, and deploy (figure 7)
    8. Go to Review Permissions (figure 8)
    9. Choose Advanced (figure 9)
    10. Choose Go to (file name) and then allow (figure 10)
    11. Copy the current web app URL and click OK (figure 11)
  2. Getting Google Script ID
  3. Change on your needs
    1. you have to change Google Script AND Arduino sketch accordingly, in order to add or remove values and columns: compare figure 5 and figure 5b

Step 6: Fifth Step: Connecting All Together

Now you have a device sending data to Google Sheets, a Google Script able to receive and allocate data, a browser is enough to view data, on computer or smartphone or whatever you like.

The best would be to manage a little these data, to show only a few needed.

Step 7: Sixth Step: Graphing Data

In order to have a simple but interesting and useful panel I organized my data this way:

  1. the original google sheet, main one, used to pick up its ID to enter in Google Script, MUST be untouched, and maintain its order
  2. I generated two other sheets, following the main one
    1. one to extract only a few data from the whole stuff, last 24 hours for example

      To extract data I used SORT and QUERY Function, inserting in first cell of extracted data
      =SORT(QUERY(Foglio1!A2:Z,"order by A desc limit 694"),1,1)
    2. the other to create graphs to show values,making a simple panel

Step 8: Seventh Step: Analyzing Data

I did a few analysis and I can say, by now, there shouldn't be any danger using 3D printer (material: PLA) in terms of PM2.5 and PM10.
Every time I start a new print particulate values goes to the roof, only for a while: I think this is due to previous deposited dust on the 3D printer bed, so that when effector fan reaches the plate it starts flying all around.
After a few minutes dust is away since fans continue blowing and PM2.5 and PM10 values get down to lower values.

Further data and analysis are needed, indeed.

Step 9: Results: 3D Printing Seems to Be Safe!

So using a bit of IoT, mixing together sketches and libraries and tutorialsfrom many authors lead to a comprehensive platform able to display Air Quality data, and specifically 3D printing particulate generation.

3D printing process (PLA only, by now) seems to be safe!

Step 10: Future Improvements: Nextcloud Storage

Next step will be privacy oriented, building a Nextcloud storage on a simple Raspberry to self storage my data.

Some useful links: