Converting Relative Reference to Absolute Reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

This is a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script.

Description

A1Notation is used in the cells on Google Spreadsheet.

As the 1st sample, it supposes that a formula of =A1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =A2 and =B1, respectively. This is the relative reference.

As the 2nd sample, it supposes that a formula of =$A$1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =$A$1 and =$A$1, respectively. This is the absolute reference. In the absolute reference, there are 3 patterns. The above is 1st pattern. The 2nd and 3rd patterns are as follows.

As the 3rd sample, it supposes that a formula of =$A1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =$A2 and =$A1, respectively. In this case, it is found that even when =$A1 is copied, the column is not changed.

As the 4th sample, it supposes that a formula of =A$1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =A$1 and =B$1, respectively. In this case, it is found that even when =A$1 is copied, the row is not changed.

In this post, I would like to introduce a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation using Google Apps Script.

The key factor for achieving this is to use the formulas as R1C1 instead of A1Notation. By this, the conversion for the relative reference and the absolute reference can be achieved by a simple script.

Sample script

/**
 * ### Description
 * This is a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation using Google Apps Script.
 *
 * Author: Tanaike ( https://tanaikech.github.io/ )
 */
class ConvertReference {
  /**
   * ### Description
   * Constructor of this class.
   *
   * @param {string} a1Notation Please set a range as a1Notation. It's like "'Sheet1'!B3:E5".
   * @return {void}
   */
  constructor(a1Notation) {
    this.a1Notation = a1Notation;
  }

  /**
   * ### Description
   * Convert the absolute references to the relative references.
   *
   * @return {void}
   */
  absoluteToRelative() {
    const { range, offsetR, offsetC, values, newR1C1Values } = this.getValuesAndOffset_();
    const regex = [new RegExp("R([-,0-9].*?)C([-,0-9].*?)", "g"), new RegExp("R\\[([-,0-9].*?)\\]C([-,0-9].*?)", "g"), new RegExp("R([-,0-9].*?)C\\[([-,0-9].*?)\\]", "g")];
    const newValues = newR1C1Values.map((r, i) =>
      r.map((c, j) => {
        const check = regex.findIndex(e => e.test(c));
        if (check != -1) {
          return c.replace(regex[check], (_, p2, p3) => {
            if (check == 0) {
              return `R[${Number(p2) - (i + offsetR)}]C[${Number(p3) - (j + offsetC)}]`;
            } else if (check == 1) {
              return `R[${Number(p2)}]C[${Number(p3) - (j + offsetC)}]`;
            } else if (check == 2) {
              return `R[${Number(p2) - (i + offsetR)}]C[${Number(p3)}]`;
            }
          });
        }
        return c[0] == "=" ? c : values[i][j][0];
      })
    );
    range.setValues(newValues);
    return null;
  }

  /**
   * ### Description
   * Convert the relative references to the absolute references.
   *
   * @param {number} conversionPattern Pattern for converting. 1: (=A1 --> =$A$1) 2: (=A1 --> =$A1) 3: (=A1 --> =A$1)
   * @return {void}
   */
  relativeToAbsolute(conversionPattern) {
    const { range, offsetR, offsetC, values, newR1C1Values } = this.getValuesAndOffset_();
    const newValues = newR1C1Values.map((r, i) =>
      r.map((c, j) => c ? c.replace(/R\[(.*?)\]C\[(.*?)\]/g, (_, p2, p3) => {
        let f = "";
        if (conversionPattern == 1) {
          f = `R${Number(p2) + i + offsetR}C${Number(p3) + j + offsetC}`;
        } else if (conversionPattern == 2) {
          f = `R[${Number(p2)}]C${Number(p3) + j + offsetC}`;
        } else if (conversionPattern == 3) {
          f = `R${Number(p2) + i + offsetR}C[${Number(p3)}]`;
        } else {
          throw new Error("Invalid value.");
        }
        return f;
      }) : values[i][j])
    );
    range.setValues(newValues);
    return null;
  }

  getValuesAndOffset_() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(this.a1Notation);
    const range = sheet ? sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()) : ss.getRange(this.a1Notation);
    const offsetR = range.getRow();
    const offsetC = range.getColumn();
    const values = range.getValues();
    const newR1C1Values = range.getFormulasR1C1();
    return { range, offsetR, offsetC, values, newR1C1Values };
  }
}

// Convert the relative references to the absolute references.
function sample1() {
  const a1Notation = "'Sheet1'!B3:E5";
  const conversionPattern = 1;

  new ConvertReference(a1Notation).relativeToAbsolute(conversionPattern);
}

// Convert the absolute references to the relative references.
function sample2() {
  const a1Notation = "'Sheet1'!B3:E5";

  new ConvertReference(a1Notation).absoluteToRelative();
}

  • When sample1 is run, the relative references in all formulas in the cells “‘Sheet1’!B3:E5” are converted to the absolute references. In this case, you can select the pattern of the absolute references.

  • When sample1 is run, the absolute references in all formulas in the cells “‘Sheet1’!B3:E5” are converted to the relative references.

Reference

 Share!