cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CRUSH
Level: Powered On

Excel online create new spreadsheet when a shift change happens

Hi All!

 

Bit of a back story first. I have a Sharepoint custom list that is populated from a third-party E-From provider. I then used a flow for "when an item is created in Sharepoint then add a row into a table in Excel Online (Business)". Works great, I then created a whole bunch of custom lists for specific customers we do business with and made those flows as well. It is all working great. But I want to push a lot further. 

I want to break the customers to dates and times on separate spreadsheets in the workbook. For example if our shift change is at 6AM to 6PM I need all the tickets from that customer, for that day, during that time period to be on its own spreadsheet, and then if its same date but 6PM to 6AM then it would be on a different Spreadsheet. Then so on and so forth for all days after that. 

 

Thanks in advance for thoughts and knowledge you may have!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Excel online create new spreadsheet when a shift change happens

Hi @CRUSH ,

 

Thanks for giving more clarification.

 

It seems that you would like to create spreadsheets and tables dynamically using Flow.

 

Flow does have actions to Create worksheet and Create table, however, they are not able to be used in your scenario, because dynamic contents for File and Table fields in Excel Online connector are not supported yet, while your scenario needs to get sheet or table created by previous actions.

 

There is a similar request at Flow Ideas Forum, please vote the idea at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Allow-Dynamic-Content-in-Excel-File-fields/idi-p/1885...

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Excel online create new spreadsheet when a shift change happens

Hi @CRUSH ,

 

Do you want to save items created between 6AM to 6PM into one spreadsheet, and items created between 6PM-6AM to another spreadsheet?

I have made the following test for your reference.

It is triggered by When an item is created.

Add a Compose action to convert the Created date time from UTC to local time zone.

convertTimeZone(triggerBody()?['Created'],'UTC', 'Pacific Standard Time')

Add a Condition with the following configuration:

 

greaterOrEquals(formatDateTime(outputs('Compose'),'HH:mm'),'06:00') is equal to True

AND

lessOrEquals(formatDateTime(outputs('Compose'),'HH:mm'),'18:00') is equal to True

 

Under if yes branch, add row into table 6AM-6PM (here it is Table 6).

Under if no branch, add row into table 6PM-6AM (Here it is Table 7).

Image for your reference.

1.PNG

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
CRUSH
Level: Powered On

Re: Excel online create new spreadsheet when a shift change happens

Hello!

 

Sorry for the lack of reply. I have been traveling to our other branches getting everything squared away there. Thank you for replying! To answer your question... Sort of. I need them broken into the two shifts, this will need to be on a day to day basis. So I need two spreadsheets per day repeating the flow every day afterwards. So it would need to have the flow create a new spread sheet and table per shift per day. So for example today 3/11/19 would have 2 spread sheets, tomorrow 3/12 would have 2 spreadsheets. But all in one workbook. I really hope I am explaining it well... It's Monday have had a hard time explaining things today lol

Community Support Team
Community Support Team

Re: Excel online create new spreadsheet when a shift change happens

Hi @CRUSH ,

 

Thanks for giving more clarification.

 

It seems that you would like to create spreadsheets and tables dynamically using Flow.

 

Flow does have actions to Create worksheet and Create table, however, they are not able to be used in your scenario, because dynamic contents for File and Table fields in Excel Online connector are not supported yet, while your scenario needs to get sheet or table created by previous actions.

 

There is a similar request at Flow Ideas Forum, please vote the idea at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Allow-Dynamic-Content-in-Excel-File-fields/idi-p/1885...

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,675)