cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CRUSH
Frequent Visitor

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

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
v-yamao-msft
Community Support
Community Support

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.

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

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,120)