fixing my #ERROR in google sheets with google apps script

user_19280016 0
Viewed 57

I have a google app script program that looks throughout a spreadsheet and moves rows with empty values in column 5 into another sheet (block 3 of code).

However, I keep getting a #error upon transfer because in certain rows, some phone numbers have a + in front of them. I tried to turn the entire number into a string (block 2 of code with foreach loop), but the error remains.

function insertRecord() {
  var sheet_2 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var sheet_1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var rows = sheet_1.getDataRange().getValues();

  rows.forEach(function(element) {
    iterator=element.length;
    for (i=0;i<iterator+1;i++){
      if (typeof element[i]=== Number){
        element[i]=element[i].toString
      }
    }
  });

  rows
  .filter(row => row[5] == '') 
  .map(row_w_empty5col => sheet_2.appendRow(row_w_empty5col));

}
google-apps-script google-sheets
2 Answers

When I saw your script, at for (i=0;i<iterator+1;i++){, I thought that i<iterator+1 will be i<iterator. And, toString of element[i]=element[i].toString will be toString(). And also, when appendRow is used in a loop, the process cost will be high. Ref

And, about moves rows with empty values in column 5 into another sheet, in your script, .filter(row => row[5] == '') is used. In this case, the column 6 is checked. In order to check the column 5 (column "E"), it is row[4].

From your current issue and your goal, in your situation, how about copying the number format? When this is reflected in your script, it becomes as follows.

Modified script:

function insertRecord() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet_2 = ss.getSheets()[1];
  var sheet_1 = ss.getSheets()[0];
  var range = sheet_1.getDataRange();
  var rows = range.getValues();
  var f = range.getNumberFormats();
  var n = rows.reduce((ar, r, i) => {
    if (r[4].toString() == "") ar.push(i);
    return ar;
  }, []);
  var values = n.map(e => rows[e]);
  var formats = n.map(e => f[e]);
  sheet_2.getRange(sheet_2.getLastRow() + 1, 1, values.length, values[0].length).setValues(values).setNumberFormats(formats);
}

References:

user_71086530

Update: Changed my code a little bit, and it works now.

function emailEditor() {
  var app=SpreadsheetApp;
  var targetSht=app.getActiveSpreadsheet().getSheetByName('Linkedin Only');
  var spreadsheet=app.getActiveSpreadsheet().getSheets().length;
  for (i=0;i<spreadsheet;i++){
    var shts=app.getActiveSpreadsheet().getSheets()[i];
    var originalData=shts.getDataRange().getValues();
    var newData=originalData.filter(function(item){
      return item[5]===''; //user declared input here
      });
var begin=targetSht.getDataRange().getValues().length;
targetSht.getRange(begin+1,1,newData.length,newData[0].length).setValues(newData);
  }
}
user_192800160
Related