Google Apps脚本setValues()问题:间歇性超时 [英] Google Apps script setValues() issue: timing out intermittently

问题描述

我有一个运行了四年没有问题的Google Apps脚本.但是，自3个星期以来，我遇到了一个问题:脚本运行了很长时间并且失败了.每10个运行中就有3个发生这种情况.错误消息为访问ID为[此处为电子表格ID]的电子表格时服务电子表格超时"..

I have a Google Apps script that has been running without issues for 4 years. However, since 3 weeks I have this problem: the script is running for a very long time and failing. This happens every 3 out of 10 runs. The error message is "Service Spreadsheets timed out while accessing spreadsheet with id [spreadsheet id here]".

运行在数百个电子表格上的精美脚本(成千上万行)使用 fetchUrl()获取数据，并使用 setValues()填充工作表.该实际脚本过去可以在10张工作表的电子表格上正常工作，并且在过去4年中可以无问题地更新每张工作表中的18万个单元格.现在，我什至无法更新一张纸.

The actual script, which is elaborate (thousands of lines) and runs on hundreds of spreadsheets takes the data using fetchUrl() and populates the sheet with setValues(). This actual script used to work fine on spreadsheets with 10 sheets and could update the 180k cells in each sheet without a problem for the past 4 years. Now, I can't update even one sheet.

下面的脚本复制了这个问题:它使用 .getValues()和 .setValues()从Sheet1将1300行×140列复制到Sheet2.当行数增加到800以上时，将失败.如果运行正常，执行日志将显示需要8秒钟.失败时，日志显示的运行时间最长为900秒.在这段时间内，您无法访问电子表格超过10分钟，如果您尝试将电子表格加载到其他标签中，则该电子表格根本不会加载.

The script below replicates this issue: it copies 1300 rows by 140 columns from Sheet1 to Sheet2 using .getValues() and .setValues().The script starts to fail when the number of rows is increased above 800. When it runs fine the execution logs show it takes 8 seconds. When it fails the logs show run times of up to 900 seconds. During that time, you can’t access the spreadsheet for more than 10 minutes, if you try to load the spreadsheet in a different tab it doesn’t load at all.

我已经打开了Google支持问题，没有时间表，但是给您带来的不便，我们深表歉意.这种情况发生在我尝试过脚本的所有域上，而不仅仅是我的.您需要尝试运行脚本10次以查看失败.

I have opened an issue with Google Support, I got no timeline, but profuse apologies for the inconvenience. This happens on all domains I have tried the script on, not only mine. You need to try running the script 10 times to see the failures.

如果有人可以提出解决方法或提供有关此问题的一些见解，我将不胜感激.

I would greatly appreciate if someone could suggest a workaround or provide some insight about this issue.

以下是复制该问题的电子表格的链接: https://docs.google.com/spreadsheets/d/1jea15rtjv85YIZumABMfFKESb2_QmX0-7zC-KchWeDc/edit?usp=sharing

Here is the link to the spreadsheet replicating the issue: https://docs.google.com/spreadsheets/d/1jea15rtjv85YIZumABMfFKESb2_QmX0-7zC-KchWeDc/edit?usp=sharing

function myFunction() {
  var row1 = 1;
  var col1 = 1;
  var row2 = 1300;
  var col2 = 140;
  console.log({numrows:row2, numcols:col2} );
  var rng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(row1,col1,row2,col2);
  var values_to_set = rng.getValues();
  var rng2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(row1,col1,row2,col2);
  rng2.setValues(values_to_set);
  console.log('done');
  
}

推荐答案

根据此

According to this comparison of read/write methods, using advanced services to write is faster than setValues().

使用原始代码段的以下修改版本适用于示例电子表格:

Using the following modified version of your original snippet worked for your sample spreadsheet:

function myFunction() {
  var row1 = 1;
  var col1 = 1;
  var row2 = 1300;
  var col2 = 140;
  Logger.log({numrows:row2, numcols:col2} );
  var rng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(row1,col1,row2,col2);
  var values_to_set = rng.getValues();
  var rng2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(row1,col1,row2,col2);
  //rng2.setValues(values_to_set);
  // Based on https://developers.google.com/apps-script/advanced/sheets
  var request = {
    'valueInputOption': 'USER_ENTERED',
    'data': [
      {
        'range': 'Sheet2!' + rng2.getA1Notation(),
        'majorDimension': 'ROWS',
        'values': values_to_set
      }
    ]
  };
  Sheets.Spreadsheets.Values.batchUpdate(request, SpreadsheetApp.getActiveSpreadsheet().getId());
  Logger.log('done');
}

这篇关于Google Apps脚本setValues()问题:间歇性超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！