I recently wrote a code in order to send a particular row of data to another (destination) spreadsheet every time I voluntarily execute it. The destination spreadsheet has three sub-sheets:

1. NON JV
2. JV1
3. JV2

If a particular cell in the row I want to export, contains "NON JV" in it, the row is exported to the sub-sheet "NON JV" on destination spreadsheet, and so on. The code is:

function expo() 
{
  //Source link
  var ssraw = SpreadsheetApp.getActiveSpreadsheet();
  var sheetraw = ssraw.getSheetByName('FOREX');
  var range = sheetraw.getRange('A2:M2');
  var data = range.getValues();
  var datacheck=sheetraw.getRange('L2').getValue();

  if(datacheck=="JV1")
  {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1BkEAF-4wF0Ok9za_0Pgmlb9Odu3_fNgU7vx0AqVBsYw/edit#gid=1641519106');
  var reqsheet = ss.getSheetByName("JV1");
  
  reqsheet.getRange(reqsheet.getLastRow() 1,1,1,13).clear();
  reqsheet.getRange(reqsheet.getLastRow() 1,1,1,13).setValues(data);
  }
  else if(datacheck=="JV2")
  {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1BkEAF-4wF0Ok9za_0Pgmlb9Odu3_fNgU7vx0AqVBsYw/edit#gid=1056263168');
  var reqsheet = ss.getSheetByName("JV2");
  
  reqsheet.getRange(reqsheet.getLastRow() 1,1,1,13).clear();
  reqsheet.getRange(reqsheet.getLastRow() 1,1,1,13).setValues(data);
  } 
  else if(datacheck=="NON JV")
  {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1BkEAF-4wF0Ok9za_0Pgmlb9Odu3_fNgU7vx0AqVBsYw/edit#gid=0')
  var reqsheet= ss.getSheetByName("NON JV");

  reqsheet.getRange(reqsheet.getLastRow() 1,1,1,13).clear();
  reqsheet.getRange(reqsheet.getLastRow() 1,1,1,13).setValues(data);
  }
}
function getLastRow(sheet,rangeString)
{var rng=sheet.getRange(rangeString).getValues();
  var lrIndex;
  for(var i=rng.length-1;i>=0;i--)
  {lrIndex=i;
  if(!rng[i].every(function(c){return c==""})){break;}}
}

The code works perfectly for JV1 & JV2, and the row is exported. However, when the row contains "NON JV", the prompt says "finished script", but the row is nowhere to be seen on the destination subsheet "NON JV".

I am also attaching editable copies of the sheets here-

Source sheet:

Your code is actually working! But you're having issues when finding the final row. Delete all the unneeded rows and join your range. You can find other ways here. I suggest you use one of those ways and use ARRAYFORMULA for the columns with calculations.

Also, you can skip all those ifs statements and just call .getSheetByName(datacheck)

CodePudding user response：

Try this:

function expo() {
  const sss = SpreadsheetApp.getActive();
  const names = ["Sheet1", "Sheet2", "Sheet3"];
  const ssh = sss.getSheetByName('Sheet0');
  const row = ssh.getActiveRange().getRow();//You select the row
  const vs = ssh.getRange(row, 1, 1, 13).getValues()[0];
  const idx = names.indexOf(vs[11])
  let dss = SpreadsheetApp.openById(output ssid);
  let sh = dss.getSheetByName(names[idx]);
  sh.getRange(sh.getLastRow()   1, 1, 1, 13).setValues([vs]);
}

Data Sheet: