December 30, 2019

How to build a Custom People Picker in Power Apps

In Power Apps - Canvas App, we came across the specific requirement to have a custom people picker where user can search employees by its name and associated email-id should be displayed in the drop-down.

We know, Power Apps doesn’t provide any OOTB people picker without Form control. That’s why we need to build a custom people picker in our App. So, let start creating the custom people picker in Power Apps.


A. Create Custom People Picker:
Please follow the below steps to create our own custom people picker with the suggestion box.

  1. Add Data Source. To add the data source, Go to the View menu and then select Data Sources option.

  2. Click on “Office 365 Users” for adding the Data Source.
                         

  3.  Now, add  List Box” and “Text Input” Control.
    • To add List Box click on “Insert” then on “Controls” -> “ListBox

    • To add Text Box click on “Insert” then in “Text” select “Text Input”.
  4. Select the Textbox control which we have added in Step 3 and configure the following properties.
    • Text Box
      • Default: SelectedUserEMail
      • OnSelect: Set(UsersListBox,true)
    • List Box
      • Items: If(!IsBlank(TextInput2.Text), Office365Users.SearchUser({searchTerm:TextInput2}).Mail)
      • Visible: UsersListBox
      • OnSelect: Set(SelectedUserEmail, ListBox1.SelectedText.Value); Set(UsersListBox,false);Reset(TextInput2)

Now, let’s test our custom people picker!
  • Search for the Employee name in textbox of Custom people picker.
  • It will show the email-id property of that user in the suggestion box. Currently, we have displayed only “Email Property” in the combo box. However, we can show the other Office 365 properties as well.

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

October 11, 2019

FileShare migration with updated metadata to SharePoint

We have a requirement to migrate the large number of documents from File Share System to SharePoint with metadata. Normally, we can easily migrate the large number of documents from File System to SharePoint, but the challenge is to update the large amount of metadata in SharePoint List/Library in quick time.

Resolution:

To change the metadata manually one by one is a very time-consuming process. And it is very complicated to update the large amount of metadata using OOTB “Quick Edit” feature of SharePoint.

To overcome this challenge, we have used ShareGate, third-party tool. 

We can migrate the files from File Share to SharePoint with updated metadata in very quick time using below steps.
  • Browse the ShareGate application, then select the “Import file shares” option as shown in the below screenshot. 

  • Connect the Source folder - File share path and Destination - SharePoint Library where documents need to be migrated.
  • Select All or Particular Documents for which metadata needs to be updated.
  • Click on “Export metadata”.

  • Update the Configuration as required and click on “Export”.

  • Click on “Open file” to update metadata in the excel file.
  • For multiple managed metadata column, add the comma-separated values. 
  • For Person or Group column, add the values like “Firstname Lastname” as shown below.

  • Once the metadata are updated, click on “Import & Edit” to import the documents with updated metadata.

  • You can also import the updated metadata from "Copy" section as shown below.

  • Files with updated metadata will be imported in SharePoint document library.

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

October 1, 2019

How to get Organization Asset Libraries in SharePoint Online using REST API

Organization Assets Library is a new feature introduced in Office 365 which is a tenant level object. Currently, Organization Assets Library can be created only through PowerShell commands.

I was developing custom SPFx web part to display a list of Organization Assets Libraries. As it was a custom SPFx web part, I was looking for approach like REST API calls to fetch the Organization Assets Libraries instead of CSOM. After a deep analysis, I came to know that we can use REST API call to get the Organization Assets Library with their properties.

Solution:
Use below SharePoint REST API to get the Organization Assets Libraries for a tenant:

https://<Tenant.sharepoint.com>/_api/Microsoft.Online.SharePoint.TenantManagement.Office365Tenant/GetOrgAssets

This API will return the collection of Organization Assets Objects. It will return below properties for organization asset objects as shown in below screenshot.
REST API call result schema

