Skip to main content

Notifications

Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : CF/jRPpO6K0AJlKso1AZ9h
Power Platform Community / Forums / Power Automate - General Discussion / Office Script: Filteri...
Power Automate - General Discussion
Answered

Office Script: Filtering an excel table by active cell

Like (1) ShareShare
ReportReport
Posted on 7 Nov 2023 13:02:24 by AlexanderB88 7

Good Day,

 

a helpful voice in the general Excel help forum pointed me to ask my question here, I'll post the original topic here then explain what else I've tried so far.

 

I'm not a programmer, I have very few programming skills and while I managed one of the functions from VBA that my company needs in Excel Web / Office Script, the second function eludes me.

 

What I'm trying to do is sort a large table by clicking a button after I've selected a cell.

 

For example, I click in Column H the number "A53434", when I click the button, it should sort the table so only entries that have "A53434" in Column H. I feel like this shouldn't be so hard but I'm struggling quite a lot.

 

Here is the code a previous employee did in Excel VBA:

Sub SetFilter()

With ActiveSheet

.Range("A3:R3").AutoFilter

.Range("A3:R" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter _

Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value

End With

End Sub

 

So far I've gotten this far in Office Script, but I have very little idea what I'm doing or what I'm doing wrong:

 

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  let activeCell = workbook.getActiveCell().getValue() as string;

 

  console.log(activeCell);

//just to see if I actually have the active cell

  let activeColumn = workbook.getActiveCell().getAddress().split('')[0];

  let table = selectedSheet.getTables()[0];

//above line gives error

  var sortCl = table.getColumnByName(activeColumn);

 

}

 

Please help a struggling non-programmer out, thanks.

 

 

After a bit back and forth I've gotten this far and I think I understand it

 

 

 

 

