How To Validate and Look Up Phone Numbers in Google Spreadsheets

Time to read: 6 minutes

March 09, 2016
Written by
Greg Baugues
Twilion

phone-numbers-verified

In ten minutes, you can teach your spreadsheet some new tricks, even if you’ve never written a line of code. We’ll use Google Spreadsheet and Twilio Phone Number Lookup API to validate, clean up and format phone numbers easily.

The Twilio Lookup API can be used to: 

  • Tell if phone number a cellphone or landline.
  • Tell if a phone number is real or fake
  • Clean up incorrectly formatted phone numbers
  • Properly format a list of phone numbers

The Twilio Bits

If you don’t have a Twilio account, sign up here. A free trial account gives you $20 in Twilio credit to play with. At $0.005 per lookup, you can look up 4,000 phone numbers before upgrading your account. 

Once you’re signed-in, go to your dashboard and click Show API Credentials. Keep your credentials handy — we’ll be pasting them into our code in a minute.

show-credentials

The Google Bits

Go to Google Drive and create a new spreadsheet. Create column headings for:

  • Phone Number – this is the column you fill out.
  • Status – is the phone number is real (found), or fake (not found)? 
  • Phone Type – is this a cell phone number? Can it receive SMS? (if this is interesting to you, check out this tutorial on sending SMS with Google Spreadsheets). 
  • Carrier – Which mobile or landline carrier operates this phone? 
  • Country Code – Is this an international phone number? If so, what country it from? 
  • National Format – What is the proper formatting of this phone number? 

Select all of the cells in your spreadsheet and format them as “Plain Text”.

plain-text

Add a few phone numbers you want to look up. You can enter these numbers in pretty much any format you’d like. One of the powerful uses of the Lookup API is cleaning up a list of improperly formatted numbers. 

phone-numbers

In the top menu bar, click Tools -> Script Editor to open the code editor. 

The rest of this post walks you through the code to look up and validate the phone numbers. If you’re the kind of person that likes to skip to the end of the mystery novel, you can copy in this code for the final product (take note of the placeholders for ACCOUNTSID  and AUTHTOKEN from your dashboard):

function lookup(phoneNumber) {
    var lookupUrl = "https://lookups.twilio.com/v1/PhoneNumbers/" + phoneNumber + "?Type=carrier"; 
    
    var options = {
        "method" : "get"
    };

    options.headers = {    
        "Authorization" : "Basic " + Utilities.base64Encode("ACCOUNTSID:AUTHTOKEN")
    };
  
    var response = UrlFetchApp.fetch(lookupUrl, options);
    var data = JSON.parse(response); 
    Logger.log(data); 
    return data; 
}

function lookupAll() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; 
    var numRows = sheet.getLastRow() - 1; 
    var dataRange = sheet.getRange(startRow, 1, numRows); 
    var phoneNumbers = dataRange.getValues();
  
    for (var i in phoneNumbers) {
        var phoneNumber = phoneNumbers[i]; 
        var spreadsheetRow = startRow + Number(i); 
        sheet.getRange(spreadsheetRow, 2, spreadsheetRow, 6).setValue("");
        if (phoneNumber != "") { 
            try { 
                data = lookup(phoneNumber);
                if (data['status'] == 404) { 
                    sheet.getRange(spreadsheetRow, 2).setValue("not found");
                } else {
                    sheet.getRange(spreadsheetRow, 2).setValue("found");
                    sheet.getRange(spreadsheetRow, 3).setValue(data['carrier']['type']);
                    sheet.getRange(spreadsheetRow, 4).setValue(data['carrier']['name']);
                    sheet.getRange(spreadsheetRow, 5).setValue(data['country_code']);
                    sheet.getRange(spreadsheetRow, 6).setValue(data['national_format']);
                }  
            } catch(err) {
                Logger.log(err);
                sheet.getRange(spreadsheetRow, 2).setValue('lookup error');
            }
        }
    }
}

function myFunction() {
    lookupAll(); 
}

If you want to see how we arrived at that result, carry on.

Look Up a Phone Number With Apps Script

