Hi @randomuser101 ,
There are different ways to solve your problem with the scripts. I don't know exactly how does your current file looks like, but I prepared the example below just to showcase how can you use the Office Scripts to return data based in some filters to Power Automate.
If this approach makes sense for you, please share a sample of your file with fake data and inform which data would you like to return from there. The general logic of your script must be similar to the logic below
Let's assume that we want to return the most recent sales qty of a specific product code (which will be informed in Power Automate). As you can see, there are duplicates in the product_code column, so we will need to filter it first and then "sort by date" to pick the most recent:
To perform this task, I created the getRecord script (highlighted in yellow):
Here is the full code of the script (I didn't include comments in the code at this time, but I can do it when we prepare a code for your use case):
function main(workbook: ExcelScript.Workbook, productCode:number) {
const tbl = workbook.getTable('Table1').getRangeBetweenHeaderAndTotal().getValues()
const selectedProductCode = tbl.filter(e => e[0] === productCode)
let foundRecord:(string | number)[] = null
for(let i = 0; i < selectedProductCode.length; i++){
if(foundRecord === null){
foundRecord = selectedProductCode[i]
} else if (foundRecord[2] < selectedProductCode[i][2]){
foundRecord = selectedProductCode[i]
}
}
foundRecord[2] = convertDate(foundRecord[2])
const recordObj = {
product_code: foundRecord[0],
sales_qty: foundRecord[1],
created_at: foundRecord[2]
}
return recordObj
}
function convertDate(serialDate:number){
const excelEpoch = new Date(1899, 11, 31)
const milliseconds = (serialDate - 1) * 24 * 60 * 60 * 1000;
const date = new Date(excelEpoch.getTime() + milliseconds);
return date;
}
In Power Automate, I add a 'Run Script' action, select the desired file and script (highlighted in green) and, once I do it, a new field will be displayed to enter the product code to be found (highlighted in blue).
After running this action, Power Automate receives the data related to the most recent entry of product_code 2 inside the 'result' property, in a JSON format.
Some additional comments:
- You can have more than one, or none inputs for your script.
- You have a lot of freedom to define the content of the JSON returned by 'Run Scripts'.
- There is a limitation for this action: all scripts must run in a maximum of 120 seconds. The Script above took 4 seconds to run.
