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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,982)