Thursday, September 06, 2018

Microsoft Flow - Copy Folders with documents between Sites using REST

New day, new possible business case for Microsoft Flow... for this example how to copy folder with files between sites using Microsoft Flow Action.

Following small Business Case:

  • Provisioning of Sites and Subsites base in request (example article).
  • Sub Site needs to have Folder with Document Templates that needs to be filled by users.
  • Template folder is in root Folder and needs to be copy into Sub Site.
  • Create Flow Action base in REST to copy folder template to new Sub Site when site is created.

For this Flow i use the Send an HTTP request to SharePoint and use the following REST Service call "SP.MoveCopyUtil.CopyFolder
", this method has some time (since 2016) and is very used in JSOM and here a simple article explaining how to use it:

For this example i have a Modern Team Site where are stored the Template folder files that should be available in the provisioned Sub Sites, as shown in the image below.


After definition of Root Folder where are stored the folder/Files to be copy, you can access to Microsoft Flows and create a new Flow and add the following Flow Action "Send an HTTP request to SharePoint" there you can include the HTTP call with the following source url to destination Folder to be copied.

HTTP Method:

Site URL: {Site}

Method: POST
URI : _api/SP.MoveCopyUtil.CopyFolder
Body:
"srcUrl": "https://{tenant}.sharepoint.com/sites/SaveMe/Shared Documents/Templates", "destUrl": "https://{tenant}.sharepoint.com/sites/SaveMe/111/Shared Documents/Templates" 
}





The Flow definition should be enough to be executed.
You can manually execute the Flow and validate their success of the Action. 
You can use the flow history for more details, as show in the image below.


Looks like the Flow output is ok, then you access to your Sub Site Document Library and very if Folder "Templates" appears as shown in the image below.

Three articles in a week should be a record to me, looks like my small burn out is slowing disappearing...

Hope you like it,

Best Regards,
André Lage

Tuesday, September 04, 2018

Microsoft Flow - Excel Actions To Copy Table Rows by Recurrence Timeline

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:

  • 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 byquery 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


Thursday, August 30, 2018

Microsoft Flow - Provision SharePoint Online Modern Sites using REST

Today i want to provide an article regarding MS Flow, first article and no clue for the next. :P

I was exchanging some questions with Doctor Flow (@sergeluca) in Twitter regarding MS Flow provision and Office 365 Groups and he provide me some great insight and help and very great step by step implementation with Azure, PnP framework and MS Flow.
Here is the article:
MICROSOFT FLOW PROVISIONING OFFICE 365 GROUPS, SHAREPOINT SITES, APPLYING PNP TEMPLATES AND WAITING FOR COMPLETION [AZURE FUNCTION, FLOW, AZURE QUEUE]
https://sergeluca.wordpress.com/2017/08/01/microsoft-flow-provisioning-sharepoint-sites-applying-pnp-templates-and-waiting-for-completion-azure-function-flow-azure-queue/
PS: If you want to learn about Office 365 Low code (MS Flow, Powerapps, PowerBI) you should definitely follow him and read his material.

Base in the provided example, i wanted to make more investigation and understand where can we really go with MS Flow, HTTP REST and Provisioning, base in the MS Flow.
PS: PnP provisioning framework is very complete and definitely should be used for provisioning and recommend by Microsoft.

Following simple Business case:
( Of course can be more complex, but here a simple PoC)

  1. Create a Powerapps Form with SharePoint List behind to save the Request.
  2. SharePoint List stores the entries and start MS Flow process of provisioning Modern Team Sites with Office 365 Groups.
  3. The MS Flow process starts and updates the SharePoint Items associated and make SharePoint HTTP request for the provisioning of the Modern Team Site (Office 365 Group)
  4. The Modern Team Site is available and associated Group base in the alias and available in Office 365.


Full Steps in Images

1. Team with Powerapp form Tab to Request Projects



2. SharePoint List where are stored data from the Form
3. MS Flow where the provisioning of the Site are made.

4. After provision of the Modern Team Site, then become avaliable.

--------------------------------------------------------------------------------------------------------------------------

For this implementation made some investigation regarding HTTP REST about provision and there were 2 type of provision i was looking about "Modern Communication Site" and "Modern Team Sites".
Here are my outputs:

1. Provision SharePoint Online Modern Communication Site 




When i was making my research, my first thought was to look in the Microsoft Documentation and found the following article that i use in my MS Flow Action and Metadata associated.

Create SharePoint Communication site using REST
https://docs.microsoft.com/en-us/sharepoint/dev/apis/communication-site-creation-rest 

Method: POST
Uri: "_api/sitepages/communicationsite/create"
Metadata:
{
  "request": {
    "__metadata": {
      "type": "SP.Publishing.CommunicationSiteCreationRequest"
    },
    "Title": "Comm Site 1",
    "Url": "https://{tenant}.sharepoint.com/sites/comm1",
    "Description": "",
      "Classification":"LBI",
      "SiteDesignId":"{Site design ID}",
    "AllowFileSharingForGuestUsers": false,
    "lcid":1033
  }

}

