Merging Rows with Same Header Title in Google Spreadsheet using Google Apps Script


This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each row is merged using the same header title.

In this sample script, the sample input and output situations are as follows.

Sample situation



Sample script

In this sample script, this sample can be used as the custom function.

function SAMPLE(values) {
  const headers = [ Set(
        .map((r) => [...r])
        .flatMap((r) =>
          [...Array(Math.ceil(r.length / 2))].map((_) => r.splice(0, 2)[0])
  const obj = values.reduce((o, r) => {
    [...Array(Math.ceil(r.length / 2))].forEach((_) => {
      const [k, v] = r.splice(0, 2);
      if (k && headers.includes(k)) o[k] = o[k] ? [...o[k], v] : [v];
    return o;
  }, {});
  const v = => [e, ...obj[e]]);
  return v[0].map((_, c) => => r[c]));
  • In this sample script, the following flow is used.
    1. Retrieve values from cells.
    2. Retrieve header values.
    3. Create an object for populating values for each header.
    4. Convert the object to an array.
    5. Tanspose the created array.