Google-sheets – Google script unhide sheets while running

google sheetsgoogle-apps-script

I've encountered this problem: I've made (a rough) script (but works) that generate a random list of items, sort them and archive them, all works fine and now I want to hide the sheets where this happen (the engine), but when I run the script, they will be turned visible again…

There's a way to make it run without unhide anything?

https://docs.google.com/spreadsheets/d/1Jb66PhYPsegNJYpzwrYIArLp0vTBjMtaNOqNf-qSrdA/edit#gid=711743796

function archive2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Lista');      // name of source sheet
  var sourceRange = source.getRange('A1:B');    // range to copy
  var destination = ss.getSheetByName('Sheet2'); // name of log sheet
  var lastRow = destination.getLastRow();
  sourceRange.copyTo(destination.getRange(5000 + 1, 1),{contentsOnly:true});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Sheet2');      // name of source sheet
  var sourceRange = source.getRange('H1:I5000');    // range to copy
  var destination = ss.getSheetByName('Sheet3'); // name of log sheet
  var lastRow = destination.getLastRow();
  destination.getRange('A1:B').activate();
  destination.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Sheet2');      // name of source sheet
  var sourceRange = source.getRange('H1:I5000');    // range to copy
  var destination = ss.getSheetByName('Sheet3'); // name of log sheet
  sourceRange.copyTo(destination.getRange(1 + 1, 1),{contentsOnly:true});
  var spreadsheet = SpreadsheetApp.getActive();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Sheet3');      // name of source sheet
  var sourceRange = source.getRange('A1:B5000');    // range to copy
  var destination = ss.getSheetByName('Sheet2'); // name of log sheet
  var lastRow = destination.getLastRow();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('A:B').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  sourceRange.copyTo(destination.getRange('A:B'),{contentsOnly:true});
}

Best Answer

It's very likely that the problem are

destination.getRange('A1:B').activate();     
destination.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

and

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
spreadsheet.getRange('A:B').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

because a hidden sheet can't be active as an active sheet is displayed to the active user, an active range means that it's selected (highlighted).

In a broad sense, replace getActiveRangeList by getRangeList. Other code lines should be edited accordingly.