在并行调用Apps Script API时，为什么会超时并记录丢失的错误?

本文介绍了在并行调用Apps Script API时，为什么会超时并记录丢失的错误?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在编写一个Google表格插件，该插件将一些数据从一个电子表格复制到另一个电子表格，然后重新格式化.涉及的数据集通常很大(约10万行)，因此为了避免达到6分钟的超时限制，我将数据分成多个块，然后使用google.script.run调用从每个块并行运行数据复制功能客户端.

I'm writing a Google Sheets add-on that copies some data from one spreadsheet to another and then reformats it. The data sets involved are often large (~100k rows), so to avoid hitting the 6-minute timeout limit I break the data into chunks and then run the data-copying function in parallel on each chunk using google.script.run calls from the client side.

在大约10万行的样本数据集上，成功完成的前几个块已被成功复制，其余的则抛出错误访问ID为[spreadsheet id]的文档时，服务电子表格超时."

On my sample data set of ~100k rows, the first couple of chunks to complete are copied successfully, and the rest are throwing the error "Service Spreadsheets timed out while accessing document with id [spreadsheet id]."

这是在Apps脚本仪表板中的外观:

And here's what it looks like in the Apps Script dashboard:

我对超时错误感到困惑，因为:

I'm confused by the timeout errors because:

我已经在包含5000行的数据集上成功运行了脚本
Apps脚本仪表板显示执行失败6分钟(更可能是4-5分钟)之前
Apps脚本仪表板日志记录显示成功(失败)的日志记录失败.记录发生在setValues()操作之后(请参见下面的代码)；日志记录之后唯一出现的就是返回值，因此我不知道它如何成功记录日志然后超时(我以为Apps Script是同步的……但也许我错了吗?)

我也不确定那些未捕获"错误，但是由于文档[spreadsheet id]丢失(也许已被删除，或者您没有读取权限?"，它们似乎正在显示在仪表板上.)"

I'm also not sure about those "Uncaught" errors, but they seem to be showing up on the dashboard as "Document [spreadsheet id] is missing (perhaps it was deleted, or you don't have read access?)"

这是我要复制到的文档，并且我已经确认它仍然存在于我的云端硬盘中，我可以将其打开并查看已成功复制的数据.如果脚本的太多实例试图同时访问它，那么文档可以丢失"吗?

This is the document I'm copying to, and I've confirmed that it still exists on my Drive and I can open it and see the data that was successfully copied. Can a document go "missing" if too many instances of a script are trying to access it simultaneously?

我尝试了较小的块大小(1000和2000行)，并且得到了相同类型的错误.

I've experimented with smaller chunk sizes (1000 and 2000 rows) and get the same types of errors.

这是我的客户端Javascript的样子:

Here's what my client-side Javascript looks like:

// This function is the success handler that runs after another function (which grabs the total # of rows
// from the sheet to be copied, and then creates the new spreadsheet to be copied into) completes
function dataParamsSuccess(dataParameters) {
      // dataParameters = [busHrs, outputSsUrl, lastRow, maxRows, maxColumns]
      var busHrs = dataParameters[0];
      var outputSsUrl = dataParameters[1];
      var lastRow = dataParameters[2];
      var maxRows = dataParameters[3];
      var maxColumns = dataParameters[4];
      console.log(maxRows);
      console.log(maxColumns);

      // Set chunk size
      var chunkSize = 5000; // number of rows in chunk

      // Determine number of chunks
      var numChunks = Math.ceil(lastRow / chunkSize);
      var lastChunkSize = lastRow % chunkSize;
      if ((numChunks-1) * chunkSize + lastChunkSize == lastRow) {
        console.log("Math checks out");
      } else {
        console.log("oops, check your math");
      }

      // Generate status message
      var statusHtml = numChunks + " chunks to be copied";
      for (i=0; i<numChunks; i++) {
        var chunkNum = i+1;
        var chunkNumStr = chunkNum.toString();
        statusHtml += "<div id=\"chunk" + chunkNumStr + "Status\"></div>";
      }
      document.getElementById("statusMsg").innerHTML = statusHtml;

      var startRow = 1;
      // Call copyData once for each chunk
      for (i=0; i<numChunks; i++) {
        var chunkNum = i+1;
        var chunkNumStr = chunkNum.toString();
        var chunkDivId = "chunk" + chunkNumStr + "Status";

        if (chunkNum==numChunks) { // if this is the last chunk, chunk size is smaller
          chunkSize = lastChunkSize;
        }

        var copyParams = [chunkNum, chunkSize, startRow, outputSsUrl];
        google.script.run
          .withSuccessHandler(copyChunkSuccess)
          .copyData(copyParams);
        document.getElementById(chunkDivId).innerHTML = "Chunk " + chunkNumStr + " copying in progress";
        startRow += chunkSize;
        console.log("startRow: " + startRow.toString());

      }

      // Haven't gotten to the part where I figure out what to do after all chunks are complete yet
    }

