Forwarding Incoming SMS to Google Sheets

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 “Add number”.


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:

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

We have the 46elks 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:

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.

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.

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:

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:

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 like this:

Login to your 46elks dashboard and copy the URL from the popup into the sms_url field of your number. Press save.

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.


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!