Implementation of the MS Flow Action "HTTP request to SharePoint" for the provisioning of the Communication Site.


This made the job.
PS:(take in consideration that some scenarios you will need to use Graph to get the Access Token by Graph and associated in the Headers)
"Get access tokens to call Microsoft Graph"
https://developer.microsoft.com/en-us/graph/docs/concepts/auth_overview

An older method was used for the provisioning "publishingsite" REST.
Important: "/_api/sitepages/publishingsite/create" with "type":"SP.Publishing.PublishingSiteCreationRequest" was the old way and most problaby not supported, should be used the following method;
Official: Create SharePoint Communication site using REST


2. Provision SharePoint Online Modern Team Sites (Office 365 Group)



As last Method, my first thought was also to look in the Microsoft Documentation but didn't found articles about, then use Search engines and found the following http Request "_api/GroupSiteManager/CreateGroupEx" that was used in my MS Flow Action and Metadata associated.

Create SharePoint Modern Team (Office 365 Group) site using REST

Method: POST
Uri: "_api/GroupSiteManager/CreateGroupEx"
Metadata:
{
"displayName": "test modernteamsite",
"alias": "testmodernteamsite",
"isPublic": true,
"optionalParams": {
"Description": "Test",
"CreationOptions": {
"results": []
}
}
}

Implementation of the MS Flow Action "HTTP request to SharePoint" for the provisioning of the Modern Team Site.
This made the job for the provisioning of Modern Team Sites. After accessing Team and join the Group the new Team Modern Site was available.




Provision Sub Site SharePoint Online Modern Team Sites





Provision of Sub Sites is more easy and more well documented "used since SP 2013" Microsoft has a very detail article regarding different SharePoint REST call that can be used for this purpose, Creating a sub Site with REST.

Method: POST
URI: "_api/web/webinfos/add"
Metadata: {
'parameters': {
'__metadata': {
'type': 'SP.WebInfoCreationInformation'
},
'Url': 'RestSubWeb',
'Title': 'RestSubWeb',
'Description': 'REST created web',
'Language': 1033,
'WebTemplate': 'sts',
'UseUniquePermissions': false
}

}

Implementation of the MS Flow Action "HTTP request to SharePoint" for the provisioning of Sub Site with Modern Team Site.

This 3 REST call can help you with the provisioning using MS Flow, but it's important to take in consideration other point such us permissions, governance and a good life cycle of all your process, Training is critical and always validate, test and validate, over and over and over.... 

Research Documentation:
PnP Sites Core
https://github.com/SharePoint/PnP-Sites-Core 
Create SharePoint Communication site using REST
https://docs.microsoft.com/en-us/sharepoint/dev/apis/communication-site-creation-rest 
Microsoft Flow Provisioning Office 365 Groups, SharePoint sites, applying PnP templates, queuing...
https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Microsoft-Flow-Provisioning-Office-365-Groups-SharePoint-sites/ba-p/61126
MICROSOFT FLOW PROVISIONING OFFICE 365 GROUPS, SHAREPOINT SITES, APPLYING PNP TEMPLATES AND WAITING FOR COMPLETION [AZURE FUNCTION, FLOW, AZURE QUEUE]
https://sergeluca.wordpress.com/2017/08/01/microsoft-flow-provisioning-sharepoint-sites-applying-pnp-templates-and-waiting-for-completion-azure-function-flow-azure-queue/
"Get access tokens to call Microsoft Graph"
https://developer.microsoft.com/en-us/graph/docs/concepts/auth_overview

Hope you like it, 

Best Regards, 
André Lage

Monday, August 20, 2018

Struggling REST query "substringof" with special Characters (umlaut and not latin characters)

Did you have struggle when you create SharePoint REST query's with substringof that didn't return special Characters (umlaut, not latin characters)?
If yes, then i understand your stress and here a simple that could help you in your JS development that return the expected text.

Business Case: Have a list of contacts with usernames that have special characters.
Issue: Create custom Search using SharePoint REST to return usernames, but if your have users with characters "ü, ö, ä" doesn't return any user.

Example:
var restUrl = "/_api/web/Lists/getbytitle('kontakt')/items?$select=FullName,Email&$filter=substringof( '" + term + "',FullName)";

One way to resolve:

Include in the JS a new String function to deal with special characters: 

String.prototype.toUnicode = function(){
var result = "";
for(var i = 0; i < this.length; i++){
result += "%u" + ("00" + this[i].charCodeAt(0).toString(16)).substr(-4);
}
return result;
};

When you make the REST Query, here a simple way to make this search that return special characters

var restUrl = "/_api/web/Lists/getbytitle('kontakt')/items?$select=FullName,Email&$filter=substringof( '" + term.toUnicode() + "',FullName)";


Simple article to help with the little things that take you hours of your life....

Best regards, 
André Lage