Google Apps Script execution time issue

Question

Im currently using Google Apps Script to implement a viewer of a supply chain database. To synchronize the viewer with the current database (a google spreadsheet) I import the values and all the formatting it into a new sheet, this means the viewer basically is a copy of the current database.

However executing the script always takes something about 1 minute in time. I tried to find the issue with logging some debug messages at various positions in the code. At first it seemed that the line with Viewer.setFrozenRows(1); (which is strange since I actually only freeze the first row) was the issue, however when commenting out this line the line afterwards (Viewer.setFrozenColumns(Database.getFrozenColumns());) seemed to be the issue.

Unfortuanetly I'm not able to share the database sheet with you, but maybe somebody can already spot the issue from the code.

Some additional Info: The database sheet has 1300 rows and 100 columns, and I added a picture of the log of the current code below.

function LoadViewer(view) {
  Logger.log("LoadViewer Start");
   if (view == null) {
    view = 0;
  }
  var Database = SpreadsheetApp.openByUrl('[SHEET_URL].getSheetByName('Database');
  var Viewer = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var numberOfColms = Database.getLastColumn();
  var numberOfRows = Database.getLastRow();
  var rules = Database.getConditionalFormatRules();
  var headerRowHeight = Database.getRowHeight(1);
  var dataRowHeight = Database.getRowHeight(2);
  var Values = Database.getRange(1, 1, numberOfRows, numberOfColms).getValues();

  Logger.log("Declarations Finished");

  Viewer.getRange(1, 1,numberOfRows,numberOfColms).setValues(Values);
  if(!Viewer.getRange(1, 1,numberOfRows,numberOfColms).getFilter()) 
    Viewer.getRange(1, 1,numberOfRows,numberOfColms).createFilter(); 
  Viewer.setConditionalFormatRules(rules);
  Viewer.getRange(1, 1, 1, numberOfColms).setFontWeight('bold');
  Viewer.autoResizeColumns(1, numberOfColms);
  Viewer.setRowHeight(1, headerRowHeight);

  Logger.log("1st Half of functions finished");

  Viewer.setRowHeights(2, numberOfRows-1, dataRowHeight);

  Logger.log("Freeze Rows");

  //Viewer.setFrozenRows(1);

  Logger.log("Freeze Columns");

  Viewer.setFrozenColumns(Database.getFrozenColumns());

  Logger.log("Loop Start");
  for(var i = 1; i<=numberOfColms; i++){
    Viewer.setColumnWidth(i, Database.getColumnWidth(i));
  }
  Logger.log("Loop End");

  Viewer.getRange(1, 1,1,numberOfColms).setVerticalAlignment('middle').setWrap(true);
  Logger.log("Load Viewer End");
}

ziganotschka ziganotschka · Accepted Answer · 2020-09-08T09:02:26

Two optimization points I can see for your code:

Requests to the any external service including SpreadsheetApp make your code slow - see Best Practices.

Thus, making calls to a SpreadsheetApp method within a for loop will slow your code down.

You will be able to accelerate your code by replacing multiple setColumnWidth() requests within the loop by a single setColumnWidths(startColumn, numColumns, width) - avoiding iteration.
Log the number of columns and rows in your sheet.

A common problem is that the sheet contains a significant amount of empty rows and columns that increase your detected data range and consequently apply the subsequent calls to a bigger range than necessary.

If that you case - either delete the spare rows and columns manually, or use getNextDataCell() instead of getLastRow() or getLastColumn()

Google Apps Script execution time issue

1 Answers