Copy formula every other row and every column

Question

I have been playing with scripts to try and figure out this tedious task as there are 600ish rows in my sheet, times 4 sheets.

So far I have come up with this

function myFunction() {
  var ss =SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("A2").setFormula("=iferror(VLOOKUP(A1,Responses!$M:$N,2,false),)");

  var lr = ss.getLastRow()
  var lc = ss.getLastColumn()
  var fillDownRange = ss.getRange(2, 1, lr, lc)
  ss.getRange("A2").copyTo(fillDownRange)

}

However, this copies it everywhere... I am looking to find a way to copy it every even row (2,4,6,8...) Any suggestion would be appreciated.

This better might give you a better visual. I am trying to put the formula in those blank spaces.

Answer 1

Have you made a start on this? I don't know Apps Script, but cobbled together the following.

If you copyTo all rows, they will naturally be overwritten. I'd suggest stepping through the even rows instead.

First there's a problem with that approach; if not using the "intelligent" formula copying, the formula's A2 reference will not update, so rewrite the formula to refer to "the cell above the current cell", e.g. instead of
=iferror(VLOOKUP(A1,Responses!$M:$N,2,false),)
use something like
=IFERROR(VLOOKUP(INDIRECT("R"&ROW()-1&"C"&COLUMN(),FALSE),Responses!$M:$N,2,false))

Then, instead of overwriting everything, step through the rows in pairs—increment by two. E.g.:

function overwriteEvenRows() {
  var fillFormula = "=IFERROR(VLOOKUP(INDIRECT(\"R\"&ROW()-1&\"C\"&COLUMN(),FALSE),Responses!$M:$N,2,false))";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var lc = ss.getLastColumn();

  var thisRow;
  for (thisRow=2; thisRow<=lr+1; thisRow+=2) {
    ss.getRange(thisRow, 1, 1, lc).setFormula(fillFormula);
  }
}

I don't know what the odd-numbered rows' values mean or what your Responses sheet looks like, but I made a demo sheet with filler values if you or others want a starting place to experiment.