Speeding up a script for a spreadsheet (GAS - Google App Script & Google Sheets)

Question

I have a very long script and have summarized the commands that I use numerous times in the script, which are of these types:

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('Page 1!A1').activate();
  spreadsheet.getCurrentCell().setFormula('=UNIQUE(H2:H)');

  spreadsheet.getRange('Page 2!P1:P').activate();
  spreadsheet.getRange('Page 3!A1:A').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('Page 3!A1').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true}); 

  //----------------------------------------------

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var page = ss.getSheets()[0];
  var range = page.getRange('Page 4!A2:R51');
  range.sort({column:8, ascending:false});

And I would like to know how I could speed up these procedures, as I learned to create most things by Macro, I end up also playing for scripts and I know there is no need to use them, but I have a little difficulty adjusting and removing Things that are unnecessary.

Answer 1

Take a look at the Best Practices, particularly the section about minimizing calls to other services. This is an oversimplification, but basically every time you call a Spreadsheet service method (.doSomething()), you slow down your script.

  var ss = SpreadsheetApp.getActive();

  ss.getRange('Page 1!A1').setFormula('=UNIQUE(H2:H)');
  ss.getRange('Page 3!A1:A').copyTo(ss.getRange('Page 2!P1:P'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Page 3!A1').clear({contentsOnly: true, skipFilteredRows: true}); 

  //----------------------------------------------

  ss.getRange('Page 4!A2:R51').sort({column:8, ascending:false});

Improvements:

• SpreadsheetApp.getActive() and SpreadsheetApp.getActiveSpreadsheet() are the same. Only call it once.
• No need to get a range and then activate it. Activation is basically just for interacting with a user selection, but that doesn't seem to be what you need here. The way you're doing it, you're calling the Spreadsheet service 4 times: (1) get the range, (2) activate the range, (3) get the activated range, (4) do something to the activated ranged. You can halve those calls by simply getting the range and then perform whatever actions you need on it.
• You're specifying the page in your .getRange('Page 4!A2:R51') call, so no need to do the ss.getSheets() call.

There's probably a lot of other improvements that could be made, but that would likely take a complete refactoring of your code. It's probably a good exercise for you. If you look at the batch operations section in the best practices, you might get an idea of how much you might improve the performance (their example code was sped by from 70 seconds to just 1 second).