cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jared_Simon
Helper I
Helper I

excel adding sheet with referencing

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

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

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.

 

DamoBird365_0-1618934155909.png

 

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

View solution in original post

6 REPLIES 6
DamoBird365
Super User
Super User

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:
DamoBird365_0-1618932502956.png

 

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
Helper I
Helper I

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
Super User
Super User

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.

 

DamoBird365_0-1618934155909.png

 

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
Helper I
Helper I

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
Super User
Super User

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

 

Damien

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(00).setFormula("=Hourly_Sales!A1");
 
This sets the formula "=Hourly_Sales!A1" into the top left cell in the worksheet "Sales_Data" using Office Scripts.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (2,662)