[SOLVED] Hide or Show multiple columns in one command

Hide or Show multiple columns in one command

I am currently running the following to hide columns depending on the value of a specific cell. It works, but I'm wondering if there's a more efficient way to achieve this? I notice there is 2 commands hideColumn and hideColumns which suggests there's a way of hiding more than one column in a single command:

  resultsSheet.hideColumns(11);
  resultsSheet.hideColumns(18);
  resultsSheet.hideColumns(19);
  resultsSheet.showColumns(26);
  resultsSheet.showColumns(27);
  resultsSheet.showColumns(28);
  resultsSheet.showColumns(29);

Solution

I believe your goal as follows.

You want to reduce the process cost of the following script.

  resultsSheet.hideColumns(11);
  resultsSheet.hideColumns(18);
  resultsSheet.hideColumns(19);
  resultsSheet.showColumns(26);
  resultsSheet.showColumns(27);
  resultsSheet.showColumns(28);
  resultsSheet.showColumns(29);

Modification points:

When hideColumns and showColumns are used, the arguments are columnIndex, numColumns. In this case, when the columns you want to hide and show are continuously existing like the column "A", "B" and "C", you can achieve this using one hideColumns and showColumns. But when the columns you want to hide and show are scattered, multiple hideColumns and showColumns are required to be used.
In this case, in order to achieve your goal using one call, I would like to propose to use the method of batchUpdate in Sheets API. When Sheets API is used, both hideColumns and showColumns can be used by one API call.

The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet you want to use. Before you use this script, please enable Sheets API at Advanced Google services. And, please set the sheet name.

function myFunction() {
  const hideColumns = [11, 18, 19];
  const showColumns = [26, 27, 28, 29];
  const sheetName = "Sheet1";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetId = ss.getSheetByName(sheetName).getSheetId();
  const requests = [];
  
  // Create requests for the hide columns.
  if (hideColumns.length > 0) {
    hideColumns.forEach(c => 
      requests.push({ updateDimensionProperties: { properties: { hiddenByUser: true }, range: { sheetId: sheetId, dimension: "COLUMNS", startIndex: c - 1, endIndex: c }, fields: "hiddenByUser" } })
    );
  }

  // Create requests for the show columns.
  if (showColumns.length > 0) {
    showColumns.forEach(c =>
      requests.push({ updateDimensionProperties: { properties: { hiddenByUser: false }, range: { sheetId: sheetId, dimension: "COLUMNS", startIndex: c - 1, endIndex: c }, fields: "hiddenByUser" } })
    );
  }

  // Request to Sheets API using the created requests.
  if (requests.length > 0) Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
}

In above sample script, when only hideColumns is declared, the columns are hidden using hideColumns. when only showColumns is declared, the columns are shown using showColumns. When both hideColumns and showColumns are declared, the columns are hidden and shown using hideColumns and showColumns.
- Above process is done by one API call.
The values of hideColumns and showColumns are from your script

Hide or Show multiple columns in one command

Modification points:

Sample script:

References: