cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
victor_lion
Helper II
Helper II

Sending input to filter using Excel Scripts

Hello everyone!

 

I'm using the following script to filter a column on a spreadsheet 

 

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getWorksheet("Planilha1");
	
	selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 9, { filterOn: ExcelScript.FilterOn.custom, criterion1: "=*10/10*" });
}

 

But the problem here is that I can't figure out a way to use a variable to filter, I was trying to use as a string but had no success doing it. Maybe someone can help me.

6 REPLIES 6
Paulie78
Super User
Super User

I didn't test it, but you need to do something like this:

function main
(
  workbook: ExcelScript.Workbook,
  filterString: string
) 
{ 
  let selectedSheet = workbook.getWorksheet("Planilha1"); 
  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 9, { filterOn: ExcelScript.FilterOn.custom, criterion1: filterString }); 
}

noticed I added an input parameter of filterSring and then replaced =*10/10*" with the passed in value from filterString. If you change your Excel Script to the code above. Power Automate should prompt you to provide the value of the filter when you choose the script.

 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

victor_lion
Helper II
Helper II

Hi @Paulie78 I was just doing that, and it didn't work, I think it might have something to do with how the Power Automate sends the string to Excel...

Paulie78
Super User
Super User

When you say it didn't work, do you mean it didn't filter as you'd expect, or it produced an error?

Check that it passed the value first. If you add the line:

console.log(filterString);

To the script does it show the filterString in the output? 

victor_lion
Helper II
Helper II

It produced an error, and it does not show the filterString in the output, I'll try with console.log to see what happens.

Paulie78
Super User
Super User

I just tried it out and it worked fine. What value are you passing in? What error did you get? Might provide a good clue.

I'm sending this 

 

{
    "host": {
        "connectionReferenceName": "shared_excelonlinebusiness_1",
        "operationId": "RunScriptProd"
    },
    "parameters": {
        "source": "https://datora.sharepoint.com/sites/RPA",
        "drive": "b!0G8l10EXt0yKPly2Lz_n1rVDPl6Fe1JBsl8Z5h3VZ496KqTxyjw9SYAyME17eECF",
        "file": "01MUDNWAWR3ITBEEI3PBEJ2MOIU5NPT5LY",
        "scriptId": "ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F015PNFAXI5NFVXPRIZEBHLOKMY53R3YHXO",
        "ScriptParameters/filterString": "=*01/10*"
    }
}

 

 

And receiving this error:

Não foi possível executar o script. Tente novamente.
Erro do Office JS: Line 5: AutoFilter apply: The argument is invalid or missing or has an incorrect format.
clientRequestId: 09eeb6a2-927f-4ce3-b896-866eaeedfbfd

 

Also I should mention that I'm running this script, with a few additions to the first one:

 

function main(workbook: ExcelScript.Workbook, filterString: string)
{
    let selectedSheet = workbook.getWorksheet("Planilha1");
    // Apply custom filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 9, { filterOn: ExcelScript.FilterOn.custom, criterion1: filterString });
  console.log(filterString);
  
  let range = selectedSheet.getUsedRange(true).getVisibleView().getRange();
  return range;
}

 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (578)