[GAS Library] Automatically Format Phone Numbers in Google Sheets by Country

‍

1. Overview

This is a Google Apps Script library for batch-formatting phone numbers in a Google Sheet to the appropriate format (e.g., with hyphens) based on their country.

‍

2. Functionality

The library reads phone number and country name data from specified columns in a sheet and writes the formatted phone numbers to a designated output column.

‍

3. Function

‍

formatSpreadsheet(sheetName, phoneCol, countryCol, outputCol, startRow)

‍

Formats the phone numbers within a spreadsheet.

Parameters:

‍

‍

Return Value:

• Integer: Returns the number of phone numbers processed.

Note on Country Names:

• Country names can be full names (e.g., Japan), abbreviations (e.g., US), or in Japanese (e.g., 日本).

How-to Guide

Step 1: Add the Library

1. Open the Google Apps Script editor. On the left-hand menu, click the + icon next to "Libraries."
2. In the "Script ID" field, paste the following ID and click "Look up."

1kL3K8a9qUz4yQWTNls0arDHy6bkhmtqeseWp4uYxXFjL9Fjy6ADR6VIU


1. For the "Version," select the latest one and set an "Identifier" of your choice (e.g., PhoneFormatter).
2. Click the "Add" button to add the library to your project.

Step 2: Create the Script

Use the sample code below, modifying the parameters to fit your sheet's layout.

JavaScript
function formatPhoneNumbers() {
// Configure the parameters 
// 1. Sheet Name: The name of the sheet containing the phone numbers.  
// 2. Phone Column: The column with the phone numbers (1 for A, 2 for B, etc.). 
// 3. Country Column: The column with the country names (1 for A, 2 for B, etc.). 
// 4. Output Column: The column where formatted numbers will be placed (1 for A, 2 for B, etc.).  
// 5. Start Row: The row number where your data begins (e.g., 1 if there's no header).  
var count = PhoneFormatter.formatSpreadsheet('Sheet1', 1, 2, 3, 1); 
// Log the number of processed items  
console.log(count + ' phone numbers were processed.');}

‍

Step 3: Run the Script

1. From the function dropdown menu, select formatPhoneNumbers.
2. Click the "Run" button.
3. The first time you run it, you will be asked to grant permission for the script to access your spreadsheet. Please authorize it.
4. Once execution is complete, the newly formatted phone numbers with hyphens will appear in the output column you specified.

‍