Google Sheets – Script changing A1 to A2 to A3, … inside a formula

Answer:

You can do this by incrementing the R1C1 notation of the formula.

Code:

// Copyright 2020 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function onButtonClick() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let r = s.getRange('A2:B2')
  let f = r.getFormulasR1C1();
  var n = [];

  f.forEach(function(c) {
    let row = (parseInt(c[0].match(/R\[([0-9]*)\]/)[1]) + 1);
    n.push(["=Questions!R[" + row + "]C[0]"])
  });
  r.setFormulaR1C1(n)
}

Rundown of this script:

• Gets the range of the Question and Answer from the ‘Quiz’ Sheet (in this example it has been assumed to be A2:B2)
• Gets the formulae in these cells as R1C1 notation
• Uses the regular expression /R\[([0-9]*)\]/ to get the current increment of the row
  • Increments this number by 1
• Creates a new array of formulae to save to A2:B2
• Sets the formulae.

References:

• Regular expression – Wikipedia
• Class Range – getFormulasR1C1() | Apps Script | Google Developers