这是称为服务器端的Apps脚本函数:

And here's the server-side Apps Script function being called:

function copyData(copyParams) {

  try {
    // copyParams = [chunkNum, chunkSize, startRow, outputSsUrl]
    var chunkNum = copyParams[0];
    var chunkSize = copyParams[1];
    var startRow = copyParams[2];
    var outputSsUrl = copyParams[3];
    var lastRow = startRow + chunkSize;

    // Get input and output sheets
    var dataSheet = SpreadsheetApp.getActiveSheet();
    var outputSpreadsheet = SpreadsheetApp.openByUrl(outputSsUrl);
    var outputSheet = outputSpreadsheet.getActiveSheet();

    // Copy values
    var values = dataSheet.getRange(startRow, 1, chunkSize, 22).getValues();
    outputSheet.getRange(startRow, 1, chunkSize, 22).setValues(values);

    // Logging
    var dataSpreadsheetId = dataSheet.getParent().getId();
    var outputSpreadsheetId = outputSpreadsheet.getId();
    console.log("Chunk " + chunkNum.toString() + " (rows " + startRow.toString() + " through " + lastRow.toString() + ") copied successfully");
    return [chunkNum, startRow, lastRow, "success"];
  } catch(e) {
    return [chunkNum, startRow, lastRow, e.message]; // Return error to client-side; server-side logging is taking too long
  }
}

推荐答案

此答案如何?

以我的经验，即使使用Spreadsheet服务，但在异步过程中发生连续访问时，我也遇到了此类问题.那时，我使用了锁定服务和 setTimeout .但是我不确定此方法是否可以解决您的问题.因此，请测试以下修改.在这里，我想建议对Google Apps脚本端使用锁定服务，对Javascript端使用 setTimeout .修改脚本后，它如下所示.

In my experience, even when the Spreadsheet service is used, when the continuous accesses occurs with the asynchronous process, I have experienced such issue. At that time, I used the lock service and setTimeout. But I'm not sure whether this method can resolve your issue. So please test the following modification. Here, I would like to propose to use the lock service for Google Apps Script side and setTimeout for Javascript side. When your script is modified, it becomes as follows.

此解决方法的流程如下.

The flow of this workaround is as follows.

将10个工作人员发送到Google Apps脚本端.
派出10名工作人员后，等待5秒钟.
在Google Apps脚本方面，收到了10名工作人员.并在锁定服务下对其进行处理.
5秒后，在Javascript端，将发送下10个工作线程.

在此循环中，脚本已运行.

By this cycle, the script is run.

请如下修改 copyData .

