如何减少Google Apps脚本中两个脚本调用之间的延迟 [英] How to reduce the latency between two script calls in Google Apps Script

问题描述

在针对Google表格的自行开发的加载项中，添加了功能，即根据表中的选择，将通过边栏中的JavaScript音频播放器播放声音文件.有关代码本身，请参见

在表格中选择一行时，相应的声音文件将在侧栏中播放.每次选择下一行时，脚本将开始运行并将声音文件加载到侧栏中，大约需要2秒钟.由于该脚本的基本思想是快速侦听一长串声音文件，因此，尽可能减少等待时间至关重要.

可访问的示例可在此处进行访问.附件>播放音频"(需要Google帐户).要重现该错误，必须将工作表打开两次(例如，在两个浏览器中).

解决方案

为减少延迟，您可以尝试减小Cooper在对该问题的评论中建议的 poll 函数的间隔并更改 getRecord 函数.

投票

此时间隔为2秒.请记住，间隔时间过长可能会导致错误，并且可能对每日使用配额的消耗产生重要影响.请参见 https://developers.google.com/apps-script/guides/services/quotas

getRecord

每次运行时，它都会多次调用Google Apps Script，这很慢，因此您应该寻找一种减少Google Apps Script调用次数的方法.为此，您可以将电子表格表格数据存储在客户端代码中，并且只有在数据更改后才能再次读取.

注意:Properties Service的消费者帐户每天有50,000个使用配额.

一种快速实现上述方法的方法是限制 getRecord 函数以读取当前单元格，并添加一个按钮以从表中重新加载数据.

---

函数取自脚本，该脚本绑定到问题中链接的演示电子表格.

  function getRecord(){var scriptProperties = PropertiesService.getScriptProperties();var sheet = SpreadsheetApp.getActiveSheet();var data = sheet.getDataRange().getValues();var标头= data [0];var rowNum = sheet.getActiveCell().getRow();//获取当前选中的行var oldRowNum = scriptProperties.getProperty("selectedRow");//获取之前选择的行if(rowNum == oldRowNum){//检查是否是行选择更改//函数返回字符串"unchanged"；返回不变"；}scriptProperties.setProperty("selectedRow"，rowNum)；//更新行索引如果(rowNum> data.length)返回[]；var record = [];对于(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});}返回记录；} 

相关

• 多个用户使用Google电子表格加载项时的问题

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

解决方案

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

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

这篇关于如何减少Google Apps脚本中两个脚本调用之间的延迟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！