Google Sheets for IOT

You are here because you know what this is all about. An IOT project often requires that I empty my pockets, learn a new database and micro-controller etc, and it is rarely that everything works in the first try, or the second, if you are persistent enough it will surely work.

Now that we are on the same page I am sharing with you this little yet useful hack that saved me a few bucks on most of my IOT projects, and the best part is it is free, cross platform, online and just works.

One of the most important things that any IOT project requires is a database to store the values, results and do some computation on them. In this brief instructable you will learn how to use google sheets to replace your typical database.

"Yes, you can use this same trick for any other project such as a mobile application, website, robotics, etc."

Step 1: Getting Ready

The only thing you need to get started is a google account. That is all that you require and it would be making fun of you to show you how to create a google account. So the next logical thing to do is, as you guessed it to create a new google sheet.

So, head on to Google Sheets and create a new sheet, and name it whatever you want.

The first row will be important. It serves as the titles to the data information we will input.

For example if you want column B to have data of dates, then in the first row of column B you will have the word date. In the following rows you will input the various dates you need.

The column titles should only be one word, no uppercase. If you need to use multiple words for the column title then insert a hyphen between the words. You can view the image for reference.

Step 2: Connecting Through Code

Now that your Google Sheet is setup, you’ll need to link the spreadsheet to your IOT deivce. To do that we’ll use an google apps script. I recommend this because it’s free and easy to setup.

You’ll need to download nothing . It can be found here.

There are detailed instructions to setting it up , but I’ll give a brief walkthrough.

Go to Tools>ScriptEditor in your created google sheet.

Copy and Paste the below code into a script file :

var sheet_key = "KEY OF YOUR SHEET";

var sheet_name = "NAME OF YOUR SHEET";
function doGet(e){

var ss = SpreadsheetApp.openById(sheet_key);

var sheet = ss.getSheetByName(sheet_name);

var temp = Number(e.parameter.temp);

var date = Number(e.parameter.date);

sheet.appendRow([temp,date]);

}

Substitute sheet_key with your Google spreadsheet key. To find your spreadsheet key, go back to your Google Sheet.

The sheet_key is in the URL and is after “https://docs.google.com/spreadsheets/d/“ and before the last “/”. The key will be letters and numbers and possibly have hyphens or underscores.

The sheet_name is the name of your workspace, in my case it is "One".

Save the script with any relevant name.

Step 3: Publishing Your Script

When you run this on your device, it will not work that is because you need to publish your script.

Go back to your Google Script and go to Publish >Deploy as web app You will get a dialog menu.

It will ask you to authorise the code execution on your behalf, simply click Allow. You only need to do this once.

Make sure you have entire document selected in the drop down option. You can choose to automatically publish changes by checking the feature on the bottom. If you don’t do this, you will have to publish the spreadsheet every time you make a change.

By using AppsScript you can connect your Google Sheet to your IOT device. Using Google Sheets as your database allows you to avoid learning a new database platform. With that said, this is an easy and free way to get a database up and running relative to other options.

Step 4: Setting Up URL

So far we have set up your database now it is time to put some values into it, for that do the following.

Copy the link to your deployed web app, then replace the "dev" or "exex" with "exec?temp=30&date=1708"

It is obvious that you can modify the value of temp and date using the code in your IOT platfrom.

Thus your URL wll look like :-

''https://script.google.com/macros/s/SCRIPT_ID/exec?temp=1234&date=1234"