How to get back end value of a cell showing #ERROR! using Google Apps Script?

Question

I had sync Google Contacts with Google Spreadsheet using Zapier. The problem i am facing is that the contacts with country code like [phone] when enters in spreadsheet cell, it shows #ERROR! because of space in number, when i remove the space manually it works well and doesn't shows the #ERROR!.

I had tried to get the value of the cell using Google Apps Script and remove the space from the contact number, but i am facing issue with getting the back end value of the cell showing #ERROR!. It gives me #ERROR! when i use getValues() or getDisplayValues() function of the Google Apps Script.

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CS2");
  var lr = ss.getLastRow();
  var data = ss.getRange("E2:E" + lr).getValues();
  for(var i = 0; i < data.length; i++)
  {
     Logger.log(data[i].toString());  
  }

When i double click the cell with #ERROR!, it shows =6018 2888184, i want to get this value using apps script code. What i am getting instead is #ERROR!.

Answer 1

You could use getFormulas() to get rid of the errors.

Try replacing this

  var data = ss.getRange("E2:E" + lr).getValues();
  for(var i = 0; i < data.length; i++)
  {
     Logger.log(data[i].toString());  
  }

with this

  var data = ss.getRange("E2:E" + lr).getFormulas();
  for(var i = 0; i < data.length; i++)
  {
     data[i][0] = data[i][0].replace(" ","");
  }
  ss.getRange("E2:E" + lr).setFormulas(data);