Retrieving Overwrapped Cells Between 2 Ranges on Google Spreadsheet using Google Apps Script

submit.md

Retrieving Overwrapped Cells Between 2 Ranges on Google Spreadsheet using Google Apps Script

This is a sample script for retrieving the overwrapped cells between 2 ranges on Google Spreadsheet using Google Apps Script. Please use this with enabling V8.

const getOverwrappedRanges_ = (rangeList1, rangeList2) => {
  if (
    rangeList1.toString() != "RangeList" ||
    rangeList2.toString() != "RangeList"
  ) {
    throw new Error("Input RangeList object.");
  }

  // 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;
  };

  // Expand range1.
  const ar = rangeList1.getRanges().reduce((ar, r) => {
    const startRow1 = r.getRow();
    const endRow1 = startRow1 + r.getNumRows();
    const startColumn1 = r.getColumn();
    const endColumn1 = startColumn1 + r.getNumColumns();
    for (let j = startRow1; j < endRow1; j++) {
      for (let k = startColumn1; k < endColumn1; k++) {
        ar.push(columnToLetter(k) + j);
      }
    }
    return ar;
  }, []);

  // Expand range2.
  const map = rangeList2.getRanges().reduce((m, r) => {
    const startRow2 = r.getRow();
    const endRow2 = startRow2 + r.getNumRows();
    const startColumn2 = r.getColumn();
    const endColumn2 = startColumn2 + r.getNumColumns();
    for (let j = startRow2; j < endRow2; j++) {
      for (let k = startColumn2; k < endColumn2; k++) {
        m.set(columnToLetter(k) + j, null);
      }
    }
    return m;
  }, new Map());

  return ar.filter(e => map.has(e));
};

const main = () => {
  const range1 = ["B3:C7", "D6:E9"]; // Please input range1 as a1Notation.
  const range2 = ["A2:B3", "C7:D10"]; // Please input range2 as a1Notation.

  const sheet = SpreadsheetApp.getActiveSheet();
  const res = getOverwrappedRanges_(
    sheet.getRangeList(range1),
    sheet.getRangeList(range2)
  );
  console.log(res); // <--- ["B3","C7","D7","D8","D9"]
};

• In this sample script, from the ranges of "B3:C7", "D6:E9" and "A2:B3", "C7:D10", the overwrapped cells are returned. In this case, ["B3","C7","D7","D8","D9"] is returned.

• For example, when sheet.getRangeList(res).setBackground("red") is put after const res = getOverwrappedRanges_(), the background of overwrapped cells are changed to the red color.

• The following sample script can be also used for this.

  • Updated: Expanding A1Notations using Google Apps Script