Compiling Continuous Numbers using Google Apps Script

GasTips / jstips / spreadsheet
Google Apps Script / Javascript / spreadsheet

Gists

This is a sample script for compiling the continuous numbers using Google Apps Script. For example, the values of [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1] are converted to ["1","3-5","7","9-11","13"].

Sample script

const compilingNumbers = (ar) => {
  const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce(
    (o, e, i, a) => {
      if (
        o.temp.length == 0 ||
        (o.temp.length > 0 && e == o.temp[o.temp.length - 1] + 1)
      ) {
        o.temp.push(e);
      } else {
        if (o.temp.length > 0) {
          o.values.push({ start: o.temp[0], end: o.temp[o.temp.length - 1] });
        }
        o.temp = [e];
      }
      if (i == a.length - 1) {
        o.values.push(
          o.temp.length > 1
            ? { start: o.temp[0], end: o.temp[o.temp.length - 1] }
            : { start: e, end: e }
        );
      }
      return o;
    },
    { temp: [], values: [] }
  );
  return values;
};

// Please run this function.
function main() {
  const ar = [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1]; // This is sample values.

  const values = compilingNumbers(ar);
  console.log(values);

  const res = values.map(({ start, end }) =>
    start == end ? start.toString() : `${start}-${end}`
  );
  console.log(res);
}

When this script is run, console.log(values) and console.log(res) show [{"start":1,"end":1},{"start":3,"end":5},{"start":7,"end":7},{"start":9,"end":11},{"start":13,"end":13}] and ["1","3-5","7","9-11","13"], respectively. From this result, it is found that the continuous numbers were compiled.

Sample situation

As a sample situation using this sample script, I would like to introduce the following situation.

The column numbers of [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1] on an active sheet of Spreadsheet are hidden.

Sample script

const compilingNumbers = (ar) => {
  const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce(
    (o, e, i, a) => {
      if (
        o.temp.length == 0 ||
        (o.temp.length > 0 && e == o.temp[o.temp.length - 1] + 1)
      ) {
        o.temp.push(e);
      } else {
        if (o.temp.length > 0) {
          o.values.push({ start: o.temp[0], end: o.temp[o.temp.length - 1] });
        }
        o.temp = [e];
      }
      if (i == a.length - 1) {
        o.values.push(
          o.temp.length > 1
            ? { start: o.temp[0], end: o.temp[o.temp.length - 1] }
            : { start: e, end: e }
        );
      }
      return o;
    },
    { temp: [], values: [] }
  );
  return values;
};

// Please run this function.
function main() {
  const ar = [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1]; // It supposes that these values are the column numbers for Spreadsheet.

  const values = compilingNumbers(ar);
  const sheet = SpreadsheetApp.getActiveSheet();
  values.forEach(({ start, end }) => sheet.hideColumns(start, end - start + 1));
}

In this sample situation, the columns “A”, “C-E”, “F-G”, “I-K”, “M” of the active sheet are hidden. When this sample script compilingNumbers() is used, the method of hideColumns(columnIndex, numColumns) can be used effectively. Of course, when this situation can be achieved by Sheets API, the process cost will be the lowest. But when Sheets API cannot be used, I think that this method will be useful.

Reference

 Share!