How do I have create a recurring flow that generates a copy of a template file?

[Spenser]

I have an Excel file on Sharepoint called Template 1.xls. in the folder Test.

/Test/Template1.xls

I want to create a flow with power automate that will recur every day at 7 am.

The flow should create a copy of Template1.xls into a subdirectory of the Test folder called Storage and name it using the following pattern: MMDDYYYY Daily.xls

If it were created today, we would see:
/Test/Storage/12202023.xls 

Within the file itself, the flow should change cell A1 into the following name: "Log", regardless of the existing data.

Within the file itself, the flow should change cell B2-C2 (it is one cell) into the current date, regardless of the existing data.

How do I create this flow?

[AlexEncodian]

@Spenser 

It sounds like your data is not in Excel tables. Therefore you will have to use Office Scripts to change values in specific cells.

You can copy files using SharePoint's Copy Files action:

However, to rename the file, you will need to use HTTP requests:

How to rename files in SharePoint with Power Automate (pnp.github.io)

A workaround would be to "Get File Content" on the newly updated copied file and then re-create it using "Cretae File" with the correct name.

Your flow would look something like this:

1) Recurring trigger
2) Copy template file
3) Excel's "Run Script"*
4) Get File content + Create new renamed file
5) Delete copied file

*I believe the script will be copied from the template. If not, you'll have to "Run script from SharePoint Library". Have a play with it.

[Spenser]

Thank you.  I’ll have to try it out, but I’m having some struggles with the copy part.  I only get three options when I copy - replace, fail, or overwrite.  Neither is correct - I just want it to name the file something else. 

[Spenser]

I was able to resolve the issue by just getting the file content with path and creating a new file.

Now I am struggling with editing the cell data.  Update a Row is giving me issues.

I have two images here to demonstrate the issue.
One image shows Table2.  It has a header called PM LOG.  It has one cell with the value of "DATE".

In Power Automate, I have the site and folder selected.
The file is a concatenization function because the function changes daily (the files are named daily based on the date and concatenized with a tail.)
The Table is always called Table2.
The Key Column is PM LOG.
The Key Value is DATE.

However, Automate is asking for properties.
It also doesn't give me an option to put in what I want to set the value to.

This is where I am confused and need more help.

[Spenser · Accepted solution]

I figured it out.
JSON was required because the data type is dynamic.
Using some snippets I found online I was able to craft the solution needed to resolve the issue.
Thank you for the help.

[ZizouG]

Hi @Spenser,

I'm new to the SharePoint Power Automate and I'm trying to create a flow, where I can copy files (xlsx and csv) to a subfolder every week, and add the current date to the file name. Can you please post the flow you did to get the file by the path?

Thank you