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 “Choose”. You will be given some more information about the number and then you can click “Buy 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
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:
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 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.
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:
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.
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”.
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.
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.
Please be advised that Google has a limitation on how long a script is allowed to run: “maximum execution time”. It depends on which kind of Google account you use, the lowest time allows a script to run for 6 minutes. If you plan to send or receive a large amount of SMS at the same time, please contact 46elks support on +46766861004 or help@46elks.com.
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.
