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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,148)