Forwarding Incoming SMS to Google Sheets

46elks
46elks
Jun 15, 2016 · 5 min read

You want a phone number that can receive sms and forward them into a Google Sheet. You’re in the right place! This tutorial assumes no prior knowledge of programming or custom telephony. If you like to code we highly recommend that you check out our API over at 46elks.com.

Let’s get started, shall we?

1. Setup a 46elks phone number

First off, you’re going to register an account at 46elks and get yourself one of our phone numbers. Go here to create an account. Once you’ve registered and logged in you’ll be taken to the Dashboard. Click “Numbers”, choose your country and click “Choose”. You will be given some more information about the number and then you can click “Buy Number”.

Image for post
Image for post

2. Create a Google Sheet

Now go ahead and create a new Google Sheet. Rename it as sms_feedback and add headings Date, From & Message, to the three first columns:

Image for post
Image for post

It’s important for the sake of this tutorial that the spreadsheet is called precisely sms_feedback (not smsfeedback, SMSFeedback or myspreadsheet).

3. Add a Google script to the spreadsheet

Now, we have our 46elks virtual phone number, we have the Google Sheet, now all we need is the “glue” to get them talking to each other. That glue is Google Scripts. Create a new script from your spreadsheet by selecting Tools > Script editor… in the toolbar. You should be met with a new page that looks something like this:

Image for post
Image for post

Replace everything in the editor with the following snippet:

We now need to tell the script which spreadsheet it should work with. Open your spreadsheet again and click on the address bar.

Image for post
Image for post
The address bar

The highlighted text in the image below is theURL of your spreadsheet. This is what the script needs in order to figure out which spreadsheet to work with. Copy everything up until #gid.

Image for post
Image for post
The spreadsheet URL. Every spreadsheet has a unique URL so yours will be different.

Now switch back to the script editor. Go to File > Project Properties. If you’re asked to choose a project name, call it anything you like. Next, choose the “Script properties” tab in the pop-up menu. Add a new row. Set the property to be spreadsheetUrl and the value to be the URL you copied from the address bar. You should end up with something like this:

Image for post
Image for post

Again, it’s important that the property is called precisely spreadsheetUrl and not SpreadSheetURL,spreadsheet_url etc. Your URL will be different than the one in the above image but everything else should be the same. If all looks well then click “Save”.

4. Deploy the script and add it to your 46elks number

This is the final step so let’s just get to it. First, you’re going to deploy your script. Open the script editor and choose Publish > Deploy as web app… in the toolbar. You should see a popup like this:

Image for post
Image for post

When deploying on the dropdown below “Project version” always select New. This will ensure the latest changes are applied to the new app.

It’s important to make sure that “Execute the app as” is set to Me and “Who has access to the app” is set to Anyone, even anonymous. This allows external services (like 46elks) to trigger the script. The actual spreadsheet is still private, of course.

Click Deploy. Google will prompt you to inform you that authorization is required. Click Review Permissions and then Allow. You should see a confirmation dialogue with a URL (as the picture below). Copy this URL and login to your 46elks dashboard.

Image for post
Image for post

Navigate to your new virtual phone number by clicking “Numbers” and then “Edit” next to the number you want to use for this tutorial. You should see a window similar to the one below. Paste your google scripts URL into the “sms_url” section and click “Save changes”.

Image for post
Image for post

Some helpful tips sometimes necessary in this section …

If your script URL contains something like /u/0/ or /u/1/ (the number varies) you need to remove that part of the URL before setting the sms_url. For example in this URL you remove /u/0:

https://script.google.com/macros/u/0/s/AK…wvC/exec

And get:

https://script.google.com/macros/s/AK…wvC/exec

That’s it! You’re done! Send an SMS to your 46elks number and it will magically appear in your Google Sheet.

Image for post
Image for post

Troubleshooting

If you make changes to the script, you need to publish it again as a new version number. Don’t forget to update the sms_url on 46elks to the updated URL.

That’s it for now, thanks for reading!

P.S. This post was updated September 2020 to keep up with changes in Google Sheets and to make it easier to use.

46 thoughts

The 46elks blog

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store