Please explain this Google Script

Question

Note I'd never seen a spreadsheet until about a year ago.

I used this script a while ago, and it worked:

function setFormulas() {

  var sheet, startRow, i; 
  sheet = SpreadsheetApp.getActiveSheet();
  startRow = 7;
  i = 3;
  while(startRow < 5181) {
    sheet.getRange(startRow, 2).setFormula('=AVL!V' + i);
    i++;
    startRow += 26;
  }
}

It set a formula =AVL!N3 into a cell B7, on the tab MER, which is in the same workbook, and then copied the formula down the sheet in every 26th cell in ColB starting in B7 and then every 26th cell down in ColB.

So:

AVL!N3 went into B7
AVL!N4 went into B33
AVL!N5 went into B59
AVL!N6 went into B85

Etc etc...

And for 31 times in total. I need to do this for 72 different sheets (It's a boat booking system) so adjusting the formulas manually would be a nightmare.

Answer 1

I wrote what each step of the function does below- the // indicates the comment explaining the line.

function setFormulas() {

  var sheet, startRow, i; //sets your variables
  sheet = SpreadsheetApp.getActiveSheet(); //gets the sheet
  startRow = 7; //sets the start row to 7
  i = 3; //sets i to be the number 3

  while(startRow < 5181) { //while startRow (which starts at 7) is less than 5181 keep doing this 
    sheet.getRange(startRow, 2).setFormula('=AVL!V' + i);//get the cell and set it to =ALV!V plus the value for i
    i++; // add 1 to the value of i
    startRow += 26; //increment startRow by adding 26 for each loop through
  }
}