Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

"It’s amazing how easy it is to forget the magical little moments you assumed would get tattooed on your brain." I read in Matthew Dick's article on how to remember more of your kid's childhood and couldn't help nodding along.

My toddler recently turned two and a half and my second daughter was born three weeks ago, so special moments are abundant but the ability to remember them is severely compromised. His suggestion of recording a memory every day in an Excel sheet or leather bound journal resonated with me, but after a few days of trying to find time to open up Excel and write a memory I realized I needed a more lightweight solution. Texting, especially with the voice dictation feature, has always been my favorite medium, and Twilio makes it easy and cheap to set up a dedicated phone number that can record those text messages to a Google sheet.

There are only a few lines of code required that you can just copy and paste from this article. It only takes a few minutes to set up!

Tutorial Requirements

To follow this tutorial you’ll need the following:

• A free or paid Twilio account.
• A free Google account.

Set up your Google Sheet

First create a new Google sheet.

In the first row, type Date, From and Message as column headings. We’ll be appending rows below this row with a timestamp, the phone number the text was sent from and the contents of the text message.

Next open Script Editor by selecting Tools -> Script Editor.

This will open up a new tab with a code editor. Copy the following code into the editor overwriting any default code.

The function opens the currently active spreadsheet and when it gets a new POST request it appends a new row to the spreadsheet with:

• A time stamp containing the current date and time
• The parameter passed in from the “From” field of the POST request which will be the phone number the text message was sent from
• The parameter passed in from the “Body” field of the POST request which will be the contents of the text message

When you’ve copied the code in, click on the “Deploy” button in top right and select “New Deployment”. It will open up a Configuration screen.  

Add a description such as “Text Memories” and change “Who has access” to Anyone. This will make the endpoint public, but it will not give people access to your code or the Google sheet.  Click the “Deploy” button to deploy the function.  

Once it’s done deploying, you will see a “New deployment” screen with a Web app URL.

Keep the Web app URL handy, as you’ll need it later to configure the Twilio phone number webhook.

If you make changes to the code, keep in mind that saving the function will not update the deployed endpoint. Anytime you make a change to the code you will need to create a new deployment and copy the new URL.

Set Up Phone Number with Twilio

The next step is to configure a Twilio phone number. Log in to the Twilio Console and access the Phone Numbers page. If your account does not have a phone number yet, click the “Buy a Number” button on the top right and select a phone number that you like. If you are using a trial account you will be making this purchase with your trial balance. If you are using a paid account, most phone numbers cost $1 USD per month.

Once you have a phone number, click on it to access its configuration page. Scroll to the bottom of the “Configure” tab until you see the “Messaging” section. Then paste in the Web app URL from your Google Sheets Script into the “A MESSAGE COMES IN” slot. Keep Webhook and HTTP POST settings as below.

Click the “Save” button when you’re done.

Text your Memories

Send a text message to the Twilio phone number that you have set up from your own cell phone. You’ll see your message, phone number and the time appear in the Google Sheet.

Now whenever your little ones do something that you want to remember just text (or voice memo) directly into your Google sheet.

Optional Security Feature

You can ensure that only a pre-approved list of phone numbers can add to your spreadsheet by adding a security check to the beginning of the function:

Modify “+16501234567” and “+16507654321” to be phone numbers you wish to allow. This will ensure that only phone numbers in the “approved_numbers” array will be able to add memories to the Google sheet and will stop execution if a message is received from any other number.

After making these changes, you will need to create a new deployment and copy and paste the updated function URL into the Twilio “a message comes in” webhook as you did above.

In just a few days we found ourselves texting into the Google sheet multiple times a day. Little things like our eldest telling her baby sister to “use your words” instead of crying and calling pumpkins tomatoes at the pumpkin patch that would have otherwise been forgotten in the blur of raising children are now safely recorded for our family to look back on.

Alina Libova Cohen is a venture capitalist, entrepreneur and software engineer. Most recently she was a General Partner at Initialized Capital and prior to that led startup investing for Tamares. Before transitioning to VC she was a software engineer at Facebook following their acquisition of RecRec, a computer vision startup she cofounded. You can follow her on Twitter: @alibovacohen.