Not properly referencing cells

Question

I am coding a phone number formatter for a large database. Everything is working, but there is an inconsistent printing of blank cells. Most of the cells are read through and are properly formatted, but there are some that show blank outputs in the wrong cells.

I have tried fixing this by resetting the cleanNumber variable to a blank string but this just posed another issue on line 33 with indexOf().

function myFunction() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // connects sheet to code
  var startRow = 18;
  var endRow = 41;
  for (var i = startRow; i <= endRow; i++) {     // i = currnet row | row to end at | add 1 to count each time
    var workingCell = activeSheet.getRange(i, 2).getValue();
    Logger.log("Original number: " + workingCell)
    //If blank, move to next row
    exit: if (workingCell.length == 0.0) {
      var blank = "";
      activeSheet.getRange(i, 3).setValue(blank);
      Logger.log("This row is blank")
      //break exit;
    }
    // cleanNumber if it isn't formatted already 
    else if (isNaN(workingCell)) { // runs if active cell is not a preformatted number 
      var cleanNumber = workingCell.replace(/\D/g, '');  // removes all non-numeric values
      activeSheet.getRange(i, 3).setValue(cleanNumber);
      Logger.log("Extra char's removed: " + cleanNumber)
    }
    // runs if active cell is already preformatted 
    else {
      activeSheet.getRange(i, 3).setValue(workingCell);
      Logger.log("No need for formatting: " + workingCell)
    }
    // If cleanNumber has a country code(+1), remove it
    if ((cleanNumber.indexOf("1")) == 0) {
      cleanNumber = cleanNumber.substring(1); //removes first character = "1"
      activeSheet.getRange(i, 3).setValue(cleanNumber);
      Logger.log("Country code removed: " + cleanNumber);
    }
    // If number is longer than 10 characters, create an extension variable - with entire number, remove 10 characters from front
    if (cleanNumber.length > 10.0) {
      var extension = cleanNumber.substring(10, 15);
      var phoneNumber = cleanNumber.substring(0, 10);
      var formatted = phoneNumber.slice(0, 3) + "-" + phoneNumber.slice(3, 6) + "-" + phoneNumber.slice(6, 15);
      var finalPhoneNumber = formatted + " ext. " + extension;
      activeSheet.getRange(i, 3).setValue(finalPhoneNumber);
      Logger.log("This number is in its final ext. format: " + finalPhoneNumber);
    }
    //if number doesnt have an extension, put it into final format
    else if (cleanNumber.length = 10.0) {
      var frontFinal = cleanNumber.substring(0, 3);
      var midFinal = cleanNumber.substring(3, 6);
      var endFinal = cleanNumber.substring(6, 10);
      var finalNumber = frontFinal + "-" + midFinal + "-" + endFinal;

      activeSheet.getRange(i, 3).setValue(finalNumber);
      Logger.log("This number is in its final format: " + finalNumber);
    }
    //if number is less than 10 numbers
    else {
      Logger.log("This number is shorter than 10 numbers" + cleanNumber);
    }
    cleanNumber = " ";
  }
}

The pre-formatted numbers are on the left and the output is in the right column.

Here is some sample data, please consider that the issue seems to be stemming from blank rows.

Unformatted
1999-111-1111
1+2222-222222
4444444444 ext. 223
9738094395
9172609107
866.786.6682
973 330 2212
(631)563-4000 ext. 234

I look forward to solving this issue, thank you for the help :)

Answer 1

You can do it with ARRAYFORMULA or you may use the RegExp in your script.

=ArrayFormula(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(TO_TEXT(A2:A),"\D",),"^(?:1)?(\d{3})(\d{3})(\d{4})(\d{0,5}).*$","$1-$2-$3 ext. $4")," ext\. $",))

---

You are recommended to use batch operations

const values = [
  ['1999-111-1111'],
  ['1+2222-222222'],
  ['4444444444 ext. 223'],
  [9738094395],
  [9172609107],
  ['866.786.6682'],
  ['973 330 2212'],
  ['(631)563-4000 ext. 234'],
  ['973-809-4395'],
  ['']
];

const results = [];
for (const value of values) {
  const cleanNumber  = value[0].toString().replace(/\D/g, '');
  const m = cleanNumber.match(/^(?:1)?(\d{3})(\d{3})(\d{4})(\d{0,5}).*$/);
  if (m) {
    let finalNumber = `${m[1]}-${m[2]}-${m[3]}`;
    if (m[4]) { finalNumber += ` ext. ${m[4]}`; }
    results.push([finalNumber]);
  }
  else {
    results.push(value);
  }
}
console.log(results.flat());