Move rows from one spreadsheet to another spreadsheet-CodePudding

I am trying to copy the last populated row from one spreadsheet to the first not populated row on another spreadsheet. This function works where the source and target sheet are from the same spreadsheet, but not if they are from different spreadsheets.

function moveRows() {

  // identifying source & target spreadsheets
  let ss = SpreadsheetApp.openById('1PugO3VL8ZkGzA6zcYfUkQK4WkU-FfRUBHUzRDTmEbYE');
  let target = SpreadsheetApp.openById('17my-WAAVkZK8m541dH50japQZYQY4WEC_vm12-fCKR8');
  let source_sheet = ss.getSheetByName('Sheet2');
  let target_sheet = target.getSheetByName('Sheet1');

  // identifying the ranges
  let row = source_sheet.getActiveRange().getRow();
  let activeRow = source_sheet.getRange( row ,1, 1 ,10);
  let last_row = target_sheet.getLastRow();

  // row below gives -> Exception: Target range and source range must be on the same spreadsheet.
  activeRow.copyTo(target_sheet.getRange('A' (last_row 1) ':Q' (last_row 1)));

}

CodePudding user response：

If you don't need to copy formatting, you can just set the values from the source to the next open row of the target.

Give this a try:

function moveRows() {
  let sourceSs = SpreadsheetApp.openById('1PugO3VL8ZkGzA6zcYfUkQK4WkU-FfRUBHUzRDTmEbYE');
  let targetSs = SpreadsheetApp.openById('17my-WAAVkZK8m541dH50japQZYQY4WEC_vm12-fCKR8');
  let sourceSheet = sourceSs.getSheetByName('Sheet2');
  let targetSheet = targetSs.getSheetByName('Sheet1');
  let sourceLastRow = sourceSheet.getLastRow();
  let sourceValues = sourceSheet.getRange(sourceLastRow   ':'   sourceLastRow).getValues();
  let targetNextRow = targetSheet.getLastRow()   1;
  targetSheet.getRange(targetNextRow   ':'   targetNextRow).setValues(sourceValues);
}

CodePudding user response：

Move Last Row of Source to next row of target:

function moveLastRow() {
  let ss = SpreadsheetApp.openById('1PugO3VL8ZkGzA6zcYfUkQK4WkU-FfRUBHUzRDTmEbYE');
  let tss = SpreadsheetApp.openById('17my-WAAVkZK8m541dH50japQZYQY4WEC_vm12-fCKR8');
  let ssh = ss.getSheetByName('Sheet2');
  const svs = ssh.getDataRange().getValues();
  let o = svs[svs.length -1];
  let tsh = tss.getSheetByName('Sheet1');
  const tvs = tsh.getDataRange().getValues() 
  tsh.getRange(tsh.getLastRow()   1, 1, 1, o.length).setValues([o]);
}