This post is part of a series of posts. Please use the links below to navigate through the related posts:
- Macro Templates Introduction
- Deployment
- Dataverse Tables
- Custom Page Dialog
- Power Automate Flow
- Javascripts
- Template File
- Final Notes
- Git Sources
There is a single cloud flow in the solution – the purpose of that flow is to create a small excel spreadsheet in Sharepoint that has all the data the document template may need.
On the Sharepoint side, here is how it’s organized:
All generated spreadsheets will be stored in the “Documents/MacrosTemplates/Data”(Note: not everything is parameterized in the solution at this point, so make sure to create those subfolders exactly that way) folder by copying that DataExportTemplate.xlsx you see on the screenshots above (you’ll find it in git, too) into the Data folder (under a unique name each time) and adding some data to the file:
What kind of data is going to be stored in those spreadsheets depends on the specific templates of course.
The copying part happens in the following actions of the cloud flow:
In order to add data to the spreadsheet, the flow uses “Add a row into a table” action from the Excel connector:
It’s a bit tricky there, since, while “Location” parameter is essentially a “text”, those document library, file, and table parameters are not that straightforward. Keep in mind that they have to be “dynamics”, since it’s a different file every time.
With the table, since the spreadsheets are always created as copies of the same “template spreadsheet”, it’s the same table id all the time. So you just need to manually pick the table once, then use “peek code” on the action and copy the value which you can, then, reuse it.
Document library would have been the same for all the generated files for a given sharepoint site, it it’s going to be different between different sites. And File parameters is always different. So there needs to be a way to figure those two out dynamically, and that’s what this action does:
With that, I can get document library and file parameters like this:
Library id: body(‘Send_an_HTTP_request_to_SharePoint’)[‘parentReference’]?[‘driveId’]
File id: body(‘Send_an_HTTP_request_to_SharePoint’)[‘id’]
Which produces these results:
And the rest is straightforward. By this time we have a small excel file in stored in the “Data” folder in Sharepoint, it has whatever data is needed there, it has a very specific name… so we just need to create a record in the Template Downloads table so that the javascript that started all this knew which template to download and which file name to use when downloading (since, remember, there is a portion of that file name that matches the name of the generated spreadsheet, and, than way, the macro knows which spreadsheet it should be using to read the data):
