工作表的Google脚本 - 超过最长执行时间

I'm writing a script that's going to look through a monthly report and create sheets for each store for a company we do work for and copy data for each to the new sheets. Currently the issue I'm running into is that we have two days of data and 171 lines is taking my script 369.261 seconds to run and it is failing to finish.

我正在编写一个脚本,该脚本将查看月度报告并为我们工作的公司为每个商店创建工作表,并将每个商店的数据复制到新工作表中。目前我遇到的问题是我们有两天的数据和171行正在运行我的脚本369.261秒运行并且它未能完成。

function menuItem1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("All Stores");
  var data = sheet1.getDataRange().getValues();
  var CurStore;
  var stores = [];
  var target_sheet;
  var last_row;
  var source_range
  var target_range;
  var first_row = sheet1.getRange("A" + 1 +":I" + 1);

  //assign first store number into initial index of array
  CurStore = data[1][6].toString();
  //add 0 to the string so that all store numbers are four digits.
  while (CurStore.length < 4) {CurStore = "0" + CurStore;}
  stores[0] = CurStore;

  // traverse through every row and add all unique store numbers to the array
  for (var row = 2; row <= data.length; row++) {
    CurStore = data[row-1][6].toString(); 

    while (CurStore.length < 4) {
      CurStore = "0" + CurStore;
    }

    if (stores.indexOf(CurStore) == -1) {
      stores.push(CurStore.toString());
    }
  }

  // sort the store numbers into numerical order
  stores.sort();

  // traverse through the stores array, creating a sheet for each store, set the master sheet as the active so we can copy values, insert first row (this is for column labels), traverse though every row and when the unique store is found, 
  // we take the whole row and paste it onto it's newly created sheet
  // at the end push a notification to the user letting them know the report is finished.
  for (var i = stores.length -1; i >= 0; i--) {
    ss.insertSheet(stores[i].toString());
    ss.setActiveSheet(sheet1);
    target_sheet = ss.getSheetByName(stores[i].toString());
    last_row = target_sheet.getLastRow();
    target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));
    first_row.copyTo(target_range);

    for (var row = 2; row <= data.length; row++) { 
      CurStore = data[row-1][6].toString();

      while (CurStore.length < 4) {
        CurStore = "0" + CurStore;
      }

      if (stores[i] == CurStore) {
        source_range = sheet1.getRange("A" + row +":I" + row);
        last_row = target_sheet.getLastRow();
        target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));
        source_range.copyTo(target_range);
      }
    }

    for (var j = 1; j <= 9; j++) {
       target_sheet.autoResizeColumn(j);
    }
  }

  Browser.msgBox("The report has been finished.");
}

Any help would be greatly appreciated as I'm still relatively new at using this, and I'm sure there are plenty of ways to speed this up, if not, I'll end up finding a way to break down the function to divide up the execution. If need be, I can also provide some sample data if need be.

任何帮助都会非常感激,因为我仍然相对较新使用它,我敢肯定有很多方法可以加快速度,如果没有,我最终会找到一种方法来分解功能来划分执行。如果需要,我还可以提供一些样本数据。

Thanks in advance.

提前致谢。

1 个解决方案

#1

The problem is calling SpreadsheepApp lib related methods like getRange() in each iteration. As stated here:

问题是在每次迭代中调用SpreadsheepApp与lib相关的方法,如getRange()。如上所述:

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

在脚本中使用JavaScript操作比调用其他服务快得多。您可以在Google Apps脚本中完成的任何操作都比拨打需要从Google服务器或外部服务器获取数据的内容快得多,例如对电子表格,文档,网站,翻译,UrlFetch等的请求。如果您可以找到最小化脚本对这些服务的调用的方法,那么您的脚本将运行得更快。

I ran into the same situation and, instead of doing something like for(i=0;i<data.length;i++), I ended up dividing the data.length into 3 separate functions and ran them manually each time one of them ended. Same as you, I had a complex report to automate and this was the only solution.

我遇到了同样的情况,而不是像for(i = 0; i ;>