function main(workbook: ExcelScript.Workbook) {

 let selectedSheet = workbook.getActiveWorksheet();

 let activeCell = workbook.getActiveCell();

 let activeColumn = activeCell.getAddress().split('')[10];
//this gives me the Columns letter as the full Adress is a 9 letter word and ! then the column index

 let activeColumnINdex = activeCell.getColumnIndex();
//this gives me the columns Index as a numerical value, which one I need? No idea.

 console.log(activeColumn)

 let table = selectedSheet.getTables()[0];

 let filter = table.getAutoFilter();

 filter.clearCriteria();

 filter.apply(selectedSheet.getAutoFilter().getRange(),activeColumnINdex, filter);
// I think I understand the apply() function, first I give it a range (all of it) then the Column Index (A=0, B=1 etc.) but then I try to give it a criteria, which ought to be the currently active cell and I don't know how to do that.
}

 

 

 

  • AlexanderB88 Profile Picture
    AlexanderB88 7 on 09 Nov 2023 at 06:38:03
    Re: Office Script: Filtering an excel table by active cell

    Good morning Sandeep,

    thank you for your reply, I think this is going in the right direction, however it still gives an error message in line 19 that "criteria" data type cannot be inferred. Furthermore it underlines AutoFilterCriteria as the namespace ExcelScript has no member of that name.

     

    €dit: Okay, I changed a little bit of your proposed solution and it works now, I changed:
    let criteria: ExcelScript.AutoFilterCriteria =

    to

    let criteria: ExcelScript.FilterCriteria =

     

    And 

    filter.apply(table.getRange(), activeColumnIndex + 1, criteria); // Column index is 1-based in apply function

    to 

    filter.apply(table.getRange(), activeColumnIndex, criteria); // Column index is 1-based in apply function

  • Verified answer
    sandeepstw Profile Picture
    sandeepstw 302 Super User 2025 Season 1 on 08 Nov 2023 at 07:51:43
    Re: Office Script: Filtering an excel table by active cell

    Hi There, 

     

    You can use this function - 

    function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet
    let selectedSheet = workbook.getActiveWorksheet();

    // Get the active cell and its value
    let activeCell = workbook.getActiveCell();
    let activeCellValue = activeCell.getValue() as string;

    // Get the active cell column index
    let activeColumnIndex = activeCell.getColumnIndex();

    // Get the first table on the sheet (assuming there is one and it is the correct one)
    let table = selectedSheet.getTables()[0];

    // Apply filter to the active column with the value of the active cell
    let filter = table.getAutoFilter();
    filter.clearCriteria(); // Clear any existing criteria
    // Create the filter criteria based on the active cell's value
    let criteria: ExcelScript.AutoFilterCriteria = {
    filterOn: ExcelScript.FilterOn.values,
    values: [activeCellValue]
    };
    // Apply the criteria to the active column
    filter.apply(table.getRange(), activeColumnIndex + 1, criteria); // Column index is 1-based in apply function
    }

     

     

    Please give kudos and mark as solution if it helps.  

      

    Thanks, 

    Sandeep Mishra 

  • AlexanderB88 Profile Picture
    AlexanderB88 7 on 08 Nov 2023 at 06:35:20
    Re: Office Script: Filtering an excel table by active cell

    Good morning Nived,

     

    I wasn't sure either but since the Excel forum pointed me here...

     

    Thank you for your solution, however the script doesn't work - the error it gives out is that no data type of any of the "let ..." can be infered. Furthermore I don't want to filter by a specific key word, I want to dynamically get whatever cell is selected to be searched for - i.e. I click Column H, Row 64530 which contains the word "Street Name 1", and if I hit the filter key, it filters my whole table for Column H entries containing "Street Name 1", if I click another cell and then hit the filter, it should clear all current filters, then search for whatever the new cell contains.

     

    The background is a large table full of customer data and electrical solar power connectors, it's a help database since the geo information system has an updating time, meaning if in the past few months a lot of solar power was put into a region, the geo information system does not have that information yet, we use an excel table to keep track of these changes (aside from a grid database that is always up to date but is hard to search in). 

    A big part of this excel table are old VBA scripts that allow us to instantly filter by electrical sub stations, since there are a lot of switching around, the normal filter function of excel is cumbersome and sometimes we have multiple dozens of new solar panels a day. One button filtering is very helpful.

  • Nived_Nambiar Profile Picture
    Nived_Nambiar 17,519 Super User 2025 Season 1 on 07 Nov 2023 at 15:33:54
    Re: Office Script: Filtering an excel table by active cell

    Hi @AlexanderB88 

     

    Not sure whether this is a power automate specific question, but for your question, could you try this office script:

     

    function sortTableByColumnHValue() {
 // Get the table range
 let table = worksheet.getTables().getItems()[0];
 let tableRange = table.getRange();

 // Get the data range of the table
 let dataRange = tableRange.getRangeBetweenHeaderAndTotal();

 // Get the column index for Column H
 let headerRange = tableRange.getHeaderRowRange();
 let columnIndex = headerRange.getValues()[0].indexOf("Column H Header") + 1; // Replace "Column H Header" with your actual header name

 // Get the value to filter/sort
 let filterValue = "A53434"; // Replace this with the value you want to filter by

 // Filter the table for rows containing the specific value in Column H
 let filteredData = dataRange.getValues().filter(row => row[columnIndex - 1] === filterValue);

 // Update the table range with the filtered data
 dataRange.setValues(filteredData);

 // Sort the table by the filtered column (if needed)
 tableRange.sort.apply([{ key: columnIndex, ascending: true }]);
}

     

     

    Thanks & Regards,

    Nived N 🚀

    LinkedIn: Nived N's LinkedIn
    YouTube: Nived N's YouTube Channel

    🔍 Found my answer helpful? Please consider marking it as the solution!
    Your appreciation keeps me motivated. Thank you! 🙌

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,670 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,011 Most Valuable Professional

Leaderboard

Featured topics

How to Ask for Help with Your Power Automate Workflow

Product updates

Microsoft Power Platform Community release plans