javascriptgoogle-sheetsgoogle-apps-script

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.

    Modification points:

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

    References: