Friday, 17 March, 2023 UTC
Summary
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:
| Name | Cell Phone | Formatted Phone Number | Valid |
|---|---|---|---|
| Peter Parker | (407) 224-1783 | ||
| Thor Odinson | 9 100000-8467 | ||
| T'Challa | 9252632-3673837 | ||
| Xu Shang-Chi | 86 139 1099 8888 | ||
| Steve Rogers | 678-136-7092 | ||
| Tony Stark | 212-970-4133 | ||
| Natalia Romanova | +79123456789 | ||
| Groot | 4-26-47668 | ||
| Jennifer Walters | 1 (877) SHE-HULK |
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:
- Functions are hosted on Twilio for easier management
- You can deploy the Lookup function quickly using CodeExchange
- 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:// column numbers for relevant data // change to suit your spreadsheet let PHONE_NUMBER = 1; let FORMATTED_PHONE_NUMBER = 2; let IS_VALID = 3; // get this URL by deploying a project here: https://twilio.com/code-exchange/lookup let lookupUrl = "<code exchange URL here>" function validate(row) { try { let resp = UrlFetchApp.fetch(lookupUrl, { method: 'post', payload: { phone: row[PHONE_NUMBER].toString(), }, }) let { phoneNumber } = JSON.parse(resp.getContentText()); row[FORMATTED_PHONE_NUMBER] = phoneNumber; row[IS_VALID] = true; } catch (err) { console.error(err); row[IS_VALID] = false; } }; function validateAll() { var sheet = SpreadsheetApp.getActiveSheet(); var rows = sheet.getDataRange().getValues(); var headers = rows.shift(); rows.forEach(function(row) { validate(row); }); sheet.getRange(2, 1, rows.length, headers.length).setValues(rows); } function onOpen() { SpreadsheetApp.getUi().createMenu('Validate Phone Numbers') .addItem('Validate all', 'validateAll') .addToUi(); }; 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
Run the validation script from your sheet 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.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.