Expanding Rows in Google Spreadsheet using Google Apps Script

submit.md

Expanding Rows in Google Spreadsheet using Google Apps Script

This is a sample script for expanding the rows in Google Spreadsheet using Google Apps Script. The sample situation is as follows.

Sample situation

Input

Output

Sample script

function myFunction() {
  const expandedColumns = [2, 3, 4, 5]; // Please set the expanded columns you expect.
  const delimiter = "\n"; // Please set the delimiter.
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map((s) =>
    ss.getSheetByName(s)
  );
  const [head, ...values] = srcSheet.getDataRange().getValues();
  const res = [
    head,
    ...values.flatMap((r) => {
      const { v, max } = expandedColumns.reduce(
        (o, c, i) => {
          const s = r[c - 1].split(delimiter);
          o.v[c - 1] = s;
          const len = s.length;
          if (i == 0) {
            o.max = len;
          } else {
            o.max = o.max > len ? o.max : len;
          }
          return o;
        },
        { v: {}, max: 0 }
      );
      return [...Array(max)].map((_, j) =>
        r.map((c, k) => (!v[k] ? c : v[k][j] || null))
      );
    }),
  ];
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When this script is run, the above sample situation can be obtained.
• For example, when you change const expandedColumns = [2, 3, 4, 5]; to const expandedColumns = [5];, only the column "E" is expanded.

Hi Tanaike!

This post is great, thank you!

My suggestion is to make it a custom function

function unmerge(values, delimiter) {

  var expandedColumns = []; 
  for (var i = 1; i < values[0].length; i++) {
    expandedColumns.push(i);
  }
  delimiter = delimiter || "\n"; // Please set the delimiter.

  const res = [
    ...values.flatMap((r) => {
      const { v, max } = expandedColumns.reduce(
        (o, c, i) => {
          const s = r[c].split(delimiter);
          o.v[c] = s;
          const len = s.length;
          if (i == 0) {
            o.max = len;
          } else {
            o.max = o.max > len ? o.max : len;
          }
          return o;
        },
        { v: {}, max: 0 }
      );
      return [...Array(max)].map((_, j) =>
        r.map((c, k) => (!v[k] ? c : v[k][j] || null))
      );
    }),
  ];
  return res;
}

Please see sample usage here:
https://docs.google.com/spreadsheets/d/1As5ld5HQT18pYudguxv14gi8ZG5N5db-gfCGLzFgsFI/edit#gid=0

@Max-Makhrov
Thank you for your suggestion. I think that it will be useful for a lot of users!

I wish I could vote a star to the comment on gist.