Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active Apr 8, 2022
Embed
What would you like to do?
Merging Columns with Same Header Title in Google Spreadsheet using Google Apps Script

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".
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment