Copying and Deleting Dimension Groups in Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

In this post, I would like to introduce 2 sample scripts for copying and deleting the dimension groups in Google Spreadsheet using Google Apps Script.

Unfortunately, in the current stage, all dimension groups cannot be copied by one action, and also, all dimension groups cannot be deleted by one action. In this post, these can be achieved using Google Apps Script.

These sample scripts use Sheets API. So, please enable Sheets API at Advanced Google services.

Sample script 1: Copy

This sample script copies all dimension groups from a source sheet to a destination sheet.

When the values of srcSpreadsheetId and dstSpreadsheetId are the same, this script can be used in the same Spreadsheet.

function sample1() {
  const srcSpreadsheetId = "###"; // Please set source spreadsheet ID.
  const srcSheetName = "Sheet1"; // Please set source sheet name.
  const dstSpreadsheetId = "###"; // Please set destination spreadsheet ID.
  const dstSheetName = "Sheet2"; // Please destination sheet name.

  const { sheets } = Sheets.Spreadsheets.get(srcSpreadsheetId, {
    ranges: [srcSheetName],
  });
  const obj = [
    ...(sheets[0].rowGroups || []),
    ...(sheets[0].columnGroups || []),
  ];
  if (obj.length == 0) return;
  const sheetId = SpreadsheetApp.openById(dstSpreadsheetId)
    .getSheetByName(dstSheetName)
    .getSheetId();
  const requests = obj
    .sort((a, b) => (a.depth > b.depth ? 1 : -1))
    .map((o) => {
      o.range.sheetId = sheetId;
      delete o.depth;
      return { addDimensionGroup: o };
    });
  Sheets.Spreadsheets.batchUpdate({ requests }, dstSpreadsheetId);
}

Sample script 2: Delete

This sample script deletes all dimension groups in a source sheet.

function sample2() {
  const spreadsheetId = "###"; // Please set source spreadsheet ID.
  const sheetName = "Sheet1"; // Please set source sheet name.

  const { sheets } = Sheets.Spreadsheets.get(spreadsheetId, {
    ranges: [sheetName],
  });
  const obj = [
    ...(sheets[0].rowGroups || []),
    ...(sheets[0].columnGroups || []),
  ];
  if (obj.length == 0) return [];
  const sheetId = SpreadsheetApp.openById(spreadsheetId)
    .getSheetByName(sheetName)
    .getSheetId();
  const requests = obj
    .sort((a, b) => (a.depth < b.depth ? 1 : -1))
    .map((o) => {
      o.range.sheetId = sheetId;
      delete o.depth;
      return { deleteDimensionGroup: o };
    });
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

Reference

 Share!