#SMSheet
Logs SMSes received through a Twilio number to a Google Spreadsheet.
Quickly set it up by signing up for a Twilio account, a Google API account and deploying this code to Heroku.
Last updated February 3, 2016.
##Setting up your Twilio SMS
At the end of this you should have:
- a phone number from Twilio that can receive SMSes
- an SID
- an AuthToken
###Getting your Phone Number
- Sign up for an account at https://www.twilio.com/
- Log in, go to https://www.twilio.com/user/account/messaging/phone-numbers or click PHONE NUMBERS on the main navigation.
- Click Buy a Number
- Choose a number with SMS capabilities by clicking Buy
- Confirm the dialog by clicking Buy This Number
- Click Setup number
- You should now be on the Configure screen
- Click Save.
Remember how to get to this page. We'll need to add the Heroku app url later to this page. It's accessible under PHONE NUMBERS > Manage and then clicking on the Phone number from the list.
###Getting your Account SID and AuthToken
- While you are still signed it to Twilio.com, click on your username/email address on the navbar on the top right.
- Click Account from the dropdown
- Under API Credentials on the left, where the Live box is:
Account SID
, which should look something like a series of letters and numbers, ex.Pasdlkahdlahdlahd123136
- click on the padlock icon under AuthToken
- Copy the
AuthToken
, which is a series of letters and numbers, that look similar to the SID
##Setting up your Google API
At the end of this you should have:
- a Google service account ID
- a Google private key
###Steps
- Go to https://console.developers.google.com/project and sign in
- Click Create Project
- Provide a name, and click Create
- You will be directed to a Pinterest-like dashboard for the project. Click Enable and manage APIs in the blue box.
- Click the Drive API among the list of APIs
- Click Enable
- You will get a warning This API is enabled, but you can't use it in your project until you create credentials. Click "Go to Credentials" to do this now (strongly recommended).
- Click Credentials on the left menu. Don't click the link in the warning because it will take you to a wizard.
- Click New credentials in the dropdown, select Service account key
- Under Service account select New service account
- Give it a name
- Give it a service account ID
- Choose JSON as the Key type
- Click Create
- It will download a JSON file to your computer
The JSON file should contain both the Google service account ID
(which will be marked as client_email
in the JSON file) and the Google private key
##Setting up your Google Spreadsheet
At the end of this you should have:
- a Google Spreadsheet ID
###Create the spreadsheet
- Go to drive.google.com and create a spreadsheet
- Copy the ID from the URL. For example if the URL is
https://docs.google.com/spreadsheets/d/1cQ_XnVE0gQWzZ-aawFPGRPzBhqVsu1VVVbrf800LwgY/
then the ID is1cQ_XnVE0gQWzZ-aawFPGRPzBhqVsu1VVVbrf800LwgY
- The spreadsheet should have two sheets.
- The first sheet should have the top rows labeled with: Timestamp, Sender, Message
- The second sheet should have the top row labeled with: reply
- You can put a custom reply that will be sent via SMS on the second row
Here's a sample spreadhseet: https://docs.google.com/spreadsheets/d/110pzwINpi9iXo943ccSU6kAA3ko9dp_TC4RtLY1VKbI/edit?usp=sharing
###Share it with the Google service account ID
- Click Share on the top right
- Enter the
client_email
from your JSON file, under People - Make sure Can edit is selected
- Click Send
##Putting it all together by deploying to Heroku
- Click the button
- Provide an App Name. This will be used as the URL to your app. ex. an App name of smsheet will create smsheet.herokuapp.com.
- Leave Runtime as is
- For Config Variables
- GOOGLE_CLIENT_EMAIL is the
client_email
in the JSON file that was downloaded from Google - GOOGLE_PRIVATE_KEY is the
private_key
in the JSON file that was downloaded from Google - GOOGLE_SHEET_ID is the Google spreadsheet ID where the messages will be logged
- TWILIO_ACCOUNT_SID is the
SID
from Twilio - TWILIO_AUTHTOKEN is the
AuthToken
from Twilio - Click Deploy for Free
- Once the app is deployed, copy the URL ex.
https://smsheet.herokuapp.com
- Log in to Twilio.com
- Under PHONE NUMBERS, click Manage and then click on the Phone number from the list.
- On the phone number's Configure Screen, under Messaging
- Select Configure with URL
- For Request URL put in your Heroku app's URL followed by a /receive. For ex.
https://smsheet.herokuapp.com/receive
. - Select HTTP POST next to that URL
- Click Save
##Testing it
- Send a text to the Twilio phone number
- You should receive a reply after sending
- The Google Spreadsheet should have the message you texted logged