semi rookie here

I'm trying to run a script to copy a row from one spreadsheet to another - it works initially, then it comes up with a TypeError.

TypeError: Cannot read property 'length' of undefined copyrange1 @ onedit destination.gs:26 onEdit @ onedit destination.gs:2

I'm looking to multiple functions with three destination sheets based on the value in cell AQ then for it to delete the row from the source sheet.

Any suggestions?

function onEdit(){
  copyrange1();
  copyrange2();
  copyrange3();
}

function copyrange1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Position Activity'); //source sheet
  var testrange = sheet.getRange('AQ:AQ'); //range to check
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('Suspended Pending'); //destination sheet
  var data = [];
  var j =[];

  //Condition check in AQ:AQ; If true copy the same row to data array
for (i=0; i<testvalue.length;i  ) {
  if ( testvalue[i] == 'Suspended - Pending') {
  data.push.apply(data,sheet.getRange(i 1,1,1,43).getValues());
  //Copy matched ROW numbers to j
  j.push(i);
 }
 }
//Copy data array to destination sheet

 csh.getRange(csh.getLastRow() 1,1,data.length,data[0].length).setValues(data);

//Delete matched rows in the source sheet
  for (i=0;i<j.length;i  ){
  var k = j[i] 1;
  sheet.deleteRow(k);

//Alter j to account for deleted rows
  if (!(i == j.length-1)) {
  j[i 1] = j[i 1]-i-1;
}
}
} 

Thanks in advance

CodePudding user response：

Issue:

• data will only be populated if there are rows in AQ:AQ in which the cell value is 'Suspended - Pending'.
• Since the script is deleting the rows where the cell value is 'Suspended - Pending' after copying them, data will remain an empty array [] in successive executions.
• If data is empty, data[0] is undefined, so it doesn't have a length.

Solution:

Make sure that data is populated before using csh.getRange(...).setValues(data);.

function copyrange1() {
  // ...stuff...
  if (data.length) {
    csh.getRange(csh.getLastRow() 1,1,data.length,data[0].length).setValues(data);
    // ...more stuff...
  }
}