Building an SMS to Google Sheets Application with AWS Lambda
Published on May 13, 2021

In this tutorial, we'll show how you can capture SMS Messages sent to your own Nexmo number and log those into a Google spreadsheet using an AWS Lambda written in Python. This can be used for capturing feedback, registration for further information, recording votes, or any form of data collection. What's great about this is that you don't need your own dedicated server, just a small chunk of code hosted on AWS Lambda and a Nexmo account.

Vonage API Account

To complete this tutorial, you will need a Vonage API account. If you don’t have one already, you can sign up today and start building with free credit. Once you have an account, you can find your API Key and API Secret at the top of the Vonage API Dashboard.

This tutorial also uses a virtual phone number. To purchase one, go to Numbers > Buy Numbers and search for one that meets your needs.

We'll cover:

If you just want to dive in, you can always Grab the Code!

Why Lambda?

Serverless technologies are a rapidly emerging trend. Closely related to the concept of microservices, the idea is that instead of building a monolithic application that does everything your business wants you to build a number of discrete smaller applications that each does a single function and then tie those together.

One challenge of building these microservices is that the overhead of running a full server stack (OS, Web Server, Database, Application etc) can add a large amount of work and costs. This is where we are seeing a new trend in cloud computing towards serverless technologies, one of the most interesting of these is AWS Lambda from Amazon. You can now write a simple function in either Python, Java or Javascript and have that invoked by either an external API call or another part of AWS like a file being uploaded to S3.

As well as removing the requirements to run your own servers you can also keep the costs down; you are only charged for the time your code is executing, in 100ms intervals. This means that for simple functions that handle small amounts of data with very bursty traffic it can work out quite affordable while still having the capacity to handle spikes in traffic. This model makes Lambda an ideal platform to build an application for receiving SMS messages.

Logging Incoming SMS with AWS Lambda

For our demo, we will be writing the application in Python. Lambda allows you to use whatever third-party libraries you wish as part of your application bundle. For the first version, we are simply going to receive an SMS sent to a Nexmo number and log that message in the Lambda logs. The video below will walk you through creating your first Lambda application and setting it up to receive \[webhooks]( from the Nexmo API. You will also need a \[Nexmo account](${CUSTOMER_DASHBOARD_URL}/sign-up?icid=tryitfree_api-developer-adp_nexmodashbdfreetrialsignup_nav) and you will need to buy a number for this.

You can find out more about getting started with AWS Lambda in their getting started guide.

Here's a walk-through of the key points from the video:

import json

print('Loading function')

def lambda_handler(event, context):
print("Received SMS: " + json.dumps(event, indent=2))
return "OK"

Lambda is configured to call a function named lambda_handler and passes it an object called event. This event contains the parameters that we will map through on the API Gateway Integrations Request

"type" : "$input.params('type')",
"to" : "$input.params('to')",
"msisdn" : "$input.params('msisdn')",
"messageId" : "$input.params('messageId')",
"message-timestamp" : "$input.params('message-timestamp')",
"text" : "$input.params('text')"

The lambda_handler will simply take that event data and print it to the log as a JSON object, then it will return an OK string which will pass through the API gateway and be returned to Nexmo.

Setting Up Google Sheets

The example above is pretty basic, all we are doing is logging the message to the Lambda logfiles, for a real world example we need to do something a bit more useful.

Let’s take a scenario of an event where you want attendees to be able to provide feedback quickly via SMS. A great way to store and share that information is in Google Sheets and lucky for us there’s an API that will allow us to write directly to the sheet.

In the previous video, we start in the Google developers console (get an account here) where you need to create a new project, enable the Drive API for that project and then setup a Service Account Key. This type of access is designed for server to server applications. This is what we'll be using as the Lambda application will connect to Google Docs.

Once that service account key is created you will be provided with a set of credentials downloaded in a JSON file. One of the parameters in there is called client_email, this will be in the form of an email address. Make a note of this address.

Login to your Google Drive and create a new spreadsheet called nexmosms. You need to share it with the address related to the Service Account Key and give edit permissions. Now Lambda is acting like another user that is collaborating on the doc with you.

Adding SMS Messages to a Google Sheet

Now that we are working with some external API’s our Lambda code needs to be little more complex. We'll now be using some 3rd party libraries which means that we can no longer just write our code in the browser. Instead we need to create a zipped bundle of our code and the additional libraries we are using.

We skip the video for these coding steps. Instead the detailed steps are below. We'll start off with an empty directory and create a file in there called

mkdir nexmosms cd nexmosms touch

We can install the libraries that we're going to use in our bundle from the command line using the pip package manager. We need to install them to the local folder rather than the regular system path using the -t flag and can specify the current directory pwd in backticks. So to add the nexmo library to the package use the command:

pip install nexmo -t `pwd`

We’ll need to repeat that command for the other libraries we want to use:

pip install oauth2client -t `pwd` pip install gspread -t `pwd` pip install nexmo -t `pwd`

Now that we have a folder with our libraries in it we just need to use them in our file:

Our lambda function code is now a little more detailed:

import json
import requests
from time import strftime as timestamp
from oauth2client.service_account import ServiceAccountCredentials
import gspread
import nexmo
from creds import *

The first part imports the libraries we need, but you’ll also notice that there is some stuff in there like time which we didn’t include in our bundle with pip. This is because Lambda provides everything from Python 2.7 standard runtime.

Next we need to set up access to Google Sheets and also put our Nexmo API Key and API Secret into the file. In the code below replace the X's with your details in the example below.

# Setup access to Google sheets
scopes = ['']
credentials = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scopes=scopes)
#Nexmo Credentials
nexmo_key = 'XXXXXX'
nexmo_secret = 'XXXXXX"

