Google脚本/工作表-如何使用... new Set()使脚本在循环中更快地运行 [英] Google Script/sheets - How to make a script run faster during a loop using ...new Set()

问题描述

基于@ziganotschka对此问题的大力支持，我们具有以下代码.对于每个参考，这是一个链接到演示页 a>在原始问题上发布.

Based on the great support by @ziganotschka on this question, we have the following code. For each of reference, here's a link to the demo sheet posted on the original question.

function change_row_color() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = spreadsheet.getDataRange();
  var rangeData = range.getValues();
  var lastRow = spreadsheet.getLastRow();
  var previousclient = rangeData[0][4];
  console.log(previousclient);
  var colors = [[0,0,0,0,0]];
  for ( j = 1 ; j < lastRow; j++){
    var currentclient = rangeData[j][4];
    console.log(previousclient," ",currentclient);
    if (previousclient != currentclient) {
      colors.push(["#cc4125","#cc4125","#cc4125","#cc4125","#cc4125"]);
      previousclient = currentclient;  
    } else{
      colors.push([0,0,0,0,0]);
    }
  }
range.setBackgrounds(colors);
}

脚本运行良好，但运行速度很慢.我的测试阵列有350行，大约需要20秒钟才能运行.我的实际数组有1万多行，并且还在不断增长.在实际数组上运行此代码后，我希望将其降至5s以下. ziganotschka建议使用... new Set().我希望您对此有所投入.

The script is running great, but it's running rather slow. My test array has 350 rows and it takes about 20s to run. My actual array has 10K+ rows, and growing. Once I run this code on the real array, I'd like to drop it to below 5s. ziganotschka suggested to use ...new Set(). I'd love you input on this.

推荐答案

使用条件格式

在E列中的值与上一行相同的所有行中应用给定样式.

Using Conditional formatting

To apply a given style for all the row where the value in the E column is the same as the previous row.

在工作表中:

• 格式>条件格式
• 适用于范围A2:Z
• 格式化规则Custom formula is
• 公式=$E2=$E1的值//如果需要在另一行进行测试，请更改E的值.

• Format > Conditional formatting
• Apply to range A2:Z
• Format rule Custom formula is
• Value of formula =$E2=$E1 // change the value of E is you need to make the test on an other row.

选择所需的格式样式.

这篇关于Google脚本/工作表-如何使用... new Set()使脚本在循环中更快地运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！