Create Excel Workbook in SharePoint from Power Automate Flow

[ArchitectMadhan]

In this Article, I have explained the business scenario of Creating Excel Workbook in a Folder that exists in SharePoint Document Library, also dynamically the folder is created, before storing this Excel Workbook.

Here is the working solution below, you need to follow all the below steps.

Explanation for each step:

Step: 1

In order to create the Excel Workbook dynamically with content, you need to have an Excel Workbook (as template, without any values) as shown below must be stored in SharePoint Document Library already.

Step: 2

As we are going to create the Excel file and Folder dynamically, we must use few variables like varFilename, varNewFolderPath, and varFilenameWithExtn to store the Excel Filename and new Folder’s path etc.,

To extract the filename, you need to use the formula.

substring(variables('varFilenameWithExtn'),0,lastIndexOf(variables('varFilenameWithExtn'),'.'))

Step: 3

Before preparing the excel workbook, we need to create the new folder in which the excel file must be created.

Step: 4

In order to write values to it we can use the action “Add rows to excel table”. In this action we need to map the excel workbook (template, in this example it is TaskBook.xlsx) from the SharePoint Document Library as shown below.

After saving the content to the excel file, get the file content and use it in the “Create file” action.

Step: 5

Once we got the file content, next thing is to create a new excel workbook in the newly created folder.

Output:

Before uploading the file to folder “ToBeApproved” there is no folder created in the name of the file that we are going to drop in “ToBeApproved”.

Now, we have uploaded the file (TestWorkflowSample.txt) to the folder "ToBeApproved".

Here is the folder created in the name of the file uploaded.

The Excel Workbook created in the new folder.