Arduino -> Google Sheet Logger
This repo is a simple way to collect data from an Arduino on a google sheet. Here are the steps to make this work:
-
Create a new Google sheet and give it a name.
-
In the first row, add the names of the labels of the data you wish to collect as follows:
- Go to File -> Publish for web
- Go to Tools -> Script Editor and it will open a separate Google App Script page. Give your project a name and optionally in the side bar, rename code.gs to a unique name.
-
Copy-paste the GoogleSheet_Logger.gs from the github repo.
-
In line 2, Add the URL of your spreadsheet in your browser's address bar and save the project.
-
The functions which write the data to the Google sheet need more persmissions before it can run. Select addRow from the function drop-down and click 'Run'
- It will ask for permission to access your data. Click 'Review permissions' and follow the steps to grant persmission. This only needs to be done once.
- Click 'Deploy' and make sure that your deployment type is 'Web app'. Be sure to tell the script to run as yourself, and allow anyone (even anonymous) to run it.
- When you are ready, Click the blue 'Deploy' button and make a note of the app URL. That's it! Now, you can set a GET request to the web URL as follows:
If your Web App URL is: https://script.google.com/macros/s/xyz123/exec
Then your URL w/ parameters added would be: https://script.google.com/macros/s/xyz123/exec?key1=value1&key2=value2
The script compares the key values with the column labels, creates a new row and adds data below. It also adds a date and time-stamp in the final column. If it can't find a key, it leaves the cell as blank. For example, if the google sheet has been set up as per step 2:
https://script.google.com/macros/s/xyz123/exec?Red=415&Green=113&Blue=197&Brightness=127 adds values to all the columns.
https://script.google.com/macros/s/xyz123/exec?Red=211&Brightness=199 leaves "Blue" and "Green" as empty.
Note: Try it from your web browser and you shall see a "Success!" message. Please use 'anonymous' mode or sign out of Google before doing this because if you are signed in, Google inserts a '/u/1' string in your request which causes an error.