google-apps-scriptgoogle-sheets-macros

Google Sheet Script: resetting active cell with getActiveSheet()


I'm trying to copy a range of values from a sheet (Test1) and paste them into the active cell of the next sheet (Test2).

The problem I am having is that the active cell keeps getting reset back to A1 instead the last cell I clicked on that page.

When I run the script from the second page (Test2), everything works fine, but I want to be able to run it from sheet Test1 since that is where the data is being copied from and my goal is to have a button to run the script.

With setActiveSheet(Sheet sheet, Bool restoreSelection) - the restoreSelection set to true is supposed to ensure the most recent selection is selected again, but it doesn't seem to be working.

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var s1 = ss.getSheetByName('Test1');
  var s2 = ss.getSheetByName('Test2');
  var r1 = s1.getRange('A7:A19');
  ss.setActiveSheet(s2, true);
  var activeCell = s2.getActiveCell();
r1.copyTo(activeCell,SpreadsheetApp.CopyPasteType.PASTE_VALUES,true);
}

Solution

  • Is your goal to append something to Test2? In that case you can approach this easily by finding the lastRow using getLastRow() of Test2 and then appending it.