Google Apps Script & IoT

Fran Galarza
Dec 4, 2018 · 6 min read

Google Apps Scripts are very powerful yet relatively unknown for both G Suite users and Google Cloud Platform developers.

Have you ever seen an Excel workbook with a scary macro that automates a bunch of operations? I bet you have. Well, Google Apps Scripts (I’ll call them GAS for short) are just that, macros written in JavaScript for your Google Apps like Sheets, Docs, Gmail, Calendar, Drive and more.

Apps that currently support GAS. More info here: https://developers.google.com/apps-script/

Google could have stopped there and it would have been an amazing product to integrate and automate your G Suite workflow. But they didn’t. Your Google Apps live in the cloud and so do your scripts. This is where the fun begins: they can be triggered with HTTPS calls 😲 ! Yes!

Photo by Ben White on Unsplash

I hope you can see where this is going. GAS are essentially #cloud_functions, #serverless #microservices (look mum! 3 buzzwords in one line!) highly integrated with Google Apps. Kind of an entry-level and user friendly cloud.

What can you do with them?

You could have a sensor regularly measuring the humidity of your garden’s soil and posting that value to your GAS (yes, you can make POST requests). Then your GAS could log that measurement in a Google Sheet and send you an email if the soil is too dry. It can even set an event in your calendar to remind you to water your garden.

That’s cool, but a bit boring. Let me tell you what I did last Saturday.


Controlling my lights with my calendar

I like to wake up with the sunrise light. The problem is that the sun doesn’t rise until ~6am and I like to wake up at 4am (for reasons beyond the scope of this post). The solution? Create an artificial lighting system that simulates the sunrise.

I didn’t want to have to manually turn on and off the light. I didn’t want to hard code the wake up time either. I wanted to control it with my calendar. I needed to somehow connect a light to my calendar. Here’s what I did:

Solution architecture

This is how the design ended up looking:

  • A WS2812B RGB LED strip. This strip is super fun to play with and it’s simple to control using an Arduino based microcontroller. You can address each LED individually and set any(ish) colour you like. It’s feeds on 5Vdc which made it perfect for this project because the whole thing is connected to a powered USB hub.
  • An ESP32 WiFi enabled microcontroller. Hardware-wise this is an overkill and an ESP8266 would do the job just fine, but I had a few of this around. This microcontroller is in charge of talking to the GAS and controlling the LED strip. It gradually turns on the LED strip at the time specified on my calendar, holds it at max brightness for 30 minutes and then turns it off smoothly.
  • A NodeJS app to forward HTTP requests to HTTPS requests running on a Raspberry Pi 3. It turns out that Google don’t like the HTTP requests that my ESP32 makes, maybe because they’re not secure. I didn’t have time to lose and since I have a few Raspberry Pies in my house I chose to create a simple request forwarder. The ESP32 makes an HTTP request to the Raspberry, the Raspberry makes an HTTPS request to the Google Apps Script endpoint and the response is forwarded back to the ESP32.
  • The Google Apps Script function itself. This function finds the next event in my “Alarms” calendar and returns the remaining time until that event.
  • My Google Calendar where I schedule the wake up time.

Code

I don’t want to complicate things here, so you can find the code in my GitHub repo, but I will show you the core parts of the GAS function.

First we need to create the script. Go to your drive, click “New”, “More” and “Google Apps Script”. You will see a project editor where you can start writing your code.

Whatever you write inside the “doGet()” function will get executed when an HTTPS GET request is made to the script’s endpoint.

function doGet() {
return ContentService.createTextOutput('Hello world!');
}

If you click “save” (or ⌘+S) Google will validate and highlight any syntax error. A few things:

  • ‘let’ is not supported, you have to use ‘var’
  • semicolons at the end of each line are required

My function gets the next events in the calendar. This is how it does it:

// Select the calendar
const calendars = CalendarApp.getCalendarsByName('Alarms');
const calendar = calendars[0];
// Get the events from the calendar
const events = calendar.getEvents(start, stop);

The constant “events” will be an array where every object in it will be a calendar event happening between the “start” and “stop” timestamps. Then I need to calculate the time remaining until the next event. This is how I do it:

const event = events[event_index];
const milliseconds_to_event = event.getStartTime() - start;

If the event is more than 2 hours away, I want my function to return only 2 hours. This is to avoid having an exponential representation of the number and simplify things on the Arduino side.

// Limit the output to 2 hours in milliseconds
out = Math.min(milliseconds_to_event, 7200000);
out = out.toString();
// Return an HTTP response
return ContentService.createTextOutput(out)

My thoughts on Google Apps Scripts

Designing a system very often consists on connecting many simpler systems together. Google Apps Scripts is the perfect glue in this project.

If there is a chance to automate something, I will do it, that’s who I am. GAS are great for this. And having the chance to integrate anything that speaks HTTPS is a huge plus.

There is a nice dashboard to monitor your scripts and you can even check the logs in Stackdriver.

You can modify your script and save it, but those changes won’t be live until you publish a new version of the script. And to do it you will have to provide a description of what changed. Google pushes you to do version controlling. The lazy you will hate it, but the clever you knows that it’s a good idea.

If your application is small and you don’t need high throughput, GAS is a great choice. It’s simple to use and it’s free. If you’re reading this post chances are you know how to code, so you can start building stuff straight away.

The documentation is very good, you should be able to find what you’re looking for. And you will have to use it because the community quite small, but is slowly growing.

Google Searches on “Google Apps Script” topic

There are some quotas associated to the service but they’re pretty generous.

It’s pretty awesome to think that you can build this system for under $20 in hardware in ebay (including the LEDs and the microcontroller) and use open source software and a free cloud service to glue everything together.

Google Apps Scripts are great for DIY IoT hobbyists because:

  • It’s free (you don’t need to pay for a platform like Thinger.io)
  • Logging things to a Google Sheet is much more user friendly than using a database
  • You don’t need to worry about setting a GCP account with your credit card

To conclude I’d like to say that the hardest part of the project was choosing the right RBG colour to create a warm light. Everything else is easy. Except waking up at 4am, that’s hard.

Find out more about Servian’s GCP capabilities here.

WeAreServian

The Cloud and Data Professionals

Fran Galarza

Written by

Data. Coffee. Insights.

WeAreServian

The Cloud and Data Professionals

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade