/smsheet

Logs SMSes received through a Twilio number to a Google Spreadsheet.

Primary LanguageJavaScript

#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.

Deploy

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

  1. Sign up for an account at https://www.twilio.com/
  2. Log in, go to https://www.twilio.com/user/account/messaging/phone-numbers or click PHONE NUMBERS on the main navigation.
  3. Click Buy a Number
  4. Choose a number with SMS capabilities by clicking Buy
  5. Confirm the dialog by clicking Buy This Number
  6. Click Setup number
  7. You should now be on the Configure screen
  8. 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

  1. While you are still signed it to Twilio.com, click on your username/email address on the navbar on the top right.
  2. Click Account from the dropdown
  3. Under API Credentials on the left, where the Live box is:
  4. Account SID, which should look something like a series of letters and numbers, ex. Pasdlkahdlahdlahd123136
  5. click on the padlock icon under AuthToken
  6. 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

  1. Go to https://console.developers.google.com/project and sign in
  2. Click Create Project
  3. Provide a name, and click Create
  4. You will be directed to a Pinterest-like dashboard for the project. Click Enable and manage APIs in the blue box.
  5. Click the Drive API among the list of APIs
  6. Click Enable
  7. 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).
  8. Click Credentials on the left menu. Don't click the link in the warning because it will take you to a wizard.
  9. Click New credentials in the dropdown, select Service account key
  10. Under Service account select New service account
  11. Give it a name
  12. Give it a service account ID
  13. Choose JSON as the Key type
  14. Click Create
  15. 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

  1. Go to drive.google.com and create a spreadsheet
  2. Copy the ID from the URL. For example if the URL is https://docs.google.com/spreadsheets/d/1cQ_XnVE0gQWzZ-aawFPGRPzBhqVsu1VVVbrf800LwgY/ then the ID is 1cQ_XnVE0gQWzZ-aawFPGRPzBhqVsu1VVVbrf800LwgY
  3. The spreadsheet should have two sheets.
  4. The first sheet should have the top rows labeled with: Timestamp, Sender, Message
  5. The second sheet should have the top row labeled with: reply
  6. 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

  1. Click Share on the top right
  2. Enter the client_email from your JSON file, under People
  3. Make sure Can edit is selected
  4. Click Send

##Putting it all together by deploying to Heroku

  1. Click the button Deploy
  2. 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.
  3. Leave Runtime as is
  4. For Config Variables
  5. GOOGLE_CLIENT_EMAIL is the client_email in the JSON file that was downloaded from Google
  6. GOOGLE_PRIVATE_KEY is the private_key in the JSON file that was downloaded from Google
  7. GOOGLE_SHEET_ID is the Google spreadsheet ID where the messages will be logged
  8. TWILIO_ACCOUNT_SID is the SID from Twilio
  9. TWILIO_AUTHTOKEN is the AuthToken from Twilio
  10. Click Deploy for Free

Connecting the Heroku app to Twilio

  1. Once the app is deployed, copy the URL ex. https://smsheet.herokuapp.com
  2. Log in to Twilio.com
  3. Under PHONE NUMBERS, click Manage and then click on the Phone number from the list.
  4. On the phone number's Configure Screen, under Messaging
  5. Select Configure with URL
  6. For Request URL put in your Heroku app's URL followed by a /receive. For ex. https://smsheet.herokuapp.com/receive.
  7. Select HTTP POST next to that URL
  8. Click Save

##Testing it

  1. Send a text to the Twilio phone number
  2. You should receive a reply after sending
  3. The Google Spreadsheet should have the message you texted logged