RkauffMy wife Teaches 5th grade, and is currently e-Teaching from home due to Covid-19. They use lots of Google Sheets for various assignments. She needed a way for her students to let her know they need help. She can’t monitor them all simultaneously, so I used some Google Apps Script, Twilio, AWS API and AWS Lambda to help. The students click on a button in a Google sheet if they need help, and she gets a text message letting her know.
Goal:
This is actually really easy to build, so I want to show you how I did it, in order for you to recreate yourself, or for any of the Teachers in your life.
Tech Stack:
Twilio SMS: To send a text message.
Google Sheets: What the Students see, the User Interface.
Google Apps Script: The "trigger" that will execute this app.
AWS API: The "front door" to the cloud that connects the Google Sheet to the Lambda.
AWS Lambda: The "logic", also known as the serverless environment to hold our Python code.
Python version 3.8: The language we will write our code in.
Create a Twilio account and select your phone number
The first thing to do is sign up for a Twilio account. Sign up for free using this link and receive a $10 account credit. Once you've signed up, buy a phone number. Make a note of the phone number you’ve bought, your Account SID, and your Auth Token. You’ll need them all later.
Create an AWS Account
Even if you already have an Amazon account or Prime membership, you will need to create a new AWS login, because it is separate from the rest of Amazon.
Once you've created your AWS account, you will need to create a Lambda function. Don't worry if you've never done this before, it's actually pretty simple. Just go to the Lambda service, and click on "Create Function."
- Select "Author From Scratch"
- Type a "Function Name" (this is whatever you want to call your function, it can be anything)
- Enter a "Runtime" (I used Python 3.8, but any 3.x will work)
- Go to "Permissions" and click "Create a new role with basic Lambda permissions." This lets AWS set it up for you, which is a good idea, unless you're an experienced user.
- Click on "Create Function" on the bottom right hand side of the page.
This will bring you into the Lambda configuration. Paste the code below to your Lambda. You can also find this code on Github:
#import the twilio module
from twilio.rest import Client
#define your lambda function
def lambda_handler(event, context):
#enter your account details here
account_sid = "paste your twilio account SID here"
auth_token = "paste your twilio auth token here"
client = Client(account_sid, auth_token)
message = client.messages.create(
to="+The phone number you want to send the message to here",
from_="+The twilio phone number you bought earlier here",
body="Mrs. Kauffman, I need some help.")
It should look like this when you're done:
Make sure to add your own Twilio Account SID, Twilio Auth Token, Twilio phone number that you bought earlier, and the phone number that you want the text message to go to in your code.
Click "Save" in the top right hand corner. Your Lambda is done, but there is one more thing you need to do here. Since we are editing the code inline, we need to handle the Twilio dependency. There are essentially two main ways to use Lambda. We could build our own zip file, details on how to do that here. Alternatively, we can write the code inline, and use Lambda Layers for dependencies. For simplicity, we are going to Layer our Lambda function.
Click on "Layers" below "kristins-lambda-function" in my example. Then click "Add a layer."
Click "Provide a layer version ARN" and paste Keith Rozario's Twilio layer:
aws_lambda_us-west-2_770693421928_layer_Klayers-python38-twilio_7
This ARN is specific to the us-west-2 region, so if you have difficulty here, double check that you're region in the "Oregon" region.
Click "Add."
Congrats! Your Lambda function is ready to go!
Now we need a way to trigger the Lambda function. That's where the AWS API Gateway comes in.
In "Services", find the "API Gateway," then click "Create API."
Scroll down to find "REST API," and click "Build."
From here, select "REST," "New API," Give your API a name, Description, and use "Regional" for Endpoint Type, and click "Create API."
Next, you need to create a method, so under the Actions dropdown menu, click on "Create Method," then use "Post," and then click the check mark.
In the "Post" method, you want to select "Lambda Integration," your region (I'm using US-West-2 for this tutorial), and the ARN (Amazon Resource Number), or the name of the Lambda Function you just created. Either way works. I'm using the name of my Lambda function here. Hit Save.
It will give you a warning: "You are about to give API Gateway permission to invoke your Lambda function:"
Click OK.
It should look like this:
Nice work! You just built your API, now you just need to Deploy it. You do this by clicking on "Actions", then "Deploy API." You can use "New Stage" as your Deployment Stage, then come up with a Stage Name (I usually call it "Prod", but feel free to call it anything you want, it's your API). Then hit "Deploy."
Awesome, your API is live!
The only thing left to do is build your trigger in the Teacher's Google Sheet.
In the Google Sheet, click on "Tools," then "Script Editor."
Paste this simple function into your Google App Script Editor. You will need to change it to be the API endpoint you just created, otherwise your Google sheet will be trying to call my API.
function doPost() {
UrlFetchApp.fetch("https://git4xk3aif.execute-api.us-west-
2.amazonaws.com/Prod/kristins-lambda-function", {muteHttpExceptions:
true});
}
It should look something like this (modified with your API):
Save your script by hitting Ctrl + S. Then you can close out of the script editor.
In your Google Sheet, you need a button that will serve as the trigger for your students to press, in case they need your assistance.
And then upload any picture you like. I've used one of my favorite pictures of my wife.
Way to go, you just inserted a picture into your Google Sheet. Now you need to turn that picture into a button that does something.
You can do this by right-clicking anywhere in the image, and then left-clicking in the top right hand corner of the picture (where the red arrow is in the image below). Then click on "Assign Script," and assign the script that you created in your Google Script above.
Type "doPost" in the box, and click OK.
The first time you click your new button, you will most likely be prompted to give your Google script access, details on how to do that are here. It's pretty straightforward.
Now, when her students click anywhere on that picture, she receives this text message from Twilio.
You're done!
If you have any questions, feel free to reach out to me on Twitter @ryankauffman80, LinkedIn, or send me an email at koontz2k4@gmail.com.
I'm a Twilio Champion, so if you want to check out any of the blog posts I've written for the Twilio blog, you can see them here! We can't wait to see what you build!
