Service Spreadsheet Time Out - Optimizing Macro script?

Question

Context

I have a g-sheet that acts as a sort of "master sheet" in which everything pours into from a bunch of other outside spreadsheets that are all consistently being live updated throughout the day.

Each outside spreadsheet I connect, routes to its own tab within our master spreadsheet through importrange function

All those tabs then route to one master tab using row ID #'s - so that everyone can just work from that tab.

The Problem

In this master tab where everything lands, I have a macro sorting the rows to bring the most recent rows to the top, among other things to keep the data clean. As I connect more sheets over time, I add to the number in the macro to accommodate new rows.

Macro a couple days ago started throwing "Service Spreadsheet timed out while accessing document with id..." then the id is the id # of the master tab itself.

Know there is probably a lot smoother way to have this done without using a large bandwidth macro in place, but optimizing the script to best fit the use-case is far out of my experience level. The macro I have in place is as follows:

function MasterSormat2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('D1').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues([''])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(4, criteria);
  criteria = SpreadsheetApp.newFilterCriteria()
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(4, criteria);
  criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues([''])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(4, criteria);
  spreadsheet.getRange('A1:AP11001').activate();
  spreadsheet.getActiveRange().offset(1, 0, spreadsheet.getActiveRange().getNumRows() - 1).sort({column: 4, ascending: false});
  spreadsheet.getRange('A:AM').activate();
  spreadsheet.getActiveRangeList().setFontFamily('Calibri')
  .setHorizontalAlignment('left');
  spreadsheet.getRange('P:S').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('right');
  spreadsheet.getRange('U:U').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('right');
  spreadsheet.getRange('AA:AG').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('right');
  spreadsheet.getRange('AL:AL').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('right')
  .setNumberFormat('"$"#,##0.00');
  spreadsheet.getRange('D4').activate();
};

Can anyone possibly point me in the right direction here when it comes to improving this?

Thanks for any help that you can provide here, I look forward to learning further

Please learnn how to write your macros without the excessive use of activate(); — Cooper, Aug 11 '22 at 22:59

Rubén · Answer 1 · 2022-11-09T18:37:12.420

Tl;Dr:

If you are able to record the macro again, consider to use Go to range or the name box instead of using your mouse to move from one place to anohter in your spreadsheet as each click on a sheet and range adds a statement that activates the corresponding sheet / range. The .activate() methods usually are a lot slower that other options.

The alternative to record the macro again is to remove statements like spreadsheet.getRange(harcoded_ref).activate() and replace statements like spreadsheet.getActiveRangeList() by, i.e.,

spreadsheet.getRange(hardcoded_ref),
spreadsheet.getRangeList(array_of_refs),

etc.

For this, you require some writing-code-skills, JavaScript knowledge and Spreadsheet Service (Class SpreadsheetApp) knowledge. In order to be able achive the best performance possible in Google Apps Script you should consider to use the Advanced Sheets Service, more specifically the spreadsheet.batchUpdate method.

Go to range

Try the following keyboard shortcuts

Windows	Mac
`Ctrl` + `Alt` + `.` `Ctrl` + `Alt` + `,`	`⌘` + `Option` + `.` `⌘` + `Option` + `,`

If the above keyboard shortcuts don't work for you or you are on a different operative system, to open the Go to side panel, click in the Help menu, then on the search box type Go to, then select Go to range. This will open a "side panel", type the cell reference, then press enter or click on >.

Help menu	Side panel

Name box

Writing code skills and JavaScript knowledge

I suggest you to spend some time on learning the JavaScript basics in order to be able to understand the recorded macro and adapt it to your needs.

Let say that you learned about primitives, objects, properties, classes, methods, literals and variables and understand the very basics of method chaining as the macro recorder used it a lot.

One of the things that you might find that will help to optimize the recorded macros is by assigning objects to variables. I.E. assign Class Sheet object corresponding to the active sheet to the variable named sheet:

var sheet = spreadsheet.getActiveSheet();

Then replace all the spreadsheet.getActiveSheet() by sheet.

In order to improve the performance of your recorded macro, also you should replace

spreadsheet.getRange(something).activate();

by

var rangeSomething =  spreadsheet.getRange(something);

then replace the spreadsheet.getRange(something).chain1 before the following spreadsheet.getRange(something).activate(); by rangeSomething.chain1

If you find multiple likes like

spreadsheet.getRange('P:S').activate();
spreadsheet.getActiveRangeList().setHorizontalAlignment('right');

replace these lines by something like this:

var rangeList = spreadsheet.getRangeList(['P:S','U:U','AA:AG','AL:AL']);
rangeList.setHorizontalAlignment('right');

GOOGLE script 'copyTo values only' does not work when the source is a function (e.g. NOW())

Service Spreadsheet Time Out - Optimizing Macro script?

1 Answers1

Tl;Dr:

Go to range

Name box

Writing code skills and JavaScript knowledge

Linked