Google Apps脚本.setvalues For循环无效

The goal of the loop is to fill each cell over 797 rows across 5 columns A, B, C, D and E with a formula whose cell reference increments by 1.

循环的目标是用5个A,B,C,D和E列的797行填充每个单元格,其公式的单元格参考值增加1。

E.g. Column A rows 6 onwards will have formula "=indirect("'Data Repository'!A3++")"

例如。列A向前的第6行将具有公式“= indirect(”'Data Repository'!A3 ++“)”

Column B rows 6 onwards will have formula "=indirect("'Data Repository'!B3++")"

B列6行以后将具有公式“= indirect(”'Data Repository'!B3 ++“)”

What happens when I run the function however is it only fills in column A. I've checked the execution transcript and execution succeeded is logged after the first column has been filled up. I've tried various variations to no avail.

然而,当我运行该函数时会发生什么,它只填写A列。我检查了执行记录,并在第一列填满后记录执行成功。我尝试了各种变化无济于事。

Below is the last variation I've tested:

以下是我测试的最后一个变体:

function indirect(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Fleet - Weekly V3");
  var formulaArray = [];
  var columns = ["A","B","C","D","E"];
  var row = 2;
  var text = '=indirect(\"\'Data Repository\'!';
  var headerRow = 6;
  var column;

  for(i = 0; i < 5; i++) {
    column = parseInt(i) + 1;
    formula = text + columns[i];
    for(i = 0; i < 797; i++) {
      row += 1;
      if (formulaArray.length == 797) {
        sheet.getRange(headerRow, column).offset(0, 0, formulaArray.length).setValues(formulaArray);
      } else {
        formulaArray.push([formula + row + '")']);
      }  
      Logger.log(formulaArray.length);
    }
    Logger.log(i)
    formulaArray = [];
  }
}

2 个解决方案

#1

Here is where you might be making an error - you need to create the variable i (var i = 0 instead of just i = 0) and if you're nesting loops, you need to have different variables increasing (first loop use i, then nest with j, then nest in that with k etc as needed)

这里是你可能会出错的地方 - 你需要创建变量i(var i = 0而不是i = 0),如果你是嵌套循环,你需要增加不同的变量(第一次循环使用i,然后用j嵌套,然后根据需要用k等嵌套

 for(var i = 0; i < 5; i++) {
  column = parseInt(i) + 1;
  formula = text + columns[i];
   for(var j = 0; j < 797; j++) {

Untested but I believe it should work if you just substitute that in.

未经测试,但我相信如果你只是替换它,它应该工作。

#2

Your problem is in your loops. You are using the 'i' variable twice. Change the for loop that you have nested to iterate over the variable 'j' or something other than 'i'.

你的问题出在你的循环中。你正在使用'i'变量两次。更改嵌套的for循环,以迭代变量'j'或除'i'之外的其他内容。

#1

 for(var i = 0; i < 5; i++) {
  column = parseInt(i) + 1;
  formula = text + columns[i];
   for(var j = 0; j < 797; j++) {