0

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});
}
  • The script doesn't generate a random list of items. One of the "minor" flaws of this script is that it's repeating var ss = SpreadsheetApp.getActiveSpreadsheet(); and var lastRow = destination.getLastRow(); but a not so "minor" flaw is the overwritten of the destination variable without a clear reason to do that instead of using a different variable name for each destination sheet. – Rubén Nov 24 at 18:29
  • Ruben actually it's generation a random list of item...or at least it's what it seems to me...I've made a count function to check how many times items are present in the first 52 lines (cos I've more items and the first 52 lines under "print" tab are my target, but also next I wanna print on those lines first those items that wasnt on the first 52 lines, to avoid printing many times the same words, and to get then an average of printed words)...to me looks working, in archive I copy the history of counta, add it to sheet2 and continue in this way... – Simone Rosmarino Nov 24 at 18:35
  • While including a link to an external resource could be helpful, the question should include all the relevant details to be answered because the external resource could be not accesible later. Please edit the question to clarify that random list is generated by another function that isn't included in the question. Regarding that it's working, there is a site for asking help to improve working code: Code Review. – Rubén Nov 24 at 18:41
  • I try to explain what the script is doing for me: 1) checking the box in Lista, it randomize the list of items to be printed. This is done reading the list in "sheet2" H:I. H:I is a filtered unique sum of the archive+the current randomized words. In Sheet2, column A, first 140 items are the archive from Sheet3 (copied via script), below line 5000 is the copy of total list with 0 as no and 1 as yes, if they appeared on Print Tab. They are summed in Sheet2 columns H:I, backupped in Sheet3 and the circle repeat. In G1 I'm using a random formula to randomize the words. I know it's dirty but works – Simone Rosmarino Nov 24 at 18:43
  • Please edit the question to add the explanation to it. – Rubén Nov 24 at 18:48
0

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.

  • Oh I see...thanks Rubén. There's a way to make this works without activate the sheets then? – Simone Rosmarino Nov 24 at 17:39
  • Please edit the question to describe the algorithm that you are trying to implement. – Rubén Nov 24 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.