The credentials file that you downloaded when setting up the Google Service Account key should be renamed to creds.json and included in the bundle.

And add a function called addrow to our bundle that lets us add a row to our Google Sheet:

def addrow(sender, text):
gc = gspread.authorize(credentials)
sheet ='nexmosms').worksheet("Sheet1")
sheet.append_row([timestamp('%Y-%m-%d %H:%M:%S'), sender, text])

This function is passed 2 values, sender which will be the mobile number (MSISDN) that sent the message and text which is the body of the message. The function creates a gc object to represent the connection to Google Sheets, a sheet object which will open a doc in that account called nexmosms and select the sheet called Sheet1. Finally we append a new row to that sheet adding a timestamp as the first column, followed by the sender and message text.

We can then update our original Lambda handler to make use of our new function:

def lambda_handler(event, context):
print("Received SMS: " + json.dumps(event, indent=2))
addrow(event['msisdn'], event['text'])
client = nexmo.Client(key=nexmo_key, secret=nexmo_secret)
client.send_message({'from': event['to'], 'to': event['msisdn'], 'text': 'Thanks for your feedback!'})
return "OK"

In the above code we still print the received data to the log to help with debugging, but we now also invoke the addrow function we just created.

We then use the official Nexmo Python library (that we released just in time for PyCon 2016) to send back a response to the originator of the message from the Lambda function. We create a nexmo.Client instance and call client.send_message setting our from number to be the Nexmo number that the user sent the message to. The ‘to’ is the user’s phone number that they sent the message from (MSISDN) and the body of the message text just says Thanks for your feedback!. Finally we return an "OK" to the Nexmo API to clear down the request.

That's it! You can now send an SMS to a Nexmo registered number, and have that text message sent to an AWS Lambda function via a Nexmo webhook. Lambda will log the message contents to a Google Sheet and send a reply.

The following video shows uploading the bundle and a demonstration of the code in action:

Grab the Code!

You can download a bundle of all the code from my GitHub. Please let me know what you think, I'm @sammachin on Twitter.

Sam MachinVonage Alumni

Sam Machin has nearly 20 years of experience in the communications industry and a strong track record of innovation. His personal projects mostly revolve around Amazon Alexa, Sam created the AlexaPi project allowing developers to build thier own Alexa powered devices and he is recognised by Amazon as an Alexa Champion.

Ready to start building?

Experience seamless connectivity, real-time messaging, and crystal-clear voice and video calls-all at your fingertips.

Subscribe to Our Developer Newsletter

Subscribe to our monthly newsletter to receive our latest updates on tutorials, releases, and events. No spam.