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

问题描述

我再一次回到SO的GAS问题上，因为我对Javascript / GAS还不太熟悉。基于处理函数调用的方法，脚本的运行速度有点慢，我遇到了一些麻烦。

我已经读过几个地方（啊，这是这里），做一个阅读所有，然后全部写入以获取解析设置值（至少在电子表格中）比执行get-one，write-one更快

我知道如何获取Range中的所有值，但我并不确定

strong>如何通过（多维）数组，处理数据，并相应地设置一个新的Range 。

问题：该功能需要大约2秒的时间才能启动，并且需要大约5秒才能运行50行。问题是，我可能在这个Spreadsheet中有成千上万行，并且为了确保这个数据被正确地后处理，以便Boomerang Calendar正确地获取时间数据，在我看来是荒谬的。脚本需要处理的每一列数据需要两倍的时间，这是可怕的。我担心在下学期我会经常过去我的GAS处理时间限制。

这里是开始的代码（不好，我承认）：

$ p $ 函数fixApostrophes（）{
//获取活动电子表格以运行脚本
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet（）;

//获取文档中的活动工作表以运行
上的脚本var sheet = spreadsheet.getActiveSheet（）;

//获取处理
所需的最大行数var varmaxRows = sheet.getLastRow（）;

//获取startTime列和endTime列的范围
var apostropheRange = sheet.getRange（1，10，maxRows，2）;

//获取每个单元格中的值，从一开始就删除撇号
//并替换该单元格中的值
for（var i = 1; i< （）;} i）{

//获取startTime和endTime的单元格以加快速度
var startCell = apostropheRange.getCell（i，1）;
var endCell = apostropheRange.getCell（i，2）;

//获取startTime和endTime的值
var startTime = startCell.getValue（）;
var endTime = endCell.getValue（）;

//从startTime开始移除单引号
while（startTime.charAt（0）=='）{
startTime = startTime.substring（1）;
}

//从startTime开始移除单引号
while（endTime.charAt（0）=='）{
endTime = endTime.substring（ 1）;
}

//设置startTime和endTime的值
startCell.setValue（startTime）;
endCell.setValue（endTime）;




$ b $ p
$ b

这与我的以前的问题关于修复时间格式撇号问题Boomerang日历功能用于安排活动。

解决方案：使用 Range.getValues（）函数。处理2D数组中的每个值（逐行可能是最符合逻辑的方法），然后用新值更新已编辑值的索引（请参阅代码中的斯里克答案注释）。之后，通过使用 Range.setValues（2Darray）将2D数组中的元素放回原始范围。我希望这可以帮助你，如果你遇到它！这比在原始代码中多次调用API要快得多。

 函数myNewLibraryFunction（startCol，numColumns）{
 
 //获取活动电子表格以在
上运行脚本var spreadsheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 
 //获取文档中的活动工作表以运行
上的脚本var sheet = spreadsheet.getActiveSheet（）; 
 
 //获取处理
所需的最大行数var varmaxRows = sheet.getLastRow（）; 
 
 //获取startTime列和endTime列的范围
 var dataRange = sheet.getRange（1，startCol，maxRows，numColumns）; 
 var values = dataRange.getValues（）; 
 
 //获取每个单元格中的值，从一开始就删除撇号
 //并替换该单元格中的值
 for（var i = 1; i< maxRows; ++ i）{
 
 //获取startTime和endTime的值
 var startTime = values [i] [0]; 
 var endTime = values [i] [1]; 
 
 //从startTime开始移除单引号
 while（startTime.charAt（0）=='）{
 startTime = startTime.substring（1）; 
} 
 
 //从startTime开始移除单引号
 while（endTime.charAt（0）=='）{
 endTime = endTime.substring（ 1）; 
} 
 
 values [i] [0] = startTime; //来自Srik的答案的新东西
 values [i] [1] = endTime; //来自Srik的答案的新东西
 
 dataRange.setValues（values）; 
 SpreadsheetApp.flush（）; //来自Srik的答案的新东西
 
  

解决方案

基本上，您对GAS中列出的API进行的大多数函数调用都需要比常规JavaScript更多的时间。在你的情况下，减少调用Range和Sheet类的次数。
$ b $ pre $函数fixApostrophes（）{
// Get在
上运行脚本的活动电子表格var spreadsheet = SpreadsheetApp.getActiveSpreadsheet（）;

//获取文档中的活动工作表以运行
上的脚本var sheet = spreadsheet.getActiveSheet（）;

// 1.稍后用一个呼叫替换两个呼叫和许多其他呼叫。
var dataRange = sheet.getDataRange（）;
var values = dataRange.getValues（）; //一个二维数组

//获取每个单元格中的值，从开头删除撇号
//并替换在该单元格中的值
for（var i = 1; i< values .length; ++ i）{

//获取startTime和endTime的值
var startTime =值[i] [0];
var endTime = values [i] [1];

//从startTime
开始删除撇号//这些都没关系。常规的Javascript - 不耗时
while（startTime.charAt（0）=='）{
startTime = startTime.substring（1）;
}

//从startTime开始移除单引号
while（endTime.charAt（0）=='）{
endTime = endTime.substring（ 1）;
}

}

dataRange.setValues（values）;

$ / code>

提示：您可以看到每次通话在执行副本中所占的时间

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.

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

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.

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

Here is the starting code (bad, I admit):

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);
  }
}

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

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
}

解决方案

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屋！