vandal printing an instagram photo directly on A t-shirt

Leveraging Google Spreadsheet with Javascript

Notify customers of their shipment with a simple Google Spreadsheet

I’m going to show you how Vandal leverage interaction with customers letting them know when their t-shirts are about to be delivered with a simple Google Spreadsheet.

Not only customers returned our emails thanking for the awesome support, but we also noticed a significant decrease on returned orders.
— Arturo Edo, Vandal Co-Founder

It saved them hours of engineering time and will require NO infrastructure maintenance at all. It is just about creating a spreadsheet and adding a bit of salt to it. Just awesome.

Google Spreadsheet on Steroids

Imagine if you could control and access any data of the Google services like Spreadsheet, Docs, Tasks, Gmail, Calendar and others using JavaScript…

Well, you can — they call it Google App Script: a JavaScript with access to Google’s API.

It is just like Javascript on the browser but, instead of access to the DOM, you’ll have access to Google’s API.

Click here to see the demo

This is the spreadsheet that emails when a customer’s tracking code is updated.

There are three columns:

This is what the spreadsheet looks like. Three columns, and only two for you to fill: email and tracking code.

• Customer’s email
• Customer’s tracking code
• “Was it sent?” confirmation

Our goal will be checking the tracking code against the post office website (in our case the brazilian post office Correios) and sending the customer an email if it is marked as out for delivery.

Do you have this spreadsheet open already?

Open it, save as a copy for you to have full access.
I promisse I’ll be waiting here.

Now open the Google App Script IDE as shown below:

Click in “Tools > Script Editor” to access the Google App Script editor

Take a look on the code

Google App Script IDE with the code from this example

The code is open and yours to hack.

A quick explanation of what the code is doing:

• It gets access to the spreadsheet;
• Iterate through every row;
• Hit the post office website to see if the tracking code matchs the wanted status;
• Send an email to the customer if so;
• Mark a “yes” on the “Was it sent?” column to make sure no duplicate emails will run out.

Make Google run your code every 5 minutes

This is the BEST part! You can get Google running your code every minute, hour, day or whatever timeframe you want, for free :)

This is also something you will HAVE to do after cloning the spreadsheet: on the Google App Script IDE, click on Resources > Current project’s triggers

Set Google workers to run your code in every given timeframe by clicking Resources > Current project’s triggers

Then set the function checkAllCodes to run every 5 minutes and you are good to go!

Choose the function you want to run and when to run

Give it a try!

Start by cloning this spreadsheet, I leave a couple of valid tracking code there already for you to test.

Go to the Script Editor, select the function checkAllCodes and click on the play button to run the code imediatly.

Click on the play button, with the checkAllCodes fucntion selected, to run the code immediately.

You shall be asked to authorize google to send emails in your behalf, and then you will promptly receive the email notification for whatever tracking codes that meet the status “out for delivering”.

I wish this post inspires your curiosity to build cool things on Google Spreadsheet. If you already have something in mind, I’d love to know what you are willing to build!

Have a nice discovering! :)

Hit the big “Recommend Button” if you enjoyed this post :)
and follow me on twitter