CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Leilani
Leilani

Reputation: 33

How to stop an extra row from being added when copying a row from one spreadsheet to another

I have a script that copies some of the data from one spreadsheet to another based on a "y" being placed in column AK. Coping the data itself works just fine.

However, when it does it adds an extra row to the destination spreadsheet. I have tried multiple manipulations with the script below but can not figure out why I keep getting an extra row. Would love to understand where I am going wrong. 

  function myFunction(e) {
  var sourceSheetName = "Jims Calendar with Accounting";
  var destinationSpreadsheetId = '1dSJOOw_oLVc6Nf-C0MY9715tZGuh69kR6-j_m8EJ-So';
  var destinationSheetName = "Unprocessed Acct Data";

  if (e.source.getSheetName() == sourceSheetName && e.range.getColumn() == 37 && e.value.toUpperCase() == "Y") {
  var row = e.range.getRow();
  var sourceValues = e.source.getRange("B" + row + ":AK" + row).getValues()[0];
  var values = [sourceValues.slice(0, 35)];
  values[0].unshift("JT");
  Logger.log(values[0])
  var tss = SpreadsheetApp.openById(destinationSpreadsheetId);
  var ts = tss.getSheetByName(destinationSheetName);
  ts.getRange(ts.getLastRow()+1 , 2 , values.length, values[0].length).setValues(values);
  e.range.setValue("Copied");
}
}

Blank row every time

Upvotes: 0

Views: 93

Answers (1)

Cooper
Cooper

Reputation: 64062

This is just about the same code. It appends a row to the destination sheet. Do you want it to append or overwrite. It doesn't add any rows beyond the one that's appended.

function MyonEdit(e){
  if(e.range.getSheet().getName()=='Sheet19' && e.range.getColumn()==37 && e.value.toUpperCase()=='Y'){
    var row=e.range.getRow();
    var vA=e.range.getSheet().getRange(row,1,1,35).getValues();
    vA[0].splice(0,0,'JT');
    var ss=SpreadsheetApp.openById('SSID');
    var sh=ss.getSheetByName('Sheet2');
    sh.getRange(sh.getLastRow()+1,2,vA.length,vA[0].length).setValues(vA);
    e.range.setValue('Copied')  
  }
}

I am not getting an extra row.

Here's what the destination sheet looks like after three executions.

enter image description here

Upvotes: 1

Related Questions