CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Colin Webber
Colin Webber

Reputation: 1

How do I identify a word within a cell in order to trigger an action?

I have a set of spreadsheets with lots of redundant columns. I want to hide all columns who's header Includes the word "mark" (rather than = "mark". And get the same thing to happen on ALL sheets in the file

I can identify the columns using a match and a helper cell, I know how to hide cells, its hiding according to cell content - a word within a sentence, and checking a variable range of columns. I have based code on several similar but also quite different examples on this site

function hideColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn()+1;

  Logger.log(lastCol);

  // iterate over columns
  for (var i = 1; i < lastCol; i++) {
     if (data.getCell(1, i).match() ("mark"))
     {
        sheet.hideColumns(i);
     }
  }
}

I'm getting function match in object range error.

Upvotes: 0

Views: 118

Answers (1)

Cooper
Cooper

Reputation: 64092

This function allows you to use regular expressions in your search if you wish to. It also assumes the header row is 1 and the default search string is "mark". It will search through all sheets in the spreadsheet using the same defaults in each sheet. Of course you can edit the defaults as you prefer.

function hideColumnsInWhichHeaderContainsString(headerRow,string,useregex) {
  var useregex=useregex||false;
  var headerRow=headerRow||1;
  var string=string||"mark";
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var rg=sh.getRange(headerRow,1,1,sh.getLastColumn());
    var rgA=rg.createTextFinder(string).useRegularExpression(useregex).matchCase(true).findAll();
    for(var j=0;j<rgA.length;j++) {
      sh.hideColumns(rgA[j].getColumn());
    }    
  }
}

I performed only limited testing on it.

The below function is working on data that I copied from your sheet.

function hideColumnsInWhichHeaderContainsString1(setupA) {
  var setupA=setupA || [{headerRow:1,string:"Mark",useregex:false,matchcase:true,visibility:"hide"},{headerRow:1,string:"MILESTONES",useregex:true,matchcase:false,visibility:"hide"},{headerRow:1,string:"MILESTONES- Grade",useregex:false,matchcase:true,visibility:"show"}];
  if(setupA.length>0) {
    var ss=SpreadsheetApp.getActive();
    var shts=ss.getSheets();
    for(var i=0;i<shts.length;i++) {
      var sh=shts[i];
      for(var k=0;k<setupA.length;k++) {
        var rg=sh.getRange(setupA[k].headerRow,1,1,sh.getLastColumn());
        var rgA=rg.createTextFinder(setupA[k].string).useRegularExpression(setupA[k].useregex).matchCase(setupA[k].matchcase).findAll();
        for(var j=0;j<rgA.length;j++) {
          if(setupA[k].visibility=="hide") {
            sh.hideColumns(rgA[j].getColumn());
          }else{
            sh.showColumns(rgA[j].getColumn());
          }
        }    
      }
    }
  }
}

This will hide all of the columns for each sheet in the spreadsheet:

function hideMyColumn() {
  var ss = SpreadsheetApp.getActive();
  ss.getSheets().forEach(sh => {
    let lc = sh.getLastColumn();
    if (lc > 0) {
      sh.getRange(1, 1, 1, lc).getValues().flat().forEach((e, i) => {
        if (e.includes("mark")) {
          sh.hideColumns(i + 1)
        }
      })
    }
  })
}

Regular Expression Reference

Upvotes: 1

Related Questions