excel adding sheet with referencing

[Jared_Simon]

I have an excel workbook file that comes every one hour from a sales reporting system to my email. is it possible to add a new sheet named Sales_Data which will capture data from the "Hourly_Sales" tab and perform some calculations and then save it to a sharepoint folder.

the "Sales_Data" will have rows and columns which will be referencing to "Hourly_Sales" sheet

for eg:  =Hourly_Sales!A1 & =Hourly_Sales!B1/24 etc.... 

is there a way to programmatically add this new sheet using power automate ?

Thanks

[DamoBird365]

Hi @Jared_Simon 

Take a look at the Excel Scripts feature. It allows you to write bespoke scripts in Java/Typescript and can be called direct from the Excel Script Action (in Preview - 200 daily limit).

But you could create a basic script to create a new worksheet as follows:

function main(workbook: ExcelScript.Workbook) {

  // Add a new worksheet with name Sales_Data

  let sales_Data = workbook.addWorksheet("Sales_Data");

}

An intro on Excel Scripts is here 

I also did a demo here where I populate an excel template with data using Excel Scripts.

EXAMPLE CODE BELOW:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Jared_Simon]

Hello @DamoBird365  Thanks so much for your time.

the incoming files doesn't contain any script. What I would like to do is when the email comes in, the powerautomate workflow will run the code to create this new sheet with the reference formulas like I said in my OP and then save it to a SPO library. - is that possible? sorry, I am not much versed into powerautomate.

[DamoBird365 · Accepted solution]

Hi @Jared_Simon 

That's exactly what I am proposing, otherwise you cannot create a worksheet with Excel actions.  You can combine an excel script with Power Automate and the Excel Script can even be recorded if you have no experience of Java/TypeScript.

Here is the Excel Script Action in Power Automate.

Jump onto Excel (online client only) and record your script, save it a name and call it from the above action on your newly received file.  Excel Scripts are cloud based and don't need to be saved to an excel file to run, they are actually saved in OneDrive.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Jared_Simon]

Thank you @DamoBird365  I appreciate your help. I will mark this as a solution. A simple test seems to work as you explained. I will go in deep with this and see how far I can go. Thanks so much.

[DamoBird365]

Sounds like an interesting project, good luck and let me know how you get on.

Damien

[GeoffRen]

Try looking at the Range.setFormula() function. It'll set a formula in a cell in your worksheet. For example:

workbook.getWorksheet("Sales_Data").getCell(0, 0).setFormula("=Hourly_Sales!A1");

This sets the formula "=Hourly_Sales!A1" into the top left cell in the worksheet "Sales_Data" using Office Scripts.