Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 8. Google Script

Michael Demesa 0
Viewed 19

I am having troubles in

Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 8. Google Script

function myFunction() {
    var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Staging');
  var s2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Final');
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var Avals = s2.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;

  var resultArray = [];
  for(var n=0; n < values1.length ; n++)
  {
    var keep = false;
    var counter = 0;
    for(var p=0; p < values2.length ; p++)
    {
      if( values1[n][1] == values2[p][1])
      {
        keep = true;
        break ;
      }
    }
    if(keep == false)
    {
      resultArray.push([values1[n]]);   
      //s2.appendRow( values1[n] );
      s2.getRange(Alast + 1, 1, values1[n].length, values1[n].length).setValues([values1[n]]);
      //s2.getRange(s2.getLastRow() + 1, 1, values1.length, values1[0].length).appendRow( values1[n] );
      resultArray = [];
      keep = false
    }  
  }
}
google-sheets google-apps-script
1 Answers

I believe you are trying to keep appending a new row to the end of the data on "Final". You should revise you script as shown below. Use resultArray to collect all new rows and save it to spreadsheet once.

function myFunction() {
  var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Staging');
  var s2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Final');
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  // s2.getDataRange() will get up to the last row of data
  var Alast = values2.length;

  var resultArray = [];
  for(var n=0; n < values1.length ; n++)
  {
    var keep = false;
    var counter = 0;
    for(var p=0; p < values2.length ; p++)
    {
      if( values1[n][1] == values2[p][1])
      {
        keep = true;
        break ;
      }
    }
    if(keep == false)
    {
      resultArray.push(values1[n]);   
      keep = false
    }  
  }
  if( resultArray.length > 0 ) {
    s2.getRange(Alast+1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
  }
}
TheWizEd0
Related