Inverting Selected Ranges on Google Spreadsheet using Google Apps Script

Gists

This is a sample script for inverting the selected ranges on Google Spreadsheet using Google Apps Script.

I have the case that I want to invert the selected ranges on Google Spreadsheet. This sample script can be achieved this goal using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script. And, please select the cells and run the function main(). By this, the selected ranges are inverted.

// Ref: https://stackoverflow.com/a/21231012/7108653
const columnToLetter_ = (column) => {
  let temp,
    letter = "";
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
};
const letterToColumn_ = (letter) => {
  let column = 0,
    length = letter.length;
  for (let i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
};

// This is from https://tanaikech.github.io/2020/04/04/updated-expanding-a1notations-using-google-apps-script/
function expandA1Notations_(a1Notations, maxRow, maxColumn) {
  maxRow = maxRow || "1000";
  maxColumn = maxColumn || "Z";
  const reg1 = new RegExp("^([A-Z]+)([0-9]+)$");
  const reg2 = new RegExp("^([A-Z]+)$");
  const reg3 = new RegExp("^([0-9]+)$");
  return a1Notations.map((e) => {
    const a1 = e.split("!");
    const r = a1.length > 1 ? a1[1] : a1[0];
    const [r1, r2] = r.split(":");
    if (!r2) return [r1];
    let rr;
    if (reg1.test(r1) && reg1.test(r2)) {
      rr = [r1.toUpperCase().match(reg1), r2.toUpperCase().match(reg1)];
    } else if (reg2.test(r1) && reg2.test(r2)) {
      rr = [
        [null, r1, 1],
        [null, r2, maxRow],
      ];
    } else if (reg1.test(r1) && reg2.test(r2)) {
      rr = [r1.toUpperCase().match(reg1), [null, r2, maxRow]];
    } else if (reg2.test(r1) && reg1.test(r2)) {
      rr = [[null, r1, maxRow], r2.toUpperCase().match(reg1)];
    } else if (reg3.test(r1) && reg3.test(r2)) {
      rr =
        Number(r1) > Number(r2)
          ? [
              [null, "A", r2],
              [null, maxColumn, r1],
            ]
          : [
              [null, "A", r1],
              [null, maxColumn, r2],
            ];
    } else if (reg1.test(r1) && reg3.test(r2)) {
      rr = [r1.toUpperCase().match(reg1), [null, maxColumn, r2]];
    } else if (reg3.test(r1) && reg1.test(r2)) {
      let temp = r2.toUpperCase().match(reg1);
      rr =
        Number(temp[2]) > Number(r1)
          ? [
              [null, temp[1], r1],
              [null, maxColumn, temp[2]],
            ]
          : [temp, [null, maxColumn, r1]];
    } else {
      throw new Error(`Wrong a1Notation: ${r}`);
    }
    const obj = {
      startRowIndex: Number(rr[0][2]),
      endRowIndex: rr.length == 1 ? Number(rr[0][2]) + 1 : Number(rr[1][2]) + 1,
      startColumnIndex: letterToColumn_(rr[0][1]),
      endColumnIndex:
        rr.length == 1
          ? letterToColumn_(rr[0][1]) + 1
          : letterToColumn_(rr[1][1]) + 1,
    };
    let temp = [];
    for (let i = obj.startRowIndex; i < obj.endRowIndex; i++) {
      for (let j = obj.startColumnIndex; j < obj.endColumnIndex; j++) {
        temp.push(columnToLetter_(j) + i);
      }
    }
    return temp;
  });
}

// Please run this function after the cells were selected.
// By this, the selected cells are reversed.
function main() {
  const activeRanges = SpreadsheetApp.getActiveRangeList().getRanges();
  if (activeRanges.length == 0) return;
  const a1Notations = activeRanges.map((e) => e.getA1Notation());
  const res = expandA1Notations_(a1Notations);
  const obj = res.reduce((o, r) => {
    r.forEach((c) => (o[c] = true));
    return o;
  }, {});
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getMaxRows();
  const cols = sheet.getMaxColumns();
  const allA1Notations = [...Array(rows)].flatMap((_, r) =>
    [...Array(cols)].map((_, c) => `${columnToLetter_(c + 1)}${r + 1}`)
  );
  const reverseA1Notations = allA1Notations.filter((r) => !obj[r]);
  if (reverseA1Notations.length == 0) return;
  sheet.getRangeList(reverseA1Notations).activate();
}

Note

In the current stage, each A1Notation cannot be merged. For example, the A1Notations of “A1”, “A2”, “B1”, “B2”, “C1”, “C2” are used as the individual A1Notation instead of “A1:C2”. By this, when a large area of Spreadsheet is used, the process cost of this script become high. Please be careful this. I would like to try to think of the method for merging A1Notations.

 Share!