Search code examples
javascriptgoogle-apps-scriptgoogle-sheets

Delete all columns inside a Column Group with Google Apps Script

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.

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);
}

    Note:

    • This script removes the columns from the sheets. So, I would like to recommend testing this script using a sample Spreadsheet.

    References: