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
This is the spreadsheet that emails when a customer’s tracking code is updated.
There are three columns:
- 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:
Take a look on the code
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
Then set the function checkAllCodes to run every 5 minutes and you are good to go!
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.
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! :)