Format phone number using google app scripts

Have you ever had problems when using google forms and unable to format number?

When you use google from and get a response from that user, it can’t validate and needs to reformat later. I will use google app script to format the desired format in just one click.

1.I will add a custom function button on the menu. This function will run when the google sheet is opened. so, I want to format data => like this xxx-xxx-xxxx

//run when open Spreadsheet 


  function onOpen(){
     initMenu();
}

//Add Custom Function to Menu Bar
function initMenu(){
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu("myCustomFucntion");
  menu.addItem("Format Phone", "formatNumber");
  menu.addToUi();
}

2. Write a code formatting number

function formatNumber() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  let lastRow = ss.getLastRow();
  let numOld = ss.getRange(2, 2,lastRow-1,1).getValues();

  let newnum = []

  //delete "-" in phone number and push in newnum array
  //Or use other REGEX in replace, it's up to you.
  numOld.map(data => {
    newnum.push(data[0].toString().replace(/[-]/g, ''))
  })
  
  //push "-" in after position 3 in text and then join with ''
  let pushdatanum = newnum.map(d => {
    return [d.slice(0, 3), '-', d.slice(3)].join('');
  })
  
  //push "-" in after position 7 in text and then join with ''
  let pushdatanum2 = pushdatanum.map(d => {
    return [[d.slice(0, 7), '-', d.slice(7)].join('')];
  })
  

  ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  
  //insert back to Sheet1 begin at row2 col3, numRow from pushdatanum2.lenght, numColum from pushdatanum2[0]. 
  //length
  ss.getRange(2, 3,pushdatanum2.length, pushdatanum2[0].length).setValues(pushdatanum2);

  return false
}

3. You can press on myCustomFucntion and when the function formatNumber is pressed, the Number in your selected cell will be formatted as desired.

summary

Google app script can simplify the process by customizing it in your own way.I hope you find it useful. You may use it to format your response by using googleform or other information that you have on hand.