0

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).

Peter
  • 77
  • 9
  • 1
    Please post [mcve] here. Links to [mcve] are not acceptable – Cooper Nov 15 '20 at 17:54
  • 1
    I would guess you would have to increase your polling rate. – Cooper Nov 15 '20 at 17:56
  • 1
    Instead of polling, can you use the [`onSelectionChange()`](https://developers.google.com/apps-script/guides/triggers#onselectionchangee) trigger? – Diego Nov 15 '20 at 18:32
  • @Diego but that implies that you have to reload the sidebar and that's time consuming. – Cooper Nov 15 '20 at 19:32
  • @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 Nov 15 '20 at 19:52
  • @diego, you might be right, but I am used to this setup now and several users, too. I have 60 sheets with 20 tables each and 270,000 rows being dynamically updated from a database. I would be too time-consuming to change the entire system. – Peter Nov 19 '20 at 10:59

1 Answers1

2

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;
}

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • I tried to reduce the interval for the `poll` function - with no effect. It seems that the 2000 ms is the lowest value. – Peter Nov 16 '20 at 15:38
  • @Peter what about changing the `getRecord` function? – Rubén Nov 16 '20 at 16:06
  • No success yet. Mainly, because I don't have the competencies yet. ;-) Loading only a small part of the table seems reasonable. The Add-on needs only the value from one column. However, if I cannot get below the 2000 ms imposed by the `poll` function (or the Google API in general?), then this will likely not speed it up much? – Peter Nov 19 '20 at 10:55
  • In `getRecord`I tried to reduce the amount of data, by limiting it to only the column that is needed (containing the audio URL), but it seems it is impossible to limit somehow `getDataRange()`. Using `getRange()` is not working for me. – Peter Nov 19 '20 at 13:53