CODEWITHSUNDEEP
optimizationgoogle-apps-scriptgoogle-sheetsshow-hide
Xzila
Xzila

Reputation: 237

Optimize Google Script for Hiding Columns

These two scripts are incredibly slow. I work with a data set of about 32 columns by 1000 rows ( growing pretty rapidly ).

I've read and even used code for treating data like an array so that you can make only one call to google-services, but I'm not sure how that can help me with this case.

I need to hide certain columns depending on which person is using the google sheet

Here is the actual code:

function HideColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A1");
  sheet.hideColumn(range);
  range = sheet.getRange("C1:E1");
  sheet.hideColumn(range);
  range = sheet.getRange("G1");
  sheet.hideColumn(range);
  range = sheet.getRange("I1");
  sheet.hideColumn(range);
  range = sheet.getRange("K1");
  sheet.hideColumn(range);
  range = sheet.getRange("Q1:Z1");
  sheet.hideColumn(range);
  range = sheet.getRange("AC1:AG1");
  sheet.hideColumn(range);
}

function ShowColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A1");      
  sheet.unhideColumn(range);
  range = sheet.getRange("C1:E1");
  sheet.unhideColumn(range);
  range = sheet.getRange("G1");
  sheet.unhideColumn(range);
  range = sheet.getRange("I1");
  sheet.unhideColumn(range);
  range = sheet.getRange("K1");
  sheet.unhideColumn(range);
  range = sheet.getRange("Q1:Z1");
  sheet.unhideColumn(range);
  range = sheet.getRange("AC1:AG1");
  sheet.unhideColumn(range);
}

Upvotes: 2

Views: 3237

Answers (3)

user555121
user555121

Reputation:

You can use batch request to Sheets API (enable advanced service first) to hide/unhide multiple columns in one API call:

function changeHideForColumnsOptimized(ssId, sheetId, columns, hide)
{
  let requests = [];
  
  columns.map(col => {
    
    // get column index/indexes
    let colArray = col.split(':');

    let startIndex = letterToColumn(colArray[0]) - 1;
    let endIndex = letterToColumn(colArray[colArray[1] ? 1 : 0]) - 1;

    let hiddenByUser = hide ? true: false;
    requests.push({
      updateDimensionProperties: {
        range: {
          sheetId: sheetId,
          dimension: 'COLUMNS',
          startIndex: startIndex, // start index is inclusive
          endIndex: endIndex + 1 // end index is exclusive
        },
        properties: {
          hiddenByUser: hiddenByUser
        },
        fields: 'hiddenByUser'
      }
    })
  });

  Sheets.Spreadsheets.batchUpdate({requests: requests}, ssId);
}

// via https://stackoverflow.com/a/21231012/555121
function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

An then use it like this:

function HideColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  let columnsToHide = ['A', 'C:E', 'G', 'I', 'K', 'Q:Z', 'AC:AG'];
  changeHideForColumnsOptimized(ss.getId(), sheet.getSheetId(), columnsToHide, true);
}

function ShowColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  let columnsToUnHide = ['A', 'C:E', 'G', 'I', 'K', 'Q:Z', 'AC:AG'];
  changeHideForColumnsOptimized(ss.getId(), sheet.getSheetId(), columnsToUnHide, false);
}

This answer is based on these two questions:

Hide column with Sheets API call

Convert column index into corresponding column letter

Upvotes: 0

AdamL
AdamL

Reputation: 24609

For the ShowColumns() script, are you just needing to unhide all columns in the sheet? If so, you could at least simplify that one a bit:

function ShowColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("1:1");
  sheet.unhideColumn(range);
}

Upvotes: 1

Serge insas
Serge insas

Reputation: 46794

unfortunately hiding columns is a "spreadsheet only" function, no way to make it faster or in batch...

maybe you could imagine a custom UI (built with UiApp or HTMLService) to show only the user relevant data ? but that might not be possible, depending on how much you need spreadsheet specific features..., not speaking of the work it might represent.

Upvotes: 3

Related Questions