After my last Article "Microsoft Flow - Provision SharePoint Online Modern Sites using REST" continue to explore Microsoft Flow and their different options, today made investigations regarding the Excel Flow Actions and SharePoint Online with the following Business Case:
Microsoft Flow - Excel Actions identified boundaries:
SharePoint Online - Excel location
This example try to use all Office 365 services and capabilities and SharePoint Online Document Library was used to store the excel demo as show in the image below.
List Excel Table View - Table 1
After access the excel example we are able to see the entries from Table 1 where are listed multiples entries for this demo to be used in the copy between tables.
Microsoft Flow
Recurrence - Action
Access to Microsoft Flow Page and create New Flow using Blank Template.
To fulfill the job timer was used a recurrence Action where was defined interval of 1 time by month to be executed as show in the image below.
List of Action "Preview Excel"
After Recurrence is defined a new Action was defined and list the Excel options, for this example "Excel Online (Business)" to use SharePoint Online Location where is stored the Excel file.
List rows present in a table - Action
This action lists the Table 1 items base in a query from the Excel File to return the selected items to be used to copy in the Table 2.
Property Options:
Location - SharePoint Online Site
Document Library - Document Library from selected SharePoint Online.
File - Excel File Location from Document Library
Table - Table from Excel used as Datasource
Filter Query - query option to select which items to return.
Order by - query option to specify how to sort the items in your query return set
Top Count - query option to specify how much items should return from the return set
Skip Count - option enables you to skip over items until the specified item is reached and return the rest.
For this example were defined 10 items to make the example associated as show in the image below.
Add an apply to each - Step
This step manage the result item collection from last Action where Query from Table 1 was made to be used and copied in Table 2.
Add a Row into Table - Action
As defined in my last Action a new parameter with Item Collection rows from my last query are stored in the parameter "value".
To add new Row in new
Location - SharePoint Online Site
Document Library - Document Library from selected SharePoint Online.
File - Excel File Location from Document Library.
Table - Table from Excel used as destination Datasource.
[Columns Name] - Column Name from Excel Table to include new Items.
To call the item Collection row in the new Row/Column, i would like to recommend the following expression:
Below the example on how to get Item details.
List Excel Table View - Copy output to Table 2
After execution of the Microsoft Flow and validation of execution, users can access to Excel Document and access to support "Table 2", for this case were stored in a separated worksheet and the values should appear as shown in the image bellow.
Here the full Flow:
- Have Excel document where are stored entries from users.
- Excel Document is stored in SharePoint Online Document Library.
- Every 30 days should archive in different Excel Table the entries from that Month
- Copy from Table 1 to Table 2 (Archive)
Microsoft Flow - Excel Actions identified boundaries:
- Get rows method can be up to 2048 items.
- The action can list up to 256 rows
SharePoint Online - Excel location
This example try to use all Office 365 services and capabilities and SharePoint Online Document Library was used to store the excel demo as show in the image below.
List Excel Table View - Table 1
After access the excel example we are able to see the entries from Table 1 where are listed multiples entries for this demo to be used in the copy between tables.
Microsoft Flow
Recurrence - Action
Access to Microsoft Flow Page and create New Flow using Blank Template.
To fulfill the job timer was used a recurrence Action where was defined interval of 1 time by month to be executed as show in the image below.
List of Action "Preview Excel"
After Recurrence is defined a new Action was defined and list the Excel options, for this example "Excel Online (Business)" to use SharePoint Online Location where is stored the Excel file.
List rows present in a table - Action
This action lists the Table 1 items base in a query from the Excel File to return the selected items to be used to copy in the Table 2.
Property Options:
Location - SharePoint Online Site
Document Library - Document Library from selected SharePoint Online.
File - Excel File Location from Document Library
Table - Table from Excel used as Datasource
Filter Query - query option to select which items to return.
Order by - query option to specify how to sort the items in your query return set
Top Count - query option to specify how much items should return from the return set
Skip Count - option enables you to skip over items until the specified item is reached and return the rest.
For this example were defined 10 items to make the example associated as show in the image below.
Add an apply to each - Step
This step manage the result item collection from last Action where Query from Table 1 was made to be used and copied in Table 2.
Add a Row into Table - Action
As defined in my last Action a new parameter with Item Collection rows from my last query are stored in the parameter "value".
To add new Row in new
Location - SharePoint Online Site
Document Library - Document Library from selected SharePoint Online.
File - Excel File Location from Document Library.
Table - Table from Excel used as destination Datasource.
[Columns Name] - Column Name from Excel Table to include new Items.
To call the item Collection row in the new Row/Column, i would like to recommend the following expression:
- item()['Column Name']
Below the example on how to get Item details.
Execute Flow by request - Run now
After creation of Flow and for testing purpose we can manually execute the Flow using the "More" option and select "Run now" and wait to finalize their execution.
Microsoft Flow - Report
After execution of Flow and their success, we can see the reports about the returned Rows and HTTP communication about the copied items between Excel Tables, "were defined 10 rows for this example".
List Excel Table View - Copy output to Table 2
After execution of the Microsoft Flow and validation of execution, users can access to Excel Document and access to support "Table 2", for this case were stored in a separated worksheet and the values should appear as shown in the image bellow.
and another example made using Excel and Microsoft Flows.
Office 365 getting better every day...
Here the full Flow:
Hope you like it,
Best Regards,
André Lage
Best Regards,
André Lage
No comments:
Post a Comment