Sending input to filter using Excel Scripts

[victor_lion]

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.

[Paulie78]

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]

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]

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]

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]

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.

[victor_lion]

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;
}