javascript - Google Appscript 使用 for 循环和 if

Question

我的脚本的目的是遍历一列数据(在我的示例中为 Col 2)，并且单元格显示“已批准”，然后调整位于相应 Col1 中的公式以保存为值。下面的脚本实现了这一点，但运行速度非常慢 - 任何人都可以帮助加快速度吗？

var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = ss.getSheetByName('data');
var tracker = ss.getSheetByName('Tracker');
var rowlength = tracker.getLastRow();

for (r=2; r<rowlength+1; r++) {
 
 var ApprovedCell = tracker.getRange(r,2).getValue();
 
 if (ApprovedCell == 'Approved'){
    var FormulaCell = tracker.getRange(r,1);
    FormulaCell.copyTo(FormulaCell,{contentsOnly:true});
  
 }}
}

Answer 1

解释:

您当前解决方案的问题在于您正在迭代使用 getValue , getRange和 copyTo这是一种极其低效的方法，尤其是当数据量变大时。

相反，您可以使用 getValues()和 getFormulas()获取 全部 分别为给定范围的值和公式，然后使用 setValues()设置 全部 所需的值/公式返回到工作表。

以下脚本将使用 forEach() 迭代这些值循环并将存储值，if列中的单元格 乙 是 'Approved' ，否则将公式存储到空数组 repAr .

那么你就可以高效地使用一行代码来设置全部 值/公式回到列 一个 :tracker.getRange(2,1,repAr2D.length,1).setValues(repAr2D);

解决方案:

function myFunction(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tracker = ss.getSheetByName('Tracker');
  const vals = tracker.getRange('A2:B'+tracker.getLastRow()).getValues();
  const formulas = tracker.getRange('A2:A'+tracker.getLastRow()).getFormulas().flat();
  const repAr = [];
  vals.forEach((r,i)=>
  repAr.push(r[1]=='Approved'?r[0]:formulas[i]));
  const repAr2D = repAr.map(r=>[r]);
  tracker.getRange('A2:A'+tracker.getLastRow()).clearContent();
  tracker.getRange(2,1,repAr2D.length,1).setValues(repAr2D);
}

奖金信息:

而不是常规 if条件，我用了 ternary operator使代码更清晰。

getFormulas()返回一个二维数组，这就是我使用 flat() 的原因将其转换为一维数组。鉴于范围是单列，并且您可以使用单个索引对数组进行切片，因此这更方便。

clear the content 是一个好习惯在将数据设置为已经包含一些数据的范围之前。