Friday, 17 March, 2023 UTC


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:
  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:
// 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: 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
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.