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

submit.md

Merging Columns 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 column are merged using the same header title.

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

Sample situation

Input: "Sheet1"

Output: "Sheet2"

Sample script

function myFunction() {
  const srcSheetName = "Sheet1"; // This sheet is "Input" situation.
  const dstSheetName = "Sheet2"; // This sheet is "Output" situation.

  const transpose = (ar) => ar[0].map((_, c) => ar.map((r) => r[c]));
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = [srcSheetName, dstSheetName].map((s) =>
    ss.getSheetByName(s)
  );
  const values = src.getDataRange().getValues();
  const temp = [
    ...transpose(values)
      .reduce(
        (m, [a, ...b]) => m.set(a, m.has(a) ? [...m.get(a), ...b] : [a, ...b]),
        new Map()
      )
      .values(),
  ];
  const res = transpose(temp);
  dst.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• In this sample script, the following flow is used.
  1. Retrieve values from "Sheet1".
  2. Tanspose the retrieved values.
  3. Create an array using Map object.
  4. Tanspose the created array.
  5. Put the array to "Sheet2".

How do we merge/concatenate the data? Currently the data from the other columns are being deleted.