How to reduce the latency between two script calls in Google Apps Script

Question

In a self-developed add-on for Google Sheets, the functionality has been added that a sound file will be played from a JavaScript audio player in the sidebar, depending on the selection in the table. For the code itself see here.

When a line is selected in the table the corresponding sound file is played in the sidebar. Every time the next line is selected it takes around 2 seconds before the script will start to run and load the sound file into the sidebar. As the basic idea of the script is to quickly listen through long lists of sound files, it is crucial to reduce the waiting time as fare as possible.

A reproducible example is accessible here; Add-ons > 'play audio' (Google account necessary). To reproduce the error, the sheet has to be opened two times (e.g. in two browsers).

Please post minimal reproducible example here. Links to minimal reproducible example are not acceptable — Cooper
Instead of polling, can you use the onSelectionChange() trigger? — Diego
@Diego but that implies that you have to reload the sidebar and that's time consuming. — Cooper
@Cooper Seems like a more appropriate use of Apps Script than polling the spreadsheet every 2 seconds. I think it's fair to consider that this application is not best suited for a spreadsheet. Peter may be better off having the entire interaction in HTML either as a sidebar, modal, or web app. — Diego

Rubén Rubén · Accepted Answer · 2020-11-15T18:04:04

In order to reduce the latency you might try to reduce interval on your poll function as suggested by Cooper on a comment to the question and to change the getRecord function.

poll

At this time the interval is 2 seconds. Please bear in mind that reducing the interval too much might cause an error and also might have an important impact on the consume of the daily usage quotas. See https://developers.google.com/apps-script/guides/services/quotas

getRecord

Every time it runs it make multiple calls to Google Apps Script which are slow so you should look for a way to reduce the number of Google Apps Script calls. In order to do this you could store the spreadsheet table data in the client side code and only read it again if the data was changed.

NOTE: The Properties Service has a 50,000 daily usage quota for consumer accounts.

One way to quickly implement the above is to limit the getRecord function to read the current cell and add a button to reload the data from the table.

Function taken from the script bounded to the demo spreadsheet linked in the question.

function getRecord() {
  var scriptProperties = PropertiesService.getScriptProperties();
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var rowNum = sheet.getActiveCell().getRow(); // Get currently selected row
  var oldRowNum = scriptProperties.getProperty("selectedRow"); // Get previously selected row
  if(rowNum == oldRowNum) { // Check if the was a row selection change
    // Function returns the string "unchanged"
    return "unchanged";
  }
  scriptProperties.setProperty("selectedRow", rowNum); // Update row index
  if (rowNum > data.length) return [];
  var record = [];
  for (var col=0;col<headers.length;col++) {
    var cellval = data[rowNum-1][col];
    if (typeof cellval == "object") {
      cellval = Utilities.formatDate(cellval, Session.getScriptTimeZone() , "M/d/yyyy");
    }
    record.push({ heading: headers[col],cellval:cellval });
  }
  return record;
}

Problems when using a Google spreadsheet add-on by multiple users

How to reduce the latency between two script calls in Google Apps Script

1 Answers