Send MKR1000 Data to Google Sheets

This project allows you to process data with your MKR1000 and send that data to Google Sheets in the cloud via WiFi. This project can be accomplished without any credit card sign-ups or use of proprietary, black box API’s. For this specific example I use the inexpensive and ubiquitous DHT11 environmental sensor, however you can easily alter the included Arduino code for any sensor or device you like. It’s worthwhile to point out now that this project could easily be combined in a sketch with one of my related project, which uploads DHT11 data to Thingspeak.com for graphical charting.

This is the first of four parts. In the first part of the project you will set up and configure your own Google Sheet to accept your WiFi Data by using the included Google App Script code.

Part 1: Creating and Deploying your Google Sheet

1. Sign up for a free Google email if you don’t have one.

2. log in to your free Google account and create a new “Google Sheet,” this is the spreadsheet which will be populated by our DHT’s sensor values via WiFi. At this point you will need to copy and save your spreadsheets URL key. This key is listed in the URL between the “/d/” and the “/edit” of your new spreadsheet (see blue circle).

3. Name your Spreadsheet something original like “Environmental Data,” now we must create our Google App Script (similar to JavaScript) which will process our data and populate our spreadsheet correctly. To insert our Google App Script we first navigate from our spreadsheet to the Script Editor:

Tools →Script Editor

We will now be on the Google Script Editor page. Now we can name our Gscript something extra-creative like “My Environmental GScript”. At this point we have four things left to do on the Script editor page.

A) Copy and Paste the included Google Script code into the Editor

B) Copy and paste the previously saved spreadsheet URL key (step 2) into the correct line in the Google Script code (between the quotes) where it says:

var id = '   '; // Spreadsheet ID

C) Save the script: File→Save All

D) Deploy as a web App: Publish-→ Deploy as web App…

4. Our final Step for setting up our Google sheet will involve configuring the Web deployment parameters. It is important you set all these fields correctly. If you don’t set all four fields exactly your spreadsheet won’t work properly.

Field 1) Copy and Save the “Current web app URL:“ which has just been generated, we will need this later when we configure our API in PushingBox.

Field 2) Save a project version as “new” on each iteration, it’s important to note that if you don’t make a new project version for each script revision you make (if you decide to make any revisions), your script revisions won’t be updated on the web. This is counter-intuitive, and easy to neglect, but this is currently how Google has this system configured.

Read more: Send MKR1000 Data to Google Sheets