Manage more than 100 API calls with Batch Request in SharePoint Framework (SPFx) using React

Requirement:
We have IDs of 1000 list items and want to fetch list items for only those particular IDs using Rest API with OR condition in the filter.

Problem Statement:

1. If we append all IDs in the filter parameter with OR condition, it will give REST API length limitation (255 characters).
2. If we use API with OR condition for all IDs and use this API in a batch request, batch API will return a blank array.
3. If we create API for each ID and create an array of APIs & use this array in a batch request, it will work for maximum up to 100 APIs only. As we have 1000 list items to be retrieved, this will also not work due to the 100 APIs limit with a single batch request.

Solution:

We can create API for 100 Item IDs with OR condition in the filter API and create a bunch of 100 APIs in an array and then use this array in a batch request.

You can find batch utility code here on GitHub. Here is the code snippet for this solution:

 //Here we have imported batchutility.ts  
 import { BatchUtils } from "../../BatchUtils";  
 public componentDidMount() {  
   var listItemIds = [1, 2, 3, 4, 5, 6, ..............................., 1000];  
   var url = this.props.SPUrl + "/_api/web/lists/getbytitle('" + this.props.projectPhaseListName + "')/items?$filter=";  
   this.getDashboardBatchData(listItemIds, 0, listItemIds.length, url);  
 }  
 //Below function will create bunch of filters with max of 100 id in filter with or condition  
 public getDashboardBatchData(listItemIds, Index, totalCount, url) {  
   var loopLen = Index + 100;  
   if (Index <= totalCount) {  
     var filterString = "";  
     var tempApi = '';  
     var callNext = true;  
     if (totalCount > Index && totalCount < loopLen) {  
       loopLen = totalCount;  
       callNext = false;  
     }  
     for (var i = Index; i < loopLen; i++) {  
       if (filterString == '') {  
         filterString = "ID eq " + listItemIds[i];  
       }  
       else {  
         filterString += " or ID eq " + listItemIds[i];  
       }  
     }  
     tempApi = url + filterString;  
     dashboardBatchArray.push(tempApi);  
     if (callNext) {  
       this.getDashboardBatchData(listItemIds, loopLen, totalCount, url);  
     }  
     else {  
       //dashboardBatchArray will have all APIs with max of 100 filters for ID  
       this.processBatch(dashboardBatchArray);  
     }  
   }  
   else {  
     //dashboardBatchArray will have all APIs with max of 100 filters for ID  
     this.processBatch(dashboardBatchArray);  
   }  
 }  
 //Below funcion will create bunch of 100 APIs and will be used in batch request  
 public processBatch(dashboardBatchArray){  
   var index = 0;  
   var arrayLength = dashboardBatchArray.length;  
   var tempArray = [];  
   var chunk_size = 100;  
   //Below code will create array of 100 APIs in single bunch  
   for (index = 0; index < arrayLength; index += chunk_size) {  
     var myChunk = dashboardBatchArray.slice(index, index + chunk_size);  
     tempArray.push(myChunk);  
   }  
   //Below code will execute all apis of tempArray  
   let listItemsArray = [];  
   for (var i = 0; i < tempArray.length; i++) {  
     await BatchUtils.GetBatchAll({ rootUrl: this.props.SPUrl, FormDigestValue: '', batchUrls: tempArray[i] }).then((batchResult) => {  
       if (batchResult.length > 0) {  
         for (var i = 0; i < batchResult.length; i++) {  
           for (var j = 0; j < batchResult[i].d.results.length; j++) {  
             listItemsArray.push(batchResult[i].d.results[j]);  
           }  
         }  
       }  
     }  
   }  
   //You will see all 1000 items in listItemsArray variable  
   console.log(listItemsArray);  
 }  

If you have any questions you can reach out our SharePoint Consulting team here.

How to change Date and Time Format in Microsoft Flow or Power Automate?

Introduction:

In this blog, we will learn, how we can change the date and time format in Microsoft Power Automate. Sometimes, there are situations in which we need to change the format of the date and time value.

Let’s say for an example if our date time value is 2020-01-30T07:51:09Z and we wish to convert this value to “01/30/2020 07:51:06 AM” format, then we need to use some expression in MS Flow or Power Automate.

Real-Life Use Case:

In this article, we will format the date and time value to “MM/dd/yyyy hh:mm:ss AM/PM” format for SharePoint list item. We already have a SharePoint list with OOTB “Created” column. We will change the format of Date and Time in Power Automate. So, now let’s get started!

Step 1:

We have the following list structure. and would need to convert "Created" column to “MM/dd/yyyy hh:mm:ss AM/PM” format, and store this converted value in a variable.

Step 2:

Add a Trigger: When an item is created to the SharePoint list. Add Site Address and List Name.

Step 3:

Initialize variable, to store the value of the converted date and time. Add an action: Initialize variable.

Step 4:

Now, let’s set the variable and change the date and time format using below formula:

formatDateTime(triggerBody()?['Created'],'MM/dd/yyyy hh:mm:ss tt')

Here, we need to pass, the date time value as a first parameter and in the second parameter, we need to pass the format for the specific date and time value. 

Save the flow. We can check the formatted date and time value from this variable.

In the end, MS flow will look like in the following screenshot.

Now, let’s run the flow and check the result.

Test:

Let’s create one item in the SharePoint list to trigger a flow.

Here, our date and time format for the “Created” date is changed and stored in the variable successfully.

Conclusion:

This is how, we can easily change the format for the date and time column in Microsoft Power Automate. Isn’t it amazing?

If you have any questions you can reach out our SharePoint Consulting team here.

Office 365/SharePoint Online - PowerShell Script to get Unique Visitors & Total Views count for Site Page

Problem Statement -
We have a client with a large SharePoint Tenant, want to know the Total Views count along with Unique Visitors count for each Post/Page, they are adding every day for their employees.

Analysis -
Currently, Microsoft is working on Analytics API that is supposed to give you the details regarding total view count and unique view count for specific item. Even though it is released but not working for a single item.

The alternative way is to use the SharePoint Search API. This will use Microsoft Classic Search results and provide you count based on that.

Resolution -
We have decided to go with the SharePoint Search API approach. We have found an article with the same idea. The only concern was, using Search API directly can return max 500 rows as a result at a time. So, we need to generalize that script in such a way that it can be executed for all items in the list/library.

So, here is generalized the Power-Shell script:

Step 1 - Load required dependencies/assemblies:

 # Paths to SDK. Please verify location on your computer.  
 #Add-PSSnapin Microsoft.SharePoint.PowerShell  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll")  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Search.dll")  

Step 2 - Create a Function - to get all list items and using search query to retrieve ViewsLifeTime & ViewsLifeTimeUniqueUsers for each item:

 function Get-SPOListView  
 {  
   param(  
   [Parameter(Mandatory=$true,Position=1)]  
   [string]$Username,  
   [Parameter(Mandatory=$true,Position=2)]  
   $AdminPassword,  
   [Parameter(Mandatory=$true,Position=3)]  
   [string]$Url,  
   [Parameter(Mandatory=$true,Position=4)]  
   [string]$ListTitle  
   [Parameter(Mandatory=$true,Position=5)]  
   [string]$TenantUrl  
   )  
  #Get the SharePoint List/Library.
   $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)  
   $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $AdminPassword)  
   $ll=$ctx.Web.Lists.GetByTitle($ListTitle)  
   $ctx.load($ll)  
   $ctx.ExecuteQuery()  
  #Get all items from the List/Library.
   $qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()  
   $items = $ll.GetItems($qry)   
   $ctx.Load($items)  
   $ctx.ExecuteQuery()  
   foreach($listItem in $items)  
   {  
     Write-Host "ID - " $listItem["ID"] "Title - " $listItem["Title"] "EncodedAbsUrl - " $listItem["FileRef"]     
     $fileurl = $TenantUrl+ $listItem["FileRef"]  
     #Using Search API - Create the instance of KeywordQuery and set the properties.
     $keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($ctx)   
     #Sample Query - To get the result of last year.
     $queryText="Path:" + $fileurl  
     $keywordQuery.QueryText = $queryText  
     $keywordQuery.TrimDuplicates=$false  
     $keywordQuery.SelectProperties.Add("ViewsLifeTime")  
     $keywordQuery.SelectProperties.Add("ViewsLifeTimeUniqueUsers")  
     $keywordQuery.SortList.Add("ViewsLifeTime","Asc")   
     #Search API - Create the instance of SearchExecutor and get the result.
     $searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($ctx)  
     $results = $searchExecutor.ExecuteQuery($keywordQuery)  
     $ctx.ExecuteQuery()  
     #Result Count  
     Write-Host $results.Value[0].ResultRows.Count  
     #CSV file location, to store the result  
     $exportlocation = "C:\Pages_ViewsCount - Copy.csv"  
     foreach($result in $results.Value[0].ResultRows)  
     {  
       $outputline='"'+$result["Title"]+'"'+","+'"'+$result["Path"]+'"'+","+$result["ViewsLifeTime"]+","+$result["ViewsLifeTimeUniqueUsers"]   
       Add-Content $exportlocation $outputline   
     }   
     #}  
   }  
 }  

Step 3 - Call the above function:

 # Insert the credentials along with Admin & Tenant URLs and Call above Function.  
 #Enter Username here  
 $Username="username@sharepoint.com"  
 #Enter Password Here  
 $AdminPassword=Read-Host -Prompt "Password" -AsSecureString  
 #URL of the site collection  
 $AdminUrl= "Site collection URL"  
 $ListTitle= "Site Pages"  
 $TenantUrl= "Your tenant URL E.g. https://YourCompany.sharepoint.com"  
 Get-SPOListView -Username $Username -AdminPassword $AdminPassword -Url $AdminUrl -ListTitle $ListTitle -TenantUrl $TenantUrl  

It will export all items of the list with Total views count - ViewsLifeTime & Unique views count - ViewsLifeTimeUniqueUsers in excel sheet as shown in below image:

Conclusion - This way, we can retrieve the item level unique visitors count & total view count.

If you have any questions you can reach out to our SharePoint Consulting team here.