function copyData(copyParams) {
  var lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      // copyParams = [chunkNum, chunkSize, startRow, outputSsUrl]
      var chunkNum = copyParams[0];
      var chunkSize = copyParams[1];
      var startRow = copyParams[2];
      var outputSsUrl = copyParams[3];
      var lastRow = startRow + chunkSize;

      // Get input and output sheets
      var dataSheet = SpreadsheetApp.getActiveSheet();
      var outputSpreadsheet = SpreadsheetApp.openByUrl(outputSsUrl);
      var outputSheet = outputSpreadsheet.getActiveSheet();

      // Copy values
      var values = dataSheet.getRange(startRow, 1, chunkSize, 22).getValues();
      outputSheet.getRange(startRow, 1, chunkSize, 22).setValues(values);

      // Logging
      var dataSpreadsheetId = dataSheet.getParent().getId();
      var outputSpreadsheetId = outputSpreadsheet.getId();
      console.log("Chunk " + chunkNum.toString() + " (rows " + startRow.toString() + " through " + lastRow.toString() + ") copied successfully");
      return [chunkNum, startRow, lastRow, "success"];
    } catch(e) {
      return [chunkNum, startRow, lastRow, e.message]; // Return error to client-side; server-side logging is taking too long
    } finally {
      lock.releaseLock();
    }
  }
}

HTML&Javascript方面:

请按如下所示修改 dataParamsSuccess .

// This function is the success handler that runs after another function (which grabs the total # of rows
// from the sheet to be copied, and then creates the new spreadsheet to be copied into) completes

async function dataParamsSuccess(dataParameters) {  // <--- Modified
  const wait = (s) => new Promise(r => setTimeout(r, s));  // <--- Added

  // dataParameters = [busHrs, outputSsUrl, lastRow, maxRows, maxColumns]
  var busHrs = dataParameters[0];
  var outputSsUrl = dataParameters[1];
  var lastRow = dataParameters[2];
  var maxRows = dataParameters[3];
  var maxColumns = dataParameters[4];
  console.log(maxRows);
  console.log(maxColumns);

  // Set chunk size
  var chunkSize = 5000; // number of rows in chunk

  // Determine number of chunks
  var numChunks = Math.ceil(lastRow / chunkSize);
  var lastChunkSize = lastRow % chunkSize;
  if ((numChunks - 1) * chunkSize + lastChunkSize == lastRow) {
    console.log("Math checks out");
  } else {
    console.log("oops, check your math");
  }

  // Generate status message
  var statusHtml = numChunks + " chunks to be copied";
  for (i = 0; i < numChunks; i++) {
    var chunkNum = i + 1;
    var chunkNumStr = chunkNum.toString();
    statusHtml += "<div id=\"chunk" + chunkNumStr + "Status\"></div>";
  }
  document.getElementById("statusMsg").innerHTML = statusHtml;

  var count = 0;  // <--- Added
  var startRow = 1;
  // Call copyData once for each chunk
  for (i = 0; i < numChunks; i++) {
    count++;  // <--- Added
    var chunkNum = i + 1;
    var chunkNumStr = chunkNum.toString();
    var chunkDivId = "chunk" + chunkNumStr + "Status";

    if (chunkNum == numChunks) { // if this is the last chunk, chunk size is smaller
      chunkSize = lastChunkSize;
    }

    var copyParams = [chunkNum, chunkSize, startRow, outputSsUrl];
    google.script.run
      .withSuccessHandler(copyChunkSuccess)
      .copyData(copyParams);

    if (count == 10) {  // <--- Added
      console.log("wait");
      await wait(5000);
      count = 0;
    }

    document.getElementById(chunkDivId).innerHTML = "Chunk " + chunkNumStr + " copying in progress";
    startRow += chunkSize;
    console.log("startRow: " + startRow.toString());

  }

  // Haven't gotten to the part where I figure out what to do after all chunks are complete yet
}

注意:

我不确定 await wait(5000)中的 5000 是否适合您的情况.因此，请根据您的情况进行测试以修改此值.在当前值中， 5000 是5秒.

Note:

I'm not sure whether 5000 of await wait(5000) is suitable for your situation. So please modify this value by testing at your situation. In the current value, 5000 is 5 seconds.

锁定服务

这篇关于在并行调用Apps Script API时，为什么会超时并记录丢失的错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！