We’ll write this code in two steps:

  1. Write a lookup function to look up a single phone number.
  2. Write a lookupAll function to run lookup on every phone number in the spreadsheet.

Replace the two lines of code in your script with: 

function lookup(phoneNumber) {

}


function myFunction() {
    lookup('3123131434');
}

Replace that 3123131434  with your cellphone number.

The job of lookup  is to make an HTTP POST request to the Twilio Lookup API. You don’t need to know what that means to complete this tutorial, but you can think of it as submitting a “Lookup Phone Number” form with a single field of “Phone Number”.

All the code in this step goes inside the lookup function. Define the URL for the phone number lookup API. 

var lookupUrl = "https://lookups.twilio.com/v1/PhoneNumbers/" + phoneNumber + "?Type=carrier";

Create an options hash to tell Apps Script that this is a GET request:

var options = {
    "method" : "get",
};

Add headers to options to authorize the request with your Account SID and Auth Token from the dashboard:

options.headers = {    
    "Authorization" : "Basic " + Utilities.base64Encode("YOURACCOUNTSID:YOURAUTHTOKEN")
};

Finally, execute your HTTP request and log the result: 

var response = UrlFetchApp.fetch(lookupUrl, options);
var data = JSON.parse(response);
Logger.log(data);
return data;

Save your file and run your script. You’ll be prompted to authorize your app the first time you run it. Click View->Logs to see the data from the request. 

Look Up Phone Numbers from the Spreadsheet

It’s not terribly practical to hard code phone numbers into our script. Let’s add a function to read the numbers from the spreadsheet, pass that data to lookup, then update our spreadsheet with what we discovered. 

Replace myFunction with: 

function myFunction() {
    lookupAll();
}

Immediately above myFunction  (and below lookup ),  create the lookupAll function. The rest of the code goes here. 

function lookupApp() {

}

Fetch the data from your spreadsheet:

function lookupAll() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;
    var numRows = sheet.getLastRow() - 1;
    var dataRange = sheet.getRange(startRow, 1, numRows);
    var phoneNumbers = dataRange.getValues();

}

Then we:

  • iterate through the data
  • if it’s not not blank, try to lookup the phone number
  • if the phone number does not exist, update the “Lookup Status” column with a “not found”  
  • If the phone number does exist, update the remaining columns with the data returned from the lookup API
  • if an error occurs along the way, log the error and update the status column (you can view your logs under the Tools menu in the Script Editor)

for (var i in phoneNumbers) {
    var phoneNumber = phoneNumbers[i];
    var spreadsheetRow = startRow + Number(i);
    sheet.getRange(spreadsheetRow, 2, spreadsheetRow, 6).setValue("");
    if (phoneNumber != "") {
        try {
            data = lookup(phoneNumber);
            if (data['status'] == 404) {
                sheet.getRange(spreadsheetRow, 2).setValue("not found");
            } else {
                sheet.getRange(spreadsheetRow, 2).setValue("found");
                sheet.getRange(spreadsheetRow, 3).setValue(data['carrier']['type']);
                sheet.getRange(spreadsheetRow, 4).setValue(data['carrier']['name']);
                sheet.getRange(spreadsheetRow, 5).setValue(data['country_code']);
                sheet.getRange(spreadsheetRow, 6).setValue(data['national_format']);
            }  
        } catch(err) {
            Logger.log(err);
            sheet.getRange(spreadsheetRow, 2).setValue('lookup error');
        }
    }
}

Run your app again! You’ll need to reauthorize it since it’s now trying to access your spreadsheet as well.

phone-numbers-verified

Onward

Google Sheets and Apps Script provide a surprising amount of functionality for a programming language that requires no installation or configuration. If you found this useful, you may want to check out our tutorial on how to send a text message from a Google Spreadsheet. With that, you could text the number that you just discovered is a cellphone.

If you’d like to learn more about the Lookup API and how to send text messages, check out these docs:

If you have any questions or come up with any cool use cases for looking up  phone numbers, I’d love to hear about it. You can find me at gb@twilio.com or @greggyb.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.