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.

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 – 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”.

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.

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):