Automatic hiding columns based on cell value in google spreadsheets

Question

I have too many columns in the sheet and so many columns to hide as well, but while executing the script it runs half way and stopped saying maximum time reached.

When I again tried to execute it stopped exactly where I stopped previously. So I would like to have some customization that if the column is already hidden can skip that column and work on the others.

Is there any way to do it.

Here is the code I used:

function hideColumns() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName('ANALYTIC');
    var data = sh.getRange('6:6').getValues();
    var numCols = sh.getMaxColumns();
    var numRows = sh.getMaxRows();

    for(var i = 0; i <= numCols; i++){
        if(data[0][i] == ""){
            sh.hideColumns(i+1);
        } else {
            sh.unhideColumn(sh.getRange(1, i+1, numRows, 1));
        }
    }
}

Please help me.

Answer 1

If your sheet has under 10,000 columns (PropertiesService limit) you can use this script:

function hideColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('ANALYTIC');
  var data = sh.getRange('6:6').getValues();
  var documentProperties = PropertiesService.getDocumentProperties()
  var documentPropertiesVals = documentProperties.getProperties();
  var numCols = sh.getMaxColumns();


  for(var i = 0; i < numCols; i++){
    if (!(cPlusInt(i) in documentPropertiesVals)) {
      documentPropertiesVals[cPlusInt(i)] === 'empty';
    }

    if (documentPropertiesVals[cPlusInt(i)] === 'hidden' && data[0][i] == "") continue;
    if (documentPropertiesVals[cPlusInt(i)] === 'unhidden' && data[0][i] != "") continue;

    if(data[0][i] == ""){
      sh.hideColumns(i+1);
      documentProperties.setProperty(cPlusInt(i), 'hidden')
    } else {
      sh.unhideColumn(sh.getRange(1, i+1, 1, 1));
      documentProperties.setProperty(cPlusInt(i), 'unhidden')
    }
  }  
}

function cPlusInt(num) {
  return 'c'+num.toString()
}

You at first you may need to run this few times (many write operation to PropertiesService may be sluggish) but later it's "blazingly" fast (0.1 s per new column).

Better answer using @Jack Brown's idea

It is possible to make single save to PropertiesService - you would need to incorporate time limit from @Jack Brown's answer, this way:

function hideColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('ANALYTIC');
  var data = sh.getRange('6:6').getValues();
  var documentProperties = PropertiesService.getDocumentProperties()
  var documentPropertiesVals = documentProperties.getProperties();
  var numCols = sh.getMaxColumns();

  var startTime = new Date().getTime()    
  var ms5min = 5*60*1000 //5min in millseconds      

  for(var i = 0; i < numCols; i++){
    if (!(cPlusInt(i) in documentPropertiesVals)) {
      documentPropertiesVals[cPlusInt(i)] === 'empty';
    }

    if (documentPropertiesVals[cPlusInt(i)] === 'hidden' && data[0][i] == "") continue;
    if (documentPropertiesVals[cPlusInt(i)] === 'unhidden' && data[0][i] != "") continue;

    if(data[0][i] == ""){
      sh.hideColumns(i+1);
      documentPropertiesVals[cPlusInt(i)] = 'hidden'
    } else {
      sh.unhideColumn(sh.getRange(1, i+1, 1, 1));
      documentPropertiesVals[cPlusInt(i)] = 'unhidden'
    }

    var curTime = new Date().getTime()
    var elapsed = curTime-startTime
    if (elapsed >= ms5min){
      break;      
    }    
  }      

  documentProperties.setProperties(documentPropertiesVals)

  if (elapsed >= ms5min){
    SpreadsheetApp.getUi().alert("Please restart Run, exceeded 5min mark")
  }
}

cPlusInt function explanation

cPlusInt is necessary because of weird problems with google's PropertiesService. Object gotten from PropertiesService returns undefined at integer keys. To see problem use this code:

function test() {
  var obj = {};
  for (var i=0; i<10; i++) {
    obj[i.toString()] = 'aa' + i.toString();    
  }

  var documentProperties = PropertiesService.getScriptProperties();
  documentProperties.deleteAllProperties();
  documentProperties.setProperties(obj);
  obj = documentProperties.getProperties();

  Logger.log(obj)
  for (var i in obj) {
    Logger.log('%s %s %s', i, obj[i], i === '1');
  }
}

Answer 2

You can use the documentProperties to store the last column before the end of the execution. To prevent the run from stopping abruptly you stop the run a little prematurely at 5min (execution will terminate at 6min mark) mark and store the column number in the documentProperty. You also display an alert asking you rerun the script.

Then retrieve the column number on the next run and start from there. If the program gets through the complete loop you delete the said properties. So you start from zero if you rerun the script next time.

Below is the code for the same

function hideColumns() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName('ANALYTIC');
    var data = sh.getRange('6:6').getValues();
    var numCols = sh.getMaxColumns();
    var numRows = sh.getMaxRows();
    var docProp = PropertiesService.getDocumentProperties()
    var startCol = Number(docProp.getProperty("startCol"))    //if there is no propert called startCol will return Null, Number(Null) = 0
    Logger.log(startCol)
    var startTime = new Date().getTime()    
    var ms5min = 5*60*1000         //5min in millseconds

    for(var i = startCol; i <= numCols; i++){
        if(data[0][i] == ""){
            sh.hideColumns(i+1);
        } else {
            sh.unhideColumn(sh.getRange(1, i+1, numRows, 1));
        }
      var curTime = new Date().getTime()
      var elasped = curTime-startTime

      if (elasped >= ms5min){
        docProp.setProperty("startCol", i)
        SpreadsheetApp.getUi().alert("Please restart Run, exceeded 5min mark")
        return
      }
    }
  Logger.log(elasped)
  docProp.deleteAllProperties()
}