Javascript - Delete all columns inside a Column Group with Google Apps Script

Javascript – Delete all columns inside a Column Group with Google Apps Script

Last updated: 2024/01/07 at 6:58 AM

Admin

2 Min Read

Problem:

I’m trying to delete all columns inside Column Groups in all of my Spreadsheet Sheets, but I don’t know how to manage with Column Groups. Each sheet has Column Groups in different (random) ranges, so I can’t get the Groups by it’s concrete range, I’m looking for a way to get the ranges of Column Groups and then delete the columns of the range or something like that.

Contents

Problem:Solution:Sample script 1:Sample script 2:Note:References:

function deleteColumnsInsideColumnGroup(fileSheets) {
  for (var i = 0; i < fileSheets.length; i++) {
    var sheet = destinationSheets[i];

  }
}

Thanks!

Solution:

I believe your goal is as follows.

You want to delete all columns of the column groups of all sheets in a Google Spreadsheet.
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script 1:

function myFunction() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()
    .forEach(sheet => {
      const maxColumn = sheet.getMaxColumns();
      for (let c = maxColumn; c >= 1; c--) {
        const d = sheet.getColumnGroupDepth(c);
        if (d > 0) {
          sheet.deleteColumn(c);
        }
      }
    });
}

When this script is run, all columns of all column groups in all sheets of a Google Spreadsheet are removed.

Sample script 2:

If you want to reduce the process cost of the script, how about using Sheets API as follows? In this case, please enable Sheets API at Advanced Google services.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const { sheets } = Sheets.Spreadsheets.get(ssId, { fields: "sheets.columnGroups" });
  const requests = sheets.reduce((ar, { columnGroups }) => {
    if (columnGroups) {
      ar = [...ar, ...columnGroups.filter(({ depth }) => depth == 1).map(deleteDimension => {
        delete deleteDimension.depth;
        return { deleteDimension };
      })];
    }
    return ar;
  }, []).reverse();
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}