I have been playing around with Office Scripts lately, specifically Office Scripts for Excel.
I wanted to create a Power Automate Flow that populated an Excel spreadsheet and then have the pivot table and chart based on the sales data.
The spreadsheet consisted of two worksheets, one for car sales data, and another for a pivot table and a bar chart displaying sales by car make.
The Power Automate flow was pretty straight forward to create.
One of the tricky parts was handling the splitting of the data. I needed to split the file contents by row and then split the row into column values.
I found this article Power Automate: How to parse a CSV File to create a JSON array (tachytelic.net) which provided some excellent guidance for how to accomplish my task.
Since my file was Windows, I had to use split(body('Get_file_content'), decodeUriComponent('%0D%0A')) to split my file into rows.
Seems like the Excel Table had to have at least one row by default. I didn’t want to update it, but rather delete it at the end of my script, so I added a column called __PowerAppsId__ that I would use as a Key Column to delete the row.
The RowIndex variable would be incremented as I added a new row to the Excel file.
When I ran it the first time I noticed a couple of things:
- The default row would only be deleted some of the time
- The pivot table and chart based on the pivot table did not refresh automatically
This is where Office Scripts came into play.
I created an Office Script, called Refresh.osts, that would refresh the data, so the chart would be updated, and delete the default row.
|
1 2 3 4 5 6 |
function main(workbook: ExcelScript.Workbook) { workbook.refreshAllDataConnections(); workbook.refreshAllPivotTables(); let table = workbook.getTable("Cars"); table.deleteRowsAt(0, 1); } |
The other thing I wanted to do, just for kicks and grins, was see if I could modify properties of the Chart, for example, I wanted the chart axis Maximum to be 100,000.
I created another Office Script to do this called Update Chart.osts.
|
1 2 3 4 5 6 7 |
function main(workbook: ExcelScript.Workbook, maximumBounds: number) { let worksheet = workbook.getWorksheet("Purchases By Make"); let chart = worksheet.getChart("Chart 1"); chart.getAxes().getValueAxis().setMaximum(maximumBounds); } |
I added a couple of actions to my flow to run the Office Scripts.
I ran my flow again, and everything looked perfect!
Office Scripts give you a ton of control over your Excel documents, highly recommend you check them out!
Related files
- Car Report Sample.xlsx – Example of the final output
- Cars Report.csv – Sales data
- Cars Report.xlsx – Excel spreadsheet containing sales data and chart
- Refresh.osts – Office Script to refresh data and delete empty row
- Update Chart.osts – Office Script to change
Maximumproperty on the chart - CarsReportGenerate_20230728164230.zip – Power Automate flow
Discover more from Matt Ruma
Subscribe to get the latest posts sent to your email.