Overwrapped Cells on Google Spreadsheet using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

This is a sample script for checking the overwrapped cells of multiple ranges on Google Spreadsheet using Google Apps Script.

When applications are developed, there might be a case that it is required to confirm whether 2 ranges on Google Spreadsheet are overwrapped. In this post, I would like to introduce a sample script for achieving this.

Method: getOverwrappedCells

The following script is a method of getOverwrappedCells. This is the main script of this post. This method returns the information about the overwrapped cells by inputting an array including the Class Range object. For example, as the default response, when each cell in “range1” and “range2” is overwrapped, true is returned. When { responseType: "list" } is used, the cell coordinates of the overwrapped cells are returned as an array.

/**
 * ### Description
 * Confirm overwrapped cells from ranges.
 *
 * ### Sample script
 * ```javascript
 * const rangeList = ["B2:F6", "E6:F7", "D11:F16", "F6:I11", "I5:J12"];
 * const sheet = SpreadsheetApp.getActiveSheet();
 * const ranges = sheet.getRangeList(rangeList).getRanges();
 * const res = getOverwrappedCells_(ranges, { responseType: "list" });
 * console.log(res); // [ 'E6', 'F11', 'F6', 'F7', 'I10', 'I11', 'I6', 'I7', 'I8', 'I9' ]
 * ```
 *
 * @param {Array} ranges 1 dimensional array including Class Range objects.
 * @param {Object} obj Set the response type. You can choose one of "boolean", "list", and "object". Defailt is "boolean".
 * @return {(Boolean|Array|Object)} Returns information about the overwrapped cells of the inputted ranges.
 */
function getOverwrappedCells(ranges, obj = { responseType: "boolean" }) {
  if (
    !ranges ||
    !Array.isArray(ranges) ||
    !ranges.every((e) => (e ? e.toString() == "Range" : false))
  ) {
    throw new Error("Please give ranges.");
  }

  // Ref: https://stackoverflow.com/a/53678158
  const columnIndexToLetter_ = (index) =>
    (a = Math.floor(index / 26)) >= 0
      ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26))
      : "";

  const a1Notations = ranges
    .reduce((ar, r) => {
      const startRow = r.getRow();
      const endRow = startRow + r.getNumRows();
      const startColumn = r.getColumn();
      const endColumn = startColumn + r.getNumColumns();
      for (let j = startRow; j < endRow; j++) {
        for (let k = startColumn; k < endColumn; k++) {
          ar.push(columnIndexToLetter_(k - 1) + j);
        }
      }
      return ar;
    }, [])
    .sort((a, b) => (a > b ? 1 : -1));
  const o = a1Notations.reduce(
    (m, e) => m.set(e, m.has(e) ? m.get(e) + 1 : 1),
    new Map()
  );
  const res = [...new Map([...o].filter(([, b]) => b > 1)).keys()];
  if (obj.responseType == "list") {
    return res;
  } else if (obj.responseType == "object") {
    return Object.fromEntries([...o]);
  }
  return res.length > 0 ? true : false;
}

This method checks only the cell coordinates, and it doesn’t check the sheet. Please be careful about this. When you want to check the sheet, please give the ranges to this method by checking the sheet in your script.

The sample scripts introducing the next section use this method.

Sample script 1

The sample situation can be seen in the following image.

In this sample, the range enclosed by the red line is “B2:F6”. The ranges enclosed by the blue line are “D4” and “H4”.

About the cells “B2:F6” and “D4”, the sample script is as follows.

function sample1a() {
  const rangeList = ["B2:F6", "D4"];
  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet.getRangeList(rangeList).getRanges();
  const res = getOverwrappedCells(ranges);
  console.log(res); // true
}

When this script is run, true is returned from getOverwrappedCells. From this result, it is found that the cells “B2:F6” and “D4” are overwrapped.

About the cells “B2:F6” and “H4”, the sample script is as follows.

function sample1b() {
  const rangeList = ["B2:F6", "H4"];
  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet.getRangeList(rangeList).getRanges();
  const res = getOverwrappedCells(ranges);
  console.log(res); // false
}

When this script is run, false is returned from getOverwrappedCells. From this result, it is found that the cells “B2:F6” and “H4” are not overwrapped.

Sample script 2

The sample situation can be seen in the following image.

In this sample, the ranges enclosed by the red line are “B2:F6”, “I5:J12”, and “D11:F16”. The range enclosed by the blue line is “F6:I11”. The range enclosed by the green line is “E6:F7”. The script for retrieving the cells overwrapped of these ranges is as follows.

function sample2() {
  const rangeList = ["B2:F6", "I5:J12", "D11:F16", "F6:I11", "E6:F7"];
  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet.getRangeList(rangeList).getRanges();
  const res = getOverwrappedCells(ranges, { responseType: "list" });
  console.log(res); // [ 'E6', 'F11', 'F6', 'F7', 'I10', 'I11', 'I6', 'I7', 'I8', 'I9' ]
}

When this script is run, an array of [ 'E6', 'F11', 'F6', 'F7', 'I10', 'I11', 'I6', 'I7', 'I8', 'I9' ] is returned. From this result, it is found that the overwrapped cells of the ranges of ["B2:F6", "I5:J12", "D11:F16", "F6:I11", "E6:F7"] are [ 'E6', 'F11', 'F6', 'F7', 'I10', 'I11', 'I6', 'I7', 'I8', 'I9' ].

Here, in this script, when const res = getOverwrappedCells(ranges, { responseType: "list" }); is modified to const res = getOverwrappedCells(ranges);, true is returned.

As additional information, in this case, the named ranges can be also used. The sample script is as follows.

function main() {
  const namesOfNamedRanges = ["sample1", "sample2", "sample3"];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ranges = namesOfNamedRanges.map((e) => ss.getRangeByName(e));
  const res = getOverwrappedCells(ranges, { responseType: "list" });
  console.log(res);
}

In this script, the overwrapped cells of 3 named ranges can be retrieved.

Sample script 3

The sample situation can be seen in the following image.

In this sample, it is dynamically confirmed whether the selected cell is inside the specific named range (“B2:F6”) using the situation of “Sample script 1”. The sample script is as follows.

function onSelectionChange(e) {
  const { range } = e;
  const namedRange = e.source.getRangeByName("sampleNamedRange"); // Named range for "B2:F6"
  const message = getOverwrappedCells([range, namedRange])
    ? "Inside"
    : "Outside";
  Browser.msgBox(message);
}

In this script, when a cell is selected on Spreadsheet, onSelectionChange function is automatically run by the simple trigger. When the above image is seen, you can see whether the selected cell is inside in the range of “B2:F6” by a dialog.

I believe that this method can be used for various situations. For example, it can be also confirmed whether the edited cell is included in the specific cells using the OnEdit trigger.

 Share!