Excel Script for hiding Columns containing a defined cell

Question

Hello,

im looking for help with my excel-script I'd like to use in my sharepoint file. I want the script to search the current workbook for all the cells containing the words "Funktion_ausblenden". Then, I'd like to hide the entire column, if it contains the cell with these words in it.

Unfortunately im new to the subject and don't know how to finish this script. I think I got the part where it searches for the cells containing the words. But I don't know how to hide the columns. Help would be much appreciated.

My current script:

function main(workbook: ExcelScript.Workbook) {
// Get the current, active worksheet.
let worksheet = workbook.getActiveWorksheet();
let noCells = worksheet.findAll("Funktion_ausblenden", { completeMatch: true });
//Spalten ausblenden
noCells.getEntireColumn().setColumnHidden (true)
}

Answer 1

Hello @K, Y ,

You can try something like this:

   function main(workbook: ExcelScript.Workbook) {  
     // Get the current, active worksheet.  
     let worksheet = workbook.getActiveWorksheet();  
     let noCells = worksheet.findAll("Funktion_ausblenden", { completeMatch: true });  
     if (noCells) {  
       //Spalten ausblenden  
       noCells.getAreas().forEach(range => range.setColumnHidden(true));  
     }  
   }  

worksheet.findAll() returns a RangeAreas object. Calling getAreas() on this object can return an array of Range objects, where you can iterate through and hide their columns accordingly.