How to append data range to a list in a separate worksheet in Google Apps Script?-CodePudding

I've seen other approaches, but haven't been able to get anything working. I've been using a script similar to this to transfer a list from one TAB to another.

I thought I could just add in a different ID, set the variables, and then transfer it to a completely different WORKSHEET:

function archivesingle() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssd = SpreadsheetApp.openById('sheetid');
var idstoadd = ss.getActiveSheet();
var fulllist = ssd.getSheetByName("History");

Logger.log("Option#1 - Start")

// step 1 - get the last row on the target: Full List
var last_row = fulllist.getRange(1, 1, fulllist.getLastRow(), 1).getValues().filter(String).length;
Logger.log("Step#1 - the last row on fulllist = " last_row)

// step 2 - create a target range (or at least the top left corner of the target)
var targetrange = fulllist.getRange(last_row 1,1);
Logger.log("Step#2 - the target range will be " targetrange.getA1Notation())

// step 3 - create a source range
// how many header rows
var numheaderrows = 0
// so the range will be...
var sourcerange = idstoadd.getRange(1 numheaderrows, 14, idstoadd.getLastRow()-numheaderrows, 2);/// to pull in more columns, adjust last number
Logger.log("Stewp#3 - the sourcerange = " sourcerange.getA1Notation());

// step4 - copy from source to target
// method = range.copyto
// so copyto from the source range to the target range
sourcerange.copyTo(targetrange,{contentsOnly:true});
Logger.log("Step#4 - copied the source range to the target range and flushed");


// step 6 - apply all the pending changes.
SpreadsheetApp.flush();
Logger.log("Step#6 - Flushed the spreadsheet")
Logger.log("Option#1 - Completed")
return false;
}

I was hit with the "target range and source range must be on the same spreadsheet" error.

I've seen other approaches, but I'm new to this and don't really understand how they work.

Is there a way to tweak this in order to accomplish my task (append a range of data to a list of data hosted in a separate worksheet)?

Any insight is much appreciated.

CodePudding user response：

Try this:

function archivesingle() {
  const ss = SpreadsheetApp.getActive();
  const ssd = SpreadsheetApp.openById('sheetid');
  const ish = ss.getActiveSheet();
  const hsh = ssd.getSheetByName("History");
  let ivs = ish.getRange(1, 14, ish.getLastRow(), 2).getValues()
  hsh.getRange(hsh.getLastRow()   1,1,ivs.length,ivs[0].length).setValues(ivs);
}