Here are few points about Organization Assets libraries and REST API those need to be noted:
  • This API is retrieving tenant-level objects (Microsoft.Online.SharePoint.TenantManagement.Office365Tenant). So, it will return results only for users who are assigned with "SharePoint Administrator" role in the Office 365 tenant. 
  • All the Organization Assets objects must be created in a single site collection only.
  • Once the object/library is created, it will take 24 hours to get the Organization Asset objects using the API . Here is the more details how this API works:
    • There is an Azure function, which gets triggered by a timer (once a day).
    • The function gets SharePoint metadata and extracts all valuable information into JSON object.
    • The function stores JSON in Azure blob.
    • SharePoint REST API Metadata uses JSON, saved by Azure function to show you the metadata info.
If you have any questions you can reach out our SharePoint Consulting team here.

September 20, 2019

Performance issues with SPFx web parts [Solution: Fix it with implementing renderCompleted & isRenderAsync methods]

We mostly encounter performance related issues, like - Page takes very long time to load all features while working with highly customized SharePoint site. To identify performance issue of Custom SPFx web parts, Microsoft suggests to implement RenderCompleted method.

I searched over the internet but did not find any good documentation on how to implement it. After spending time on analysis & research, finally I was able to implement it successfully.

Here, I am providing you with detailed steps to implement renderCompleted & isRenderAsync methods to identify performance of custom webparts.

Solution - To implement such methods, open custom web part source code - solution in Visual studio.
  • Open Interface file and add below property to it.
    asynccompfunc:() => void;
  • Now, go to the ".ts" file of your custom webpart and add below highlighted code in "Render" Method.
          public render(): void
            {
               const element: React.ReactElement<ISubmitNewsProps> = React.createElement(                                 SubmitNews,
                 {
                    // other properties...,
                    asynccompfunc:this.renderCompleted
                  });

                ReactDom.render(element, this.domElement);   
            }
  • Also, add below code after "Render" Method.
           protected renderCompleted(): void { 
                super.renderCompleted();   
           }

           protected get isRenderAsync(): boolean { 
               return true;   
           }


  • Now open ".tsx" file and add below code in "componentDidMount" method.
         //Init Function of component mount 
           public async componentDidMount() {
           
            //your code
           
           this.props.asynccompfunc();
         }


After applying all changes in your web part, deploy package and open the page where you have added the webpart.

Click "Ctr + F12". to see the performance of your web part.


Note: This method requires "BaseClientSideWebPart" class to be extended. Application Customizer does not extend this class. So we cannot implement this method in Application Customizer.

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

September 12, 2019

Get Members of SharePoint Groups in Excel using Power Query

Challenge:
We have several business units. And in that, for each business unit, we have Approvers, Owners, Members and Viewers. The challenge is to get the members of all business units'  "Approvers" SharePoint group in Excel.

Resolution: 
In the previous blog, we have explained about Power Query in Excel and connecting the SharePoint Lists from SharePoint Online.

Now, using Power Query function we would be getting the list members of SharePoint groups. For this, we would be first creating a function which would get the members of any SharePoint group.

  • For this, we would go to Data > Get Data > From Other Source(s) > From OData Feed. Then, as per the requirement, we need members of the Approvers groups for all business units. So, in this query, we would be fetching the names of the SharePoint groups having "Approvers" in their names and renaming this as "SPGroups".
https://<domain>/_api/web/SiteGroups?$filter=substringof('Approvers',Title)&$select=Title



  • We have the list of SharePoint groups, and now, we would be fetching the members of each SharePoint group, by creating a Power Query function. For this, we would be adding a new blank query and rename it as "GetMemberByGroupName". We know the REST call to get group members is as below:
https://<SiteURL>/_api/web/sitegroups/getbyname('<GroupName>')/users

  • So, we would add a parameter "GrpName" as text, which would make a REST Call for each group, and get all the members. The function (in the Advanced Editor) would be like below:
(GrpName as text) as table =>         
let
    Source= OData.Feed("https://<domain>/_api/web/sitegroups/getbyname('"&Text.From(GrpName)&"')/users?$select=Title")
in
    Source

  • Now, we would go back to SPGroups (1st query) and in the ribbon Add Column > Invoke Custom function. We would select the function and the column name as a parameter and expand the table we get in Custom column.

  • So, here we have the members as per the SharePoint group names.
  • Expand "GetMemberByGroupName" column.
  • So, members for the SharePoint group will be extracted here!

