Google-sheets – Copy formula to every 25th row, increase reference by one row

google sheetsgoogle-appsgoogle-apps-script

I'm stuck on copying a formula in Google sheets. I want to copy a formula to a cell every 25th row, but increase the reference by just one row.

✅ example, I'd like these formulas to go into L4, L29, and L54

=transpose(split(ArrayFormula(concatenate(SUPIR!$N3:$AB3&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N4:$AB4&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N5:$AB5&" "))," "))

❌ what I get is…

=transpose(split(ArrayFormula(concatenate(SUPIR!$N3:$AB3&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N28:$AB28&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N53:$AB53&" "))," "))

I just don't know how to change the references with this formula. Any help gratefully received.

The script I've been using to set other formulas is here, but I haven't been able to get it to run successfully…

function setFormulas() {

  var sheet, startRow, i; 
  sheet = SpreadsheetApp.getActiveSheet();
  startRow = 4;
  i = 4;
  while(startRow < 775) {
    sheet.getRange(startRow, 12).setFormula('=transpose(split(ArrayFormula(concatenate(SUPIR!N3:AB3&" "))," "))' + i);
    i++;
    startRow += 25;
  }
}

Best Answer

You are just appending i to the end of the formula. This will create the formulas as you want them

function setFormulas() {
  var sheet, startRow, i; 
  sheet = SpreadsheetApp.getActiveSheet();
  startRow = 4;
  i = 3;
  while(startRow < 775) {
    sheet.getRange(startRow, 12).setFormula('=transpose(split(ArrayFormula(concatenate(SUPIR!$N'+i+':$AB'+i+'&" "))," "))');
    i++;
    startRow += 25;
  }
}
Related Topic