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


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


// 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]"])

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.


CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top