This way, we can have all the members of the selected SharePoint groups in the Excel file using Power Query.

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

August 29, 2019

Create file on SFTP server using Web Service and Rest API from SharePoint Online

Sometimes, we need to deal with third party tools/applications from SharePoint Online. To deal with third party tools/applications, we may need a medium like SFTP server to pass data from SharePoint Online to third party tools/applications. In this blog, we are going to showcase - how to create xml/text file on SFTP server from SharePoint Online.

First, let's understand what's SFTP server and how it can be used to transfer data from SharePoint Online.

What is SFTP server?

SFTP - Secure File Transfer Protocol server, is popular method of transferring files between two remote systems over a secure connection. In this blog, we will see how to transfer data from one remote server (SharePoint Online) to another remote server (Workday).

Create file on SFTP server using Rest API & Web Service in SharePoint Online:

We can follow below steps to create file on SFTP server:

Step 1: Create a page in SharePoint Online and add custom button using HTML and JavaScript file. On Custom button click event, JavaScript file would call web service to create file on SFTP server.

Step 2: Create a web service (c#) using Visual studio.
  • Install “SSH.NET” package to deal with the SFTP server.
  • Find below code to connect SFTP server.

var client = new SftpClient("Server URL", "User Name", "Password");
client.Connect();
  • Then, stores incoming data into memory.

byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
var memory = new MemoryStream(bArray);
    • Now, Upload/Create a file on SFTP server.

    client.UploadFile(memory, "/Folder path /File Name", null);
    • At the end, close the connection with SFTP server.

    client.Disconnect();
    client.Dispose();
      • The complete code for SFTP server integration is as below:

      [WebMethod]
      [ScriptMethod(ResponseFormat = ResponseFormat.Json)]   
      public string UploadFileToFTP(string data)
      {
           var client = new SftpClient("Server URL", "User Name", "Password");
           client.Connect();
           byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
           var memory = new MemoryStream(bArray);
           client.UploadFile(memory, "/Folder path /File Name", null);
           client.Disconnect();
           client.Dispose();
      }
      

      Step 3: Publish this web service on Microsoft Azure platform.

      Step 4: Add following code to JavaScript reference file in SharePoint Online. In this step, we would get data from SharePoint Online list and pass data to web service to create file on SFTP server.

      function MoveFileToSFTP()
      {
           var DataURL = "'ServerURL'/FTPFileUpload.asmx/UploadFileToFTP";
           var request = new XMLHttpRequest();
           var Data = JSON.stringify(data);
           var params = "data="+Data+"";
           request.open('POST', DataURL, true);
           request.onreadystatechange = function() {
              var responsetext = request.responseText;
       if (request.readyState==4)
       {
          alert("File has been created successfully on SFTP server.");
       }
           };
           request.setRequestHeader("Content-type", "application/x-www-  form-urlencoded");
           request.setRequestHeader("Content-length", params.length);
           request.setRequestHeader("Connection", "close");
           request.send(params);
      }
      
      This way, we can upload files to SFTP from SharePoint Online programmatically.

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

      Resolution: Open term is not allowed in PnP Taxonomy Picker control

      Requirement:
      We need to create a custom modern form which includes taxonomy picker control allowing Open Term. User should be allowed to add fill-in values - inserting new term on the fly.

      Approach:
      To meet above requirements, i've used PnP Taxonomy Picker control for SPFx.
      https://sharepoint.github.io/sp-dev-fx-controls-react/controls/TaxonomyPicker/

      Steps to add in Solution:
      Import modules as below:

      • import { TaxonomyPicker, IPickerTerms } from "@pnp/spfx-controls-react/lib/TaxonomyPicker";

      Use taxonomy picker control as below:
      <TaxonomyPicker allowMultipleSelections={true}
                      termsetNameOrID="Countries"
                      panelTitle="Select Term"
                      label="Taxonomy Picker"
                      context={this.props.context}
                      onChange={this.onTaxPickerChange}
                      isTermSetSelectable={false} />

      Limitation:
      A limitation with this control is, it does not allow to fill-in values.

      Resolution:
      Finally, i've utilized another control that allows to add a new term is delaware Digital Workplace React Fabric Taxonomy picker.
      https://www.npmjs.com/package/@dlw-digitalworkplace/react-fabric-taxonomypicker

      Use below command to install npm package in your solution.

      • npm i @dlw-digitalworkplace/react-fabric-taxonomypicker

      Import module as below:

      • import { TaxonomyPicker } from "@dlw-digitalworkplace/react-fabric-taxonomypicker";

      Use taxonomy picker control as below:

      <TaxonomyPicker
        title="Select your demo data"
        absoluteSiteUrl={this.props.absoluteSiteUrl}
        label="Demo picker"
        termSetId={this.props.termSetId}
        rootTermId={this.props.rootTermId}
        itemLimit={this.props.itemLimit}
        allowAddTerms={true}
        lcid={this.props.lcid}
        showTranslatedLabels={this.props.showTranslatedLabels}
        isLoading={false}
      />

      Now, when user types in new term and press enter key, it will automatically create a new term in your selected term set id.

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

      July 25, 2019

      SharePoint Online Integration with Workday through SFTP

      Sometimes, we need to deal with third party tools/applications from SharePoint Online. To deal with third party tools/applications, we may need a medium like SFTP server to pass data from SharePoint Online to third party tools/applications. In this blog, we are going to showcase - how to create xml/text file on SFTP server from SharePoint Online.

      First, let's understand what's SFTP server and how it can be used to transfer data from SharePoint Online.

      What is SFTP server?

      SFTP - Secure File Transfer Protocol server, is popular method of transferring files between two remote systems over a secure connection. In this blog, we will see how to transfer data from one application - SharePoint Online to another remote server application (e.g. Workday).

      Create file on SFTP server using Rest API & Web Service in SharePoint Online:

      We can follow below steps to create file on SFTP server:

      Step 1: Create a page in SharePoint Online and add custom button using HTML and JavaScript file. On Custom button click event, JavaScript file would call web service to create file on SFTP server.

      Step 2: Create a web service (c#) using Visual studio.
      • Install “SSH.NET” package to deal with the SFTP server.
      • Find below code to connect SFTP server.

      var client = new SftpClient("Server URL", "User Name", "Password");
      client.Connect();
      • Then, stores incoming data into memory.

      byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
      var memory = new MemoryStream(bArray);
        • Now, Upload/Create a file on SFTP server.

        client.UploadFile(memory, "/Folder path /File Name", null);
        • At the end, close the connection with SFTP server.

        client.Disconnect();
        client.Dispose();
          • The complete code for SFTP server integration is as below:

          [WebMethod]
          [ScriptMethod(ResponseFormat = ResponseFormat.Json)]   
          public string UploadFileToFTP(string data)
          {
               var client = new SftpClient("Server URL", "User Name", "Password");
               client.Connect();
               byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
               var memory = new MemoryStream(bArray);
               client.UploadFile(memory, "/Folder path /File Name", null);
               client.Disconnect();
               client.Dispose();
          }
          

          Step 3: Publish this web service on Microsoft Azure platform.

          Step 4: Add following code to JavaScript reference file in SharePoint Online. In this step, we would get data from SharePoint Online list and pass data to web service to create file on SFTP server.

          function MoveFileToSFTP()
          {
               var DataURL = "'ServerURL'/FTPFileUpload.asmx/UploadFileToFTP";
               var request = new XMLHttpRequest();
               var Data = JSON.stringify(data);
               var params = "data="+Data+"";
               request.open('POST', DataURL, true);
               request.onreadystatechange = function() {
                  var responsetext = request.responseText;
           if (request.readyState==4)
           {
              alert("File has been created successfully on SFTP server.");
           }
               };
               request.setRequestHeader("Content-type", "application/x-www-  form-urlencoded");
               request.setRequestHeader("Content-length", params.length);
               request.setRequestHeader("Connection", "close");
               request.send(params);
          }
          
          This way, we can upload files to SFTP from SharePoint Online programmatically.

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

          Power BI - Implement JSON Theme for Report Development

          We have seen many times, there are numbers of reports to be developed with identical color schemes and themes. For this type of report development, it is preferable to use JSON theme for more ease and to save time.

          So, here we have created a similar JSON Theme which has all the visuals formatted and with attached background image within the JSON Theme file which gives a custom look and feel to the Power BI Report.

          It is dual-color saturating themes which are Dark and Light themes.

          This is how the Dark Theme looks like:

          And the Light Theme looks like this:

          Note: Theme will not be applied to Custom Visuals or third-party visuals. Here, we are providing both these Dark & Light Theme JSON files to download and use for your reports as required:


          The downloaded Power BI Themes can be used in any Power BI Report (in Power BI Desktop) following steps mentioned below:

          Step 1: Download the attached JSON file.

          Step 2: Open Power BI Desktop and click on “Switch Theme” in the Home Ribbon.

          Step 3: Click on “Import theme” in the Switch Theme menu.

          Step 4: Navigate through and select the downloaded JSON theme.

          Step 5: Once it is imported then click “Close”.

          With this, we will have all styling from JSON Theme applied to Power BI visuals. And we can reuse the same JSON Theme for multiple Power BI Reports as required to save time defining the styling for each visual individually in each and every report. Happy Reporting!!

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

          July 8, 2019

          This web site has been configured to disallow editing with SharePoint Designer. [Resolved]

          Problem Statement:
          While opening a SharePoint Site in SharePoint Designer, I was getting an error like "This web site has been configured to disallow editing with SharePoint Designer. Contact your web site administrator for more information".


          Resolution:
          To open specific site in SharePoint Designer, we need to verify the configuration at 3 levels:
          1. Web Application
          2. Site Collection
          3. Site 

          Let's verify the configuration at all 3 levels and update the configuration to allow specific site to be opened from SharePoint Designer.

          1. Web Application Level:
          • Open Central Administration.
          • Navigate to Application Management -> Manage web applications.
          • Select specific web application for which this issue is appearing.
          • Click General Settings -> SharePoint Designer.

          • "Enable SharePoint Designer" option must be selected in newly opened window. If this is unchecked, please check this option and click "OK".

          2. Site Collection Level:
          • Open SharePoint Site in browser for which this issue is appearing. If you are getting this issue for any sub-site, please open root level site in the browser.
          • Navigate to "Site Settings".
          • Click "SharePoint Designer Settings" under "Site Collection Administration" section. (User performing this action must have "Site Collection Administrator" rights).
          • "Enable SharePoint Designer" option must be selected. If this is unchecked, please check this option and click "OK".

          3. Site Level:
          • Login to SharePoint Server and open SharePoint Management Shell with Farm Administrator access rights.
          • Execute following PowerShell:
             $web = Get-SPWeb <http://name.domain.com/sites/sitename/sub-site>    
             $web.AllProperties  
            

          • You will notice that the vti_disablewebdesignfeatures2 contains a value of wdfopensite.
          • To resolve the issue, enter the following command in the same PowerShell window:
             $web.AllProperties.Remove("vti_disablewebdesignfeatures2")  
             $web.Update()  
            

          Conclusion:
          This way, we can verify if specific site is allowed to open in SharePoint Designer. If site is not allowed to open in SharePoint Designer, we can also update the configuration with steps mentioned above for Web Application Level, Site Collection Level as well as Site Level.

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

          June 17, 2019

          Combine data from multiple SharePoint Lists using Power Query in MS Excel - Part 1

          Have you ever received a requirement to get data from SharePoint list and you wanted to modify it, but, you have trouble with the same?

          Well, Microsoft Excel has been evolving on a regular basis, making it a powerful tool. But are we aware of the powerful things, that we don't use on a daily basis? Here, is one such function i.e. Power Query.

          What is Power Query?
          Power Query is an add-in for business intelligence available in Microsoft Excel and now in Power BI. Similar to SQL query, it gives you the functionality to modify or create new model of the data by combining various data sources like relational databases, Excel, text and XML files, OData feeds, web pages, SharePoint, Hadoop file (HDFS), Exchange, SAP HANA, etc. and utilize it for further processes. 


          Example:
          Here, in this example, we would be getting the data of 2 SharePoint lists, and combining those, to make a new model for our use. 


          Please Note: This blog provides information on the basics like using the Power Query, creating functions, and using those functions to model data for further use.

          In this example, we have 2 SharePoint Lists - Subject Name and Students. 
          • Subject Name list is the master list of the subjects. 
          • Students list contains the data of students of different grades and Subject Name they excel at. 
          Requirement:
          We need to get list of students those who are Primary school i.e. Grades <=7, and High School i.e. Grades > 7 in Excel.



          Implementation : Connecting SharePoint List with Excel and transforming data.

          So, we begin with connecting SharePoint online data with Excel:
          • In the Data Tab, from the ribbon > Get Data. In this you would find various data connectors, and we would be going for SharePoint Online List. So, under the "Get Data menu > From Online Services > From SharePoint Online List.
          • Now, in the open SharePoint List dialog box, we would enter the Site Collection/Subsite URL of List1.
          • This would ask us to authenticate the details, and ask us for the credentials.
          • On Clicking Sign In, this would open Office 365 login page pop-up dialog box. Enter the account details. On authentication, it would show that you have signed in and Click on Connect. 
          • On Clicking Sign In button, this would open Office 365 login page pop-up dialog box. Enter the account details.
          • On authentication, it would show that you have signed in and Click on Connect. This would open up the total list and libraries in the site. 
          • Here, select the List1 from which the data is to be fetched. In our case, we would be using Subject Names list. On selection, on the right, you would see the preview of the data in a table. 

          Please note, that while fetching the data, it brings all the data in the list, including the hidden and back-end columns, not generally used and is recommended not to be used.
          • Now, there are 2 options, Load and Edit.
            1. Load: This would fetch the information as it is. Then, the changes would be made in the Excel file, but every-time, the data would fetch all the information. This would being unwanted data, and specially, when the data volume is very high, this is not recommended.
            2. Edit: The recommended approach, if the data is too large, to have a cleanup performed. This would open up our table, where we can make changes in the model  
          • So, we would be click on Edit to edit the fields, to change the model and get limited data only. This would open up Power Query Editor window. 
          • We will remove the unwanted columns, by selecting the columns and then right-clicking the column to remove the columns. 
          • We have removed the unwanted columns and our List 1 is ready. Now, we have just the Subject Name and Description. This would look like something as below.
          • Now, we would be adding another list here, in the same Power Query Editor window. We can add our 2nd list, by clicking New Source > Online Services < SharePoint Online List. Alternatively, we can right-click in the Queries section and select New Query > Online Services > SharePoint Online List. This would open the same dialog box to enter the SharePoint Site URL.
          • We will add Students which contains the Student Names, their grades and the subject name (lookup of the Subject Name). We will clean it by removing unwanted columns, and we would be having just Student Name, Grades and Subject Name. Now, as this is a lookup, we would be seeing "Record". So, we would be expanding it and selecting the Title column i.e. - Subject Name. To expand, we will click the expand button besides the column Subject Name and select Title column.
          • So, in this part, we have added the data source list, and modified the data as per our requirement.
          In the next part, we would creating function in Power Query Editor, to get the students per the grade, and and use those for different purpose. 

          Use SharePoint data in Microsoft Excel using Power Query - Part 2

          #PowerQuery #MSExcel #Excel #Query #SharePoint #Office365 #SharePointOnline #SharePointList #PowerBI #MicrosoftSharePoint


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

          June 7, 2019

          Column formatting in SharePoint Modern List View

          Requirement:
          While working with one of SharePoint Modern UI project, there was requirement to display Reminder Date with red background color in  default List View, if it is due within next 7 days from today.

          Challenge: 
          JS link is the one way to meet the requirement. But in OOTB Modern List view, there isn't any option to reference JS link in Miscellaneous section which is available in Classic view.




          Solution:
          Using Column Formatting option, we've achieved the desired results. You can find this option while editing the column as shown in below screenshot.


          Here, we need to write JSON code to format the field value. Here is code snippet to display "Reminder Date" field with red background and text color in white.

          {
            "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
            "elmType": "div",
            "debugMode": true,
            "txtContent": "@currentField",
            "style": {
                 "color":  "=if([$Reminder_x0020_Date] <= @now+604800000, if([$Reminder_x0020_Date]                            >= @now,'#ffffff',''), '')",
          "background-color":"=if([$Reminder_x0020_Date]<= @now+604800000,if([$Reminder_x0020_Date] >= @now,'#ff0000',''), '')" }
          }

          Note: 604800000 is in millisecond, equals to 7 days.

          Output: Date column will be rendered in default list view as per below screenshot.


          Limitation:
          • It will not work with SharePoint Online Classic View.
          • If formatting of the column is based on value of another column from the list, than that column must be available in List View.
          If you have any questions you can reach out our SharePoint Consulting team here.

          February 7, 2019

          Multithreading in PowerShell & Exchange Online (Office 365)


          Challenge:
          The details of a few (around 300) users' mailbox in Exchange Online were to be fetched, and the approach is via PowerShell. Now, the total users were over 3800, and to filter from those, consumed a lot of time. As a result, going the conventional way of getting those one-by-one took us 39 seconds per user which was around 3.5 hours for the whole script execution.  

          Resolution:
          We had to find another way to get this done. So, we chose the approach of getting multiple records simultaneously, using background processes. 

          And, multi-threading came to the rescue!!

          We created the connection to Exchange Online via PowerShell:
          $Credentials = Get-Credential
          
          $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri  https://outlook.office365.com/powershell-liveid/ -Credential $Credentials -Authentication  Basic -AllowRedirection
          
          Import-PSSession $Session 

          Then, we read the user display names from the file, and stored them in an array. 
          $accounts = Import-CSV C:\Temp\File.csv
          

          Now, instead of specifying the number of jobs, we would want the jobs to be automatically created.

          So, we would be specifying, the batch size of each job. In our case, we chose 50 users per batch.

          $accountsperBatch = 50
          

          Then, we iterated the loop for all the accounts to be split into batches as jobs, and then started the job using Start-Job for creating the job. 

          This Start-Job  needs:

          1. ScriptBlock in order to job to know, what it has to do.
          2. ArgumentList in order to job to know, the items using which the job is to be performed.    

          On specifying this, PowerShell would begin the creation of jobs, and as the job is created, the code within the ScriptBlock is executed.  
          $accountsperBatch = 50
          
          $i = 0
          $j = $accountsperBatch - 1
          $batch = 1
          $csvContents = @() 
            
          while ($i -lt $accounts.Count)
          {
           $accountBatch = $accounts[$i..$j]
           
           $jobName = "Batch$batch"
           $fileName = "c:\Temp\Details$jobName"
           Start-Job  -Name $jobName `
                 -ScriptBlock {
                   param([string[]] $accounts)
            foreach($account in $accounts)
            {
             $mailBoxDetail = Get-Mailbox  -Identity  $account | Select  DisplayName, UserPrincipalName 
             $row =  New-Object System.Object
             $row | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value  $mailBoxDetail.DisplayName
             $row | Add-Member -MemberType NoteProperty -Name "UserPrincipalName" -Value  $mailBoxDetail.UserPrincipalName
          
             $csvContents += $row  
            }
                          $csvContents | Export-CSV $fileName
                       }`
                -ArgumentList (,$accountBatch)
               
           $batch += 1
           $i = $j + 1
           $j += $accountsperBatch
           
           if($i -gt $accounts.Count) {$i = $accounts.Count}
           if($j -gt $accounts.Count) {$j = $accounts.Count}
          }
          

          Then, as per the best practices, we would remove the jobs completed by using Remove-Job  and the PS Session using Remove-PSSession $Session. 

          This script helped us to generate the details of the user accounts simultaneously and the results improved drastically and the results were available under 1.5 hours.

          Note: If we open too many connections with Office 365, it would suspend the connections, as the security mechanism at Office 365 would consider this as a DDoS attack, thus, giving us an error like remote host not found or the connection was closed. 

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