In this article I will explain how you can export your data from your Power Apps app and create customized excel documents from an excel template.
Sometimes you have some requirements to send data on specific formats and you are finding some limitations when you try to export your data to custom formats. You can use the most popular action of standard connectors but, for some examples, they are not enough.
For example, to customize an excel file, if you use Power Automate to export the data, you have a task to add rows to an excel table, but can you use it to merge cells or customize them? The answer is no… you can add rows to a specific table, but you can’t customize the rows when you insert them.
Let’s look for a practical example: Daily, you are filling the time you spend working in your customers projects tasks. You have a Power Apps application to create records with tasks that you made and how much time you spent doing that. This Power Apps app can be integrated with dataverse, project online, azure devops or other convenient data source.
One time per week, for specific customers, you need to export your timesheet information to Excel, before you send it. Your excel file should be like your template, you have an header, table with merged cells if they are with the same values, the total of hours and some customizations on your table cells.
So, how can we make it?
Using the office scripts! In your excel file you have a tab called Automate and you can add scripts to your file. There are already some templates to use where you can record the actions you want to make, but you can also create your own script (using JavaScript and TypeScript) to make what you want automatically.
Let me show you how!
I started creating a sample app to simulate the timesheet records. On this screen we can see all the hours spent on this customer, which projects and tasks the people worked on.
We can put some filters on this page to define the period to export and save the date when the people spend the time, but to show you an example I think this information is enough. :)
So, we need to export this information to an excel file template to send to our Customer A, the customer we are seeing on the app.
In our customer template file, we should have the header whith your logo, customer name (should be dynamical from the app) and the customer logo. After it, we have our table.
But we don’t want just to fill the table. We also want to add the rows, but the first column (Project column) should be merged by project, we need to create a border on the table, the rows should have a black border and the last row should be a total of hours with that customer.
So, let's create the script! :)
When you access to the excel file, you have a tab of automate. There you can find some script templates, but let’s create our own script.
When we create a new script we have an empty script only with the main function:
Now, let’s create some code! :)
First, we should define which parameters are needed. It will be necessary the customer’s name, and array with the project tasks information and the total of hours (because we already have the value in our application, we don’t need to recalculate it here). Our project task information has a specific structure: project name, task name, person and hours. We can define an interface to help the way we receive and use the information:
Now we can use this structure as parameter of our main function.
After we defined our parameters, we can start to code what we need inside the function.
We start to select which worksheet we want to use. After it, we can set the customer name in the header. We defined the variable rowStart to define where we start our table. The variables mergeValuesColProject and firstmergeposition will be used to make the merge of cells to the project column. The array of values is initialized with the first project name value and the first position is 0.
Now we can start the cycle to make the action for each row of our table:
Inside the for cycle, we made: call a function to set table values (row 18), merge the cells of project (row 20 - row 44), check if is the last row to create the total row (row 46 – row 49) and we call a function to set the border of the cells (row 51).
Let’s see the code step by step!
First, call the functions to set the table values. We sent to the function the current workbook, the row number, the project task array and the index (the number of our row inside the array).
Second, we make merge of the cells. We get the value of the current project cell name and we need to compare with the last project cell name. But here we have a problem, when we merge cells the value stays in the first cell, for example, if we merge cell A4 with cell A3, the value will be in cell A3 and the cell A4 will be empty, so we can’t compare with the last cell only.
That’s why we have this if:
We check if the value of the last cell is equals than the current cell OR if the last cell is empty AND the value of the current cell is the same of the last position merged saved on our array, we will make the merge and save the record on our merge values array. Otherwise, we will restart the merge array and the first merge variable and don’t merge the cell.
Third, we will set the table totals. If we are in the last row of the table, we will call the function to set the total:
We start to insert the values on the row and we set the fill color and the alignment of the cells.
Four and last, we change the border of our table.
On this print screen we can see the set of inside horizontal and vertical border and we have the same code to set the bottom, top, right and left border.
Our script is ready to use! Let’s call in Power Automate! :)
Our trigger is with PowerApps because we want to call this flow from our app. We send 3 parameters: Customer name, JSON with project tasks and the total of hours.
The first action should be to parse the JSON of project tasks to use in our script.
Then we can call the script:
At the end, we can send an answer to our application saying if the excel was successfully exported.
Let’s run everything! Don’t forget you need to set the action of your button in your app. Change the on select action to call your power automate flow and send it the required parameters.
For our example, the result is this excel file:
For conclusion:
You can create office scripts to make many actions in your excel files! In this article we created a script, and we called it with power automate, but the information that we used on it can be provided from other systems than a Power Apps app. You have some freedom on the code to create what you want and customize your file like you want, you just need to create code for it!
I hope you enjoyed this article! :)
If you need some help, feel free to talk with me!
Below you can find the complete code:
function main(workbook: ExcelScript.Workbook, customerName:string, projecttasks: projectInformation [], total: number) {
let selectedSheet = workbook.getActiveWorksheet();
// Set customerName
selectedSheet.getRange("B3:C3").setValue(customerName);
//set table start row
const rowStart = 8;
//array with project values to merge
let mergeValuesColProject: string[] = [projecttasks[0].projectName];
// first position to start the merge
let firstmergeposition = 0;
for (let i = 0; i < projecttasks.length; i++) {
let rowNumber = rowStart + i;
//call function to set table values
setValuesTable(workbook, rowNumber, projecttasks, i);
//Merge project column rows
let getCurrentProjectValue = selectedSheet.getRange(`A${rowNumber}`).getValue();
let getPreviousProjectValue = selectedSheet.getRange(`A${rowNumber - 1}`).getValue();
if (getCurrentProjectValue == getPreviousProjectValue || (selectedSheet.getRange(`A${rowNumber - 1}`).getValueType() == ExcelScript.RangeValueType.empty == true && getCurrentProjectValue == mergeValuesColProject[mergeValuesColProject.length - 1])) {
if (i != 0) {
mergeValuesColProject.push(projecttasks[i].projectName);
if ((i == projecttasks.length - 1) && (mergeValuesColProject.length > 1)) {
selectedSheet.getRange(`A${rowStart + firstmergeposition}:A${rowStart + firstmergeposition + mergeValuesColProject.length - 1}`).merge(false);
selectedSheet.getRange(`A${rowStart + firstmergeposition}: A${rowStart + firstmergeposition + mergeValuesColProject.length - 1}`).getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
selectedSheet.getRange(`A${rowStart + firstmergeposition}: A${rowStart + firstmergeposition + mergeValuesColProject.length - 1}`).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
}
}
} else {
if (mergeValuesColProject.length > 0) {
if (i != 0) {
selectedSheet.getRange(`A${rowStart + firstmergeposition}:A${rowStart + firstmergeposition + mergeValuesColProject.length - 1}`).merge(false);
selectedSheet.getRange(`A${rowStart + firstmergeposition}: A${rowStart + firstmergeposition + mergeValuesColProject.length - 1}`).getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
selectedSheet.getRange(`A${rowStart + firstmergeposition}: A${rowStart + firstmergeposition + mergeValuesColProject.length - 1}`).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
mergeValuesColProject = [];
mergeValuesColProject.push(projecttasks[i].projectName);
firstmergeposition = i;
}
}
}
//check if is the last row
if (i == projecttasks.length - 1){
//call funtion to create table totals
setTableTotal(workbook, rowNumber+1, total);
}
//call function to set border of table cells
setBorderVisibility(workbook, rowNumber);
}
}
function setValuesTable(workbook: ExcelScript.Workbook, rowNo: number, projecttasks: projectInformation[], projectTaskIndex: number){
let selectedSheet = workbook.getActiveWorksheet();
const projectTaskRow = [[projecttasks[projectTaskIndex].projectName, projecttasks[projectTaskIndex].taskName, projecttasks[projectTaskIndex].personName, projecttasks[projectTaskIndex].hours]];
const tableRange = "A" + rowNo + ":D" + rowNo;
selectedSheet.getRange(tableRange).setValues(projectTaskRow);
}
function setTableTotal(workbook: ExcelScript.Workbook, rowNo: number, total: number) {
let selectedSheet = workbook.getActiveWorksheet();
const projectTaskRow = [["", "", "Total", total]];
const tableRange = "A" + rowNo + ":D" + rowNo;
selectedSheet.getRange(tableRange).setValues(projectTaskRow);
//Set fill color to FFF2CC for range on selectedSheet
selectedSheet.getRange("C" + rowNo+":D" + rowNo).getFormat().getFill().setColor("FFF2CC");
// Set horizontal alignment to ExcelScript.HorizontalAlignment.general for range on selectedSheet
selectedSheet.getRange("C" + rowNo + ":D" + rowNo).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.general);
selectedSheet.getRange("C" + rowNo + ":D" + rowNo).getFormat().setIndentLevel(0);
// Set vertical alignment to ExcelScript.VerticalAlignment.center for range on selectedSheet
selectedSheet.getRange("C" + rowNo + ":D" + rowNo).getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
selectedSheet.getRange("C" + rowNo + ":D" + rowNo).getFormat().setIndentLevel(0);
}
function setBorderVisibility(workbook: ExcelScript.Workbook, rowNo: number){
let selectedSheet = workbook.getActiveWorksheet();
// Set border for range on selectedSheet
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.continuous);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setColor("000000");
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setWeight(ExcelScript.BorderWeight.thin);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.continuous);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setColor("000000");
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setWeight(ExcelScript.BorderWeight.thin);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setColor("000000");
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setColor("000000");
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setWeight(ExcelScript.BorderWeight.thin);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setColor("000000");
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setWeight(ExcelScript.BorderWeight.thin);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setColor("000000");
selectedSheet.getRange("A" + rowNo + ":D" + rowNo).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setWeight(ExcelScript.BorderWeight.thin);
}
interface projectInformation {
projectName: string,
taskName: string,
personName: string,
hours: number
}
Comments