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.