Validate phone numbers in a Google Sheet with Twilio Lookup

Validate phone numbers in a Google Sheet with Twilio Lookup

If you have phone numbers stored in a Google Sheet, you might want an easy way to validate that those numbers are real. Luckily the Twilio Lookup API has free formatting and validation functionality and can easily integrate with Google Sheets using Google's Apps Script and Twilio Functions.

Even if you've never used Twilio or an API before, if you're comfortable in Google Sheets then you're already a programmer in my book. I'm confident you can follow along and use this tutorial to add phone number validation to your Google sheet.

Prerequisites for validating phone numbers in Google Sheets

• A free Twilio Account - sign up or sign in
• A Google Sheet with a phone number column - here's a gist with some test data if you want to follow along

Add two empty columns to your spreadsheet: one for the formatted phone number and one for whether the phone number is valid. My example spreadsheet looks like this:

Create a Lookup function to call from Google Sheets

Instead of calling the Twilio API directly from Google Sheets, this tutorial uses Twilio Functions. This serves a few purposes:

1. Functions are hosted on Twilio for easier management
2. You can deploy the Lookup function quickly using CodeExchange
3. Functions protect your Twilio Credentials, so you don't have to worry about storing those in your Google Sheet.

Head over to the CodeExchange and deploy the Lookup project. Once the project is deployed, click on the "Go to live application" button. You'll be redirected to a handy interface where you can look up any phone number with optional data packages like line type or caller name information.

Keep this page open while we build the rest of the integration - you'll need the URL shortly.

Create an App Script in your Google Sheet

The next step is to use Google's built in Apps Script, based on JavaScript, to call out to your Lookup function from your spreadsheet.

Open your Google Sheet and navigate to Apps Script from the Extensions menu. It will open up a screen like this:

First, change "Untitled project" to something like "Phone Number Validation". Then replace all of the text that starts with function myFunction with the following:

Replace the placeholder Lookup URL on line 9 with the URL of your deployed function from the last step. Importantly, instead of /index.html, use /lookup to call the function that's doing the work behind the scenes. It should look like https://lookup-1234-47abcd.twil.io/lookup.

Here's what the code does:

The first handful of lines starting with let PHONE_NUMBER = … define the column indexes. These will be used to pull the right information to feed into our function and tell our App Script where to write the data we get back from our function. You may need to change these depending on what your data looks like, so if your phone number is stored in Column G, your variable would be let PHONE_NUMBER = 6. Note that the columns are zero-indexed.

The validate function is doing the bulk of the work to call the Lookup function and write the response back to the sheet. This includes the phone number in a standardized E.164 format and whether or not the phone number is valid. You could include other data from the Lookup API, check out the documentation to see what else is returned.

The validateAll function loops through all of the rows in the spreadsheet (assuming you have one header row) and runs the validate function on each row. Finally, onOpen will create a handy menu item so you can call this script from your spreadsheet directly.

Run the validation script from your sheet

Save the Apps Script (you do not need to deploy it) then go back to your sheet and refresh the page. It might take a moment but you will see a new menu item called "Validate Phone Numbers".

Click on "Validate Phone Numbers" then "Validate All". Validate any "Authorization Required" prompts to grant access to your sheet. The script will take a few seconds to run but you'll quickly see the validation information in the spreadsheet! Some members of my group chat seem to be operating in a different universe, but at least now I know.

Next steps for Google Sheets and Twilio

You can extend this project with other Lookup packages like line type intelligence. Learn more in the documentation about what else is possible with the Lookup API.

If you're collecting phone numbers in an application context, one way to prevent invalid phone numbers is to do phone verification - we also have an API for that!.

Now that you have better data integrity, you might want to send SMS notifications from Google Sheets or even log incoming messages in a Google Sheet. I can't wait to see what you build.