Equivalent of “ScreenUpdating” in Google Apps Script (equivalent VBA-GAS )

Question

I'm looking for the equivalent VBA-GAS of:

Application.ScreenUpdating = False

I'm running a very long macro in one of my Google Spreadsheets and it takes at least 30 seconds to finish it every time. It would be helpful if the macro didn't refresh the screen after every line of code.

Answer 1

There isnt. However you should batch all setValues into range writes (ideally a single range write) which will help here.
Only call SpreadsheetApp.flush() at the very end.

Answer 2

I ended up creating a second sheet called 'Start' which just had A1='Please wait...'. The sheet I display all my data on is called 'Report'

Then used:

//Hide Report sheet
SpreadsheetApp.getActive().getSheetByName("Start").showSheet();
var sheet = SpreadsheetApp.getActive().getSheetByName("Report");
sheet.hideSheet();

//Code to update sheet here
sheet.appendRow('Blah','Blah','Blah');

//Show Report sheet
sheet.showSheet();
SpreadsheetApp.getActive().setActiveSheet(sheet);
SpreadsheetApp.getActive().getSheetByName("Start").hideSheet();

Answer 3

The way I got around this is by bypassing every instance of "setActiveSheet" or "activate."

For example:

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
spreadsheet.getRange('B4').activate();
spreadsheet.getRange('Sheet1!A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

can be shortened to:

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('Sheet1!A2').copyTo(spreadsheet.getRange('Sheet2!B4'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

and can be run from any sheet on the document without changing the selected sheet or cell.

There may be a reason for Google to automatically add this redundancy, but this does appear to work in every case I've used it in.

Answer 4

While I understand that the below does not necessarily speed up the macro, it makes for a better UX and is important to note. For many function calls and updates, VBA first requires that we "unhide" the respective sheet:

' Unhide the sheet, write to it, hide it '
Dim lastrow As Long

Sheets("Report").Visible = True
Sheets("Report").Select

' Append a Row '
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveSheet.Cells(lastrow, "A").Value = "Blah"

Sheets("Report").Select
ActiveWindow.SelectedSheets.Visible = False

The same is not required for many of the Google Apps Script Classes:

// Hide the sheet, write to it, show it
var sheet = SpreadsheetApp.getActive().getSheetByName('Report').hideSheet();
sheet.appendRow(['Blah']);
sheet.showSheet();