Hi @Anonymous ,
Thanks for the additional context!!
To try to make things less complex, I will share in this post only a part of the solution (the Office Scripts creation and the integration with Power Automate for only one vendor). After you have this first part solved, we can move forward to the recurrent trigger and the vendors file mapping.
It is possible that you find some errors when replicating it in your environment: don't worry, you can share those errors and we will help you to solve them. Also, after we finish this solution, I can share more detailed instructions about what each line of code does.
Overview on the files:
I have a folder called 'Delivery Schedule' (highlighted in blue). Inside it, there are subfolders by date, in the 'yyyy-MM-dd' format (highlighted in yellow). Those subfolders contains Excel files from each vendor (highlighted in green). For this first part of the example, I'm using 2 subfolders only ('2023-07-10' and '2023-07-11'), with 2 vendor files inside it (called 'File1' and 'File2').
For now, we will be using only the 'File1' from both dates, which structure is represented below (first image from July 10th, second from July 11st):
Note: During this post, I will be referring to the '2023-07-10' Excel file as 'Old file' and to the '2023-07-11' Excel file as 'New file'
Step 1 - Create your "reading" Office Script:
We will first create a Office Script to read the whole data from the Old File and return it to Power Automate. So the first step is to create an Office Script - you can do it in any Excel file, but I recommend you to do it in your Old File.
To create it, go to 'Automate' tab in your Excel (highlighted in yellow), click 'New Script' (highlighted in green) and then a sidebar may appear (highlighted in blue):
If you have any trouble to open the Code Editor, you can ask here or check for more detailed instructions in the following links:
http://digitalmill.net/2023/06/19/get-started-with-office-scripts/
https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel
Your code editor may look different from mine: don't worry, it is just a style configuration 🙂
You can replace the whole text inside the Code editor for the following code:
function main(workbook: ExcelScript.Workbook) {
const ws = workbook.getWorksheet('Sheet1')
let rowIndex = 1
for(let i = 0; i< 50; i++){
const rangeValue = ws.getRangeByIndexes(rowIndex, 0, 1, 1).getValue()
if (rangeValue === ""){
break
}
console.log(rangeValue)
rowIndex++
}
const allValues = ws.getRangeByIndexes(0, 0, rowIndex+1, 4).getValues()
console.log(allValues) //you can remove this line after the first test
return {"deliveries": allValues}
}
Click in 'Run'. A dropdown may appear in the 'Output' below the 'Code editor'. Expand the dropdown to make sure that the data is being captured, specially the Material Code and Qty. If the data is being correctly displayed, you can remove the code line 'console.log(allValues)'.
Note: in the code above, we are assuming that your occupied range starts in cell 'A1', and your sheet name is 'Sheet1'. Let me know if something is different from your file template, as it will require some changes in the code.
Step 2 - Create the "highlight" Office Script:
Now you will repeat the same process in the New File, but entering the following code:
function main(workbook: ExcelScript.Workbook, deliveries:object) {
const parsedArr = deliveries['deliveries']
console.log(parsedArr)
const ws = workbook.getWorksheet('Sheet1')
let rowIndex = 1
for (let i = 0; i < 10; i++) {
const rangeValue = ws.getRangeByIndexes(rowIndex, 0, 1, 5).getValues()
if (rangeValue[0][0] === "") {
break
}
for (let ind = 0; ind < parsedArr.length; ind++){
if (rangeValue[0][0] === parsedArr[ind][0] && rangeValue[0][2] === parsedArr[ind][2] && rangeValue[0][3] === parsedArr[ind][3] && rangeValue[0][1] !== parsedArr[ind][1]) {
ws.getRangeByIndexes(rowIndex, 0, 1, 5).getEntireRow().getFormat().getFill().setColor("yellow")
console.log(rangeValue[0])
}
}
rowIndex++
}
}
Here you do not need to 'run' the code, as it will need the data from the other file as input to work.
Step 3 - Creating Power Automate flow:
Create a flow with a manual trigger (we will replace the trigger later, to make it run in a daily basis). Add a 'Run Script' action from 'Excel' connector, and enter the Old File path (highlighted in yellow) and select the "reading" script from the dropdown (highlighted in green):
You can save the flow and run it just for test purposes. If everything is running correctly, you may receive this output from the action:
Then you can add a second 'Run script' action, but this time selecting the New File and the "highlight" script. Different from the first 'Run script', in the New File one you will see an additional field called 'deliveries', where you may enter the input for your Office Script. Enter the 'result' from the first 'Run script' action as dynamic content (highlighted in blue):
Step 4 - Running the flow:
Save your flow and run it. It may take some seconds to run. After that, your New File will look like this:
Conclusion
As mentioned before, it is possible that you find some trouble during this process, specially in this initial part. Please, do not hesitate and posting your questions/errors here. Once you achieve the result from Step 4, I will send a new answer with the flow structure to run all vendors files and apply the scripts in a daily basis.
Share
Report
All responses (
Answers (
2
