Office Script: filter table by selected cell

Question

Hello,

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.

Answer 1

You may try this one.

================

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

let activeCell = workbook.getActiveCell();

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

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

let filter = table.getAutoFilter();

filter.clearCriteria();

filter.applyFilter(activeColumn, activeCell.getValue());

}

================

This code gets the active cell and column, then gets the first table on the active sheet. It then clears any existing filters and applies a new filter to the selected column with the value of the active cell.

You may also go to Microsoft Power Automate Community - Power Platform Community to post a new thread, which is the specific channel to handle Office script issue.

Answer 2

I'm also new to Office Script. I'd suggest you create a new thread at Microsoft Power Automate Community - Power Platform Community which is the specific channel to handle Office script issue.

Thank you for your understanding.

Answer 3

Hello Snow Lu, thank you again

I removed all Auto Filters and replaced it with a table, the next error message reads

Line 15: filter.applyFilter is not a function

Answer 4

The error message suggests that the table variable is undefined, which means that there is no table in the active worksheet. You may want to check if there is a table in the worksheet before trying to access it.

Regarding the "split()" function, it is a built-in JavaScript function that splits a string into an array of substrings based on a specified separator. In this case, the separator is an empty string '', which means that the string is split into an array of individual characters. The "[0]" at the end of the expression is used to get the first character of the resulting array, which represents the column letter of the active cell.

Answer 5

Hello Snow Lu, first of all thanks for the prompt answer;

the code gave out an error:

1. Line 11: Cannot read properties of undefined (reading 'getAutoFilter')

Line 11 is
let filter = table.getAutoFilter();

In the line
  filter.applyFilter(activeColumn, activeCell.getValue());

the word applyFliter is underscored with the hint "any property "applyfilter" does not exist on type "autofilter""

I am trying to understand what you did, so far I am having trouble understanding the split(arg) function, but thank you so much for the solution so far