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

It would be helpful if you could put the unformatted numbers in a table. — Cooper, Aug 13 '21 at 18:35
Yes but I can't copy and paste so I'm not even going to attempt to debug it. — Cooper, Aug 13 '21 at 19:49
This is just a sample form for testing, the actual data is in a different sheet. In the official google sheet, there is over 600,000 phone numbers that need formatting and they are in this format. — abittar2022, Aug 13 '21 at 19:54
@Cooper I have created a table for you with some of the sample data. Please let me know if you need anything else. Just for reference, the issue seems to be happening once it reads a blank row. — abittar2022, Aug 13 '21 at 20:10
Maybe this will help: https://stackoverflow.com/questions/41323674/javascript-regex-to-format-phone-with-n-any-characters-for-extension — Cooper, Aug 13 '21 at 21:10
For us to easily debug your code, kindly share a sample spreadsheet. — Nikko J., Aug 13 '21 at 21:37

idfurw · Accepted Answer · 2021-08-14T01:38:44.710

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());

Not properly referencing cells

1 Answers1