Google Apps 脚本电子表格范围处理(Get-Process-Set；Set Not Working) [英] Google Apps Script Spreadsheet Range Processing (Get-Process-Set; Set Not Working)

问题描述

我再次回到 SO 来解决 GAS 问题，因为我还不太熟悉 Javascript/GAS.基于以有效方式处理函数调用的方法，我对脚本运行速度有多慢感到有些麻烦.

Once again I'm back to SO for GAS problems because I'm not too familiar with Javascript/GAS yet. I'm having a bit of trouble with how slowly a script is running based on the method of handling function calls in an efficient manner.

我在几个地方读过(啊，是这里)，执行 "read-all" 然后 "write-all" 获取解析设置值(至少在电子表格中)比执行 快>get-one, write-one"方法(出于显而易见的原因，这是有道理的).

I've read in several places (ah, it was here), that doing a "read-all" then "write-all" for getting-parsing-setting values (in Spreadsheets at least) is faster than doing a "get-one, write-one" method (for obvious reasons, this makes sense).

我知道如何获取 Range 中的所有值，但我不太确定如何遍历(多维)数组、处理数据并相应地设置新 Range.

I know how to get all the values in a Range, but I'm not really sure how to go through the (multidimensional) array, process the data, and set a new Range accordingly.

问题:该函数启动大约需要 2 秒，运行 50 行大约需要 5 秒.问题是，我可能会在此电子表格中有数千行，并运行此函数以确保正确处理数据，以便 Boomerang Calendar 正确获取时间数据.在我看来很荒谬.对于需要处理的每一列数据，该脚本需要两倍的时间，这太可怕了.我担心在下一个学期我会经常超过我的GAS 处理时间限制".

The problem: The function takes about 2 seconds to start up, and takes about 5 seconds to run for 50 rows. The problem is, I will likely have thousands of rows in this Spreadsheet, and to have this function run to ensure that data is post-processed correctly so that Boomerang Calendar picks up the time data correctly is a tad ridiculous in my opinion. The script takes twice as long for every column of data needed to process, which is horrendous. I fear that during the next school semester I will often be going over my "GAS processing time limit."

这是起始代码(不好，我承认):

function fixApostrophes() {
  // Get the active spreadsheet to run the script on
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the active sheet within the document to run the script on
  var sheet = spreadsheet.getActiveSheet();

  // Get max number of rows needed to process
  var maxRows = sheet.getLastRow();

  // Get the range for the startTime column and endTime column
  var apostropheRange = sheet.getRange(1, 10, maxRows, 2);

  // Get the value in each cell, remove apostrophes from the start,
  // and replace the value in that cell
  for (var i = 1; i < apostropheRange.getNumRows(); ++i) {

    // Get the cells for startTime and endTime to speed things up a bit
    var startCell = apostropheRange.getCell(i, 1);
    var endCell = apostropheRange.getCell(i, 2);

    // Get the values for startTime and endTime
    var startTime = startCell.getValue();
    var endTime = endCell.getValue();

    // Remove apostrophes from start of startTime
    while(startTime.charAt(0) == "'") {
      startTime = startTime.substring(1);
    }

    // Remove apostrophes from start of startTime
    while(endTime.charAt(0) == "'") {
      endTime = endTime.substring(1);
    }

    // Set the values for startTime and endTime
    startCell.setValue(startTime);
    endCell.setValue(endTime);
  }
}

这与我的上一个问题高度相关 关于修复时间格式撇号问题，该问题破坏了 Boomerang 日历用于安排活动的功能.

This is highly related to my previous question about fixing a time-format apostrophe issue that was breaking Boomerang Calendar functionality for scheduling events.

解决方案:使用 Range.getValues() 函数将 Range 中的值拉入二维数组.处理二维数组中的每个值(逐行可能是最合乎逻辑的方法)，然后用新值更新已编辑值的索引(参见代码中的 Srik-answer 注释).之后，使用 Range.setValues(2Darray) 将 2D 数组中的元素放回原始 Range.如果你遇到它，我希望这对你有帮助！这也比在我的原始代码中看到的多次调用 API 快很多.

The solution: Pull the values from a Range into a 2D array using the Range.getValues() function. Process each value in the 2D array (row-by-row is probably the most logical method), then update the index of the edited value with the new value (see Srik-answer comments in code). After that, put the elements in the 2D array back into the original Range by using Range.setValues(2Darray). I hope this helps you if you came across it! This is also much faster than calling the API multiple times as seen in my original code.

function myNewLibraryFunction(startCol, numColumns) {

  // Get the active spreadsheet to run the script on
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the active sheet within the document to run the script on
  var sheet = spreadsheet.getActiveSheet();

  // Get max number of rows needed to process
  var maxRows = sheet.getLastRow();

  // Get the range for the startTime column and endTime column
  var dataRange = sheet.getRange(1, startCol, maxRows, numColumns);
  var values = dataRange.getValues();

  // Get the value in each cell, remove apostrophes from the start,
  // and replace the value in that cell
  for (var i = 1; i < maxRows; ++i) {

// Get the values for startTime and endTime
var startTime = values[i][0];
var endTime = values[i][1];

    // Remove apostrophes from start of startTime
    while(startTime.charAt(0) == "'") {
      startTime = startTime.substring(1);
    }

    // Remove apostrophes from start of startTime
    while(endTime.charAt(0) == "'") {
      endTime = endTime.substring(1);
    }

    values[i][0] = startTime; // New stuff from Srik's answer
    values[i][1] = endTime; // New stuff from Srik's answer
  }
  dataRange.setValues(values);
  SpreadsheetApp.flush(); // New stuff from Srik's answer
}

推荐答案

基本上，您对 GAS 中列出的 API 进行的大多数函数调用都需要比常规 JavaScript 更多的时间.在您的情况下，减少对 Range 和 Sheet 类的调用次数

Basically, most of the function calls that you make to the APIs listed in GAS will require more time than regular JavaScript. In your case, reduce the number of calls to Range and Sheet classes

function fixApostrophes() {
  // Get the active spreadsheet to run the script on
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the active sheet within the document to run the script on
  var sheet = spreadsheet.getActiveSheet();

  // 1. Replace two calls and many other calls later with one. 
  var dataRange = sheet.getDataRange(); 
  var values = dataRange.getValues();// a 2D array

  // Get the value in each cell, remove apostrophes from the start,
  // and replace the value in that cell
  for (var i = 1; i < values .length ; ++i) {

    // Get the values for startTime and endTime
    var startTime = values [i][0];
    var endTime = values [i][1];

    // Remove apostrophes from start of startTime
    // These are okay. Regular Javascript - not time consuming
    while(startTime.charAt(0) == "'") {
      startTime = startTime.substring(1);
    }

    // Remove apostrophes from start of startTime
    while(endTime.charAt(0) == "'") {
      endTime = endTime.substring(1);
    }

  }

  dataRange.setValues(values);
}

提示:您可以在执行记录中查看每个调用花费了多少时间

TIP: You can see how much time each call took in the Execution transcript

这篇关于Google Apps 脚本电子表格范围处理(Get-Process-Set；Set Not Working)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！