Salesforce and Office 365 Integration step by step

In this post I will show how to integrate Office 365 Documents in Sales force.

Requirement is to view Office 365 Documents from sales force.

Steps can be categorized as:

1. Sign up for Sales force trial account.
2. Enable Files Connect option in sales force.
3. Create Auth.Provider in sales force.
4. Create External Data Source in sales force.
5. Sign up for Office 365 trial account.
6. Create Document Library and upload Documents.
7. Register an APP in Azure web site(sign up if not available) for sales force.
8. Browse through the Office 365 Documents from sales force.

All you need to have is Office 365 Site, Azure web site and Sales force Instance Trail accounts.

1. Login to Salesforce

2. Enable Files Connect feature by searching File connect from search box from Setup page.

Files Connect allows you to connect Sales force to your Enterprise Content Management (ECM) system.

Currently supported ECM systems are:

• SharePoint 2010 & 2013 on-premises.
•  SharePoint Online.
•  OneDrive for Business.
•  Google Drive
•  Box

Edit, enable the Files Connect check box and Save. Files connect settings has two Sharing methods. Copy and Reference. Choose any one as per your requirement.

choose copy if you want files to be copied between the two systems.

choose Reference if you want just reference to files instead of copies.

Go to Permission Sets and give permission.

click on edit add File connect cloud, add and save.

3. From Setup key in Auth.Provider in search box and create new Auth. Provider

Choose following:

Provider Type: Open ID Connect

Name: SPAuthProvider

URL Suffix: SPAuthProvider

Consumer Key: SPAuthProvider

Consumer Secret: SPAuthProvider

Authorize End Point URL: https://

Token End Point URL: https://

Remaining fields leave as default and save. All the above field should be edited later according to upcoming steps.

Click on save. * Note down the call back URL to register this in Azure web site.

4. Create Azure web website, if not, sign up under https://manage.windowsazure.com/

5. Create Office 365 Site Collection, if not, sign up under https://login.microsoftonline.com/ . Create site collection and upload some Documents into Documents library.

6. Go to Azure web site and choose Active Directory, choose App registrations and click on New Application Registration.

Enter Name, Application Type-Webapp/API, sign on url – provide site collection url and click on create.

Click on created application note down Application ID and Object ID.

Under Reply URLs, add sale force Call back URL that was generated in Salesforce.

https://login.salesforce.com/services/authcallback/00D7F000004A1WuUAK/SPAuthProvider

under Owners, add owner for this app.

Note: The user in Salesforce and Office 365 should be same. if not create users/groups accordingly if needed in both salesforce and office 365.

Under Permission, delete the existing and grant/ create new one with the required permissions

Under keys, provide Description, expiration and click on save. value will be auto generated and note it down separately. This value cannot be view further so be sure to note the value.

7. Now go back to salesforce and edit the Auth.Provider and provide all the details like Consumer Key, Secret, Authorize End Point URL, Token End Point URL etc.

Consumer key is nothing but Application ID generated in Azure.

Consumer secret is the value generated under keys expiration.

Authorize end point URL:

The format is “https://login.microsoftonline.com/common/oauth2/authorize?resource=https%3 A%2F%2F[YOUR_COMPANY_NAME].sharepoint.com&prompt=login”

eg: https://login.microsoftonline.com/common/oauth2/authorize?resource=https%3A%2F%2Ftestsoftindia.sharepoint.com&prompt=login

Token end point URL: https://login.microsoftonline.com/common/oauth2/token

Click on Save.

8. Go to External Data Sources and create new Data Source with following details.

It will ask for SharePoint Online credentials. Once provided authentication, asks to trust the application, once trusted, Status will be changed to Authenticated from Pending.

9. Create External Object the external data source like below.

Validate and sync

10. From Permission set page,

choose external data source access.

click edit and add data source and save.

11. go to app launcher, and click on files.

12. under connected source, you will be seeing Office 365 files

clicking on document title will take the user to office 365 site.

 

 

 

 

 

 

 

 

Encrypt/Decrypt appsettings in webconfig file of SharePoint

At times, we need to encrypt app settings  of web config file that we use in code behind to keep information secured.

we can encrypt/decrypt settings by using  the ASP.NET IIS registration tool (aspnet_regiis.exe)

Steps:

Open command prompt window.

Go to the framework path.

cd C:\Windows\Microsoft.NET\Framework\v4.0.30319 by using cd “C:\Windows\Microsoft.NET\Framework\v4.0.30319”

Execute the following command to encrypt the appsetting in webconfig.

Before encryption:

#to encrypt

aspnet_regiis -pef appSettings “C:\inetpub\wwwroot\wss\VirtualDirectories\2039”

After encryption:

# to decrypt

aspnet_regiis -pdf appSettings “C:\inetpub\wwwroot\wss\VirtualDirectories\2039”

Note: replace the port number to respective number based on site virtual directory.

Hide or Remove header and footer from SharePoint modal pages

If your SharePoint site has custom master page with header and footer, these will come along through all inner pages, SharePoint default dialog windows/popups in which we may not require those to display.

To hide those header and footer elements in all inner pages, we can use/add a css class to hide them like below.

That’s it.

before:

after:

Save Site as Template Missing

In SharePoint 2013, you cannot save a publishing site as a site template. To save the site as template try to navigate to _layouts/savetmpl.aspx . Probably you will get an error  “the save site as template is not supported on this site”.

As a workaround, you can open SharePoint Designer and browse the site and look for site options and set “SaveSiteasTemplateEnabled” to true from false.

After setting this try to open the application page savetmpl.aspx from browser and you will be able to save the site as template with/without content for migration.

 

Power shell script to create site columns

Here is the powershell script for creating site columns for SharePoint.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Start-SPAssignment -Global

##$site
$site = Get-SPWeb -Identity “<<Site Name>>” -ErrorVariable err -ErrorAction SilentlyContinue
$Option=[Microsoft.SharePoint.SPAddFieldOptions]::AddFieldInternalNameHint

#### SITE COLUMNS
#### StatusID Number Column
$FieldSchema = ‘<Field Type=”Number” Name=”StatusID” DisplayName=”StatusID” Required=”TRUE” EnforceUniqueValues=”FALSE” Indexed=”FALSE” MaxLength=”255″ Group=”Status Columns” />’

[bool]$createColumn = $true
if($createColumn)
{
$newField = $site.Fields | where {$_.InternalName -eq “StatusID”}
if($newField -eq $null)
{
$site.Fields.AddFieldAsXml($FieldSchema)
}
}
$site.Update()

#### IsActive bool Yes/No
$FieldSchema = ‘<Field Type=”Boolean” Name=”StatusIsActive” DisplayName=”StatusIsActive” Group=”Status Columns” DefaultValue=”0″ />’

[bool]$createColumn = $true
if($createColumn)
{
$newField = $site.Fields | where {$_.InternalName -eq “StatusIsActive”}
if($newField -eq $null)
{
$site.Fields.AddFieldAsXml($FieldSchema)
}
}
$site.Update()

#### ApplicationLead People picker
$FieldSchema = ‘<Field Type=”User” Name=”ApplicationLead” DisplayName=”ApplicationLead” Required=”TRUE” UserSelectionMode=”PeopleOnly” Group=”Status Columns” />’

[bool]$createColumn = $true
if($createColumn)
{
$newField = $site.Fields | where {$_.InternalName -eq “ApplicationLead”}
if($newField -eq $null)
{
$site.Fields.AddFieldAsXml($FieldSchema)
}
}
$site.Update()

#### RequestedDate DateTime
$FieldSchema = ‘<Field Type=”DateTime” Format=”DateOnly” Name=”RequestedDate” DisplayName=”RequestedDate” Required=”TRUE” EnforceUniqueValues=”FALSE” Indexed=”FALSE” MaxLength=”255″ Group=”Status Columns” />’

[bool]$createColumn = $true
if($createColumn)
{
$newField = $site.Fields | where {$_.InternalName -eq “RequestedDate”}
if($newField -eq $null)
{
$site.Fields.AddFieldAsXml($FieldSchema)
}
}
$site.Update()

#### BusinessJustification -Rich Text
$FieldSchema = ‘<Field Type=”Note” Name=”BusinessJustification” DisplayName=”BusinessJustification” NumLines=”6″ RichText=”FALSE” Group=”Status Columns” />’

[bool]$createColumn = $true
if($createColumn)
{
$newField = $site.Fields | where {$_.InternalName -eq “BusinessJustification”}
if($newField -eq $null)
{
$site.Fields.AddFieldAsXml($FieldSchema)
}
}

#### ApplicationLookup Lookup column

#Source list

$listApplication=$site.Lists.TryGetList(“Applications”)
write-host $listApplication.id
$FieldSchema = ‘<Field Type=”LookupMulti” Mult=”TRUE” List=”‘+$listApplication.id+'” ShowField=”ApplicationName” Name=”ApplicationLookup” DisplayName=”ApplicationLookup” Group=”Status Columns” />’
write-host $FieldSchema
[bool]$createColumn = $true
if($createColumn)
{
$newField = $site.Fields | where {$_.InternalName -eq “ApplicationLookup”}
if($newField -eq $null)
{
$site.Fields.AddFieldAsXml($FieldSchema)
}
}
$site.Update()

$site.Dispose()

Stop-SPAssignment -Global

SharePoint Reusable Workflow(has lookup) Migration

To migrate SharePoint Designer reusable workflow, please follow below steps.

1. Create and Develop the workflow with all the steps as required.
2. Save the workflow as a template from source server. This will get you .wsp file saved to site assets library.
3. Go to site assets and export the file to your physical location and move wsp file to destination server.
4. Now go to Destination server SharePoint site where you want to move or migrate the workflow and go to Site Settings-> Solutions and upload the wsp and activate the feature/solution.
5. Then go site features of the site and check for the workflow feature and activate the feature.
6. That’s it if your feature is activated without any error your workflow migration is complete and success. If you find any errors while activating there may be missing lists/information for the workflow to get activated.You will be seeing migrated workflow in SharePoint Designer of Destination site.
7. For more info also check ULS logs.
8. Important thing is if your workflow uses references to another lists/libraries, lookups etc., make sure that all the lists are migrated before proceeding with the workflow migration.
9. For trouble shooting with workflow errors please check this

 

Workflow Migration Error/ SharePoint Designer Error

If you get any error related to Windows workflow foundation, part of .Net framework 3.0 must be installed to use this feature as shown below.

Close SharePoint Designer. Try clearing the SharePoint Designer cache folders completely from below locations

%APPDATA%\Microsoft\Web Server Extensions\Cache

%APPDATA%\Microsoft\SharePoint Designer\ProxyAssemblyCache

%USERPROFILE%\AppData\Local\Microsoft\WebsiteCache

Restart the SharePoint Designer and try to open the Workflow. If still error exists, time to check if your workflow contains any lookup columns and ensure that all the lists/lookup lists are in place in destination server before migrating or opening the workflow.

You can still check the ULS logs to find out the list name/lookup lists etc., that workflow is referring internally.

Also after migrating your Designer workflow, check your workflow feature is successfully activated or not. Activated feature will not cause the above error when you try to open the workflow in SPDesigner.

In my case, I missed lookup list to migrate and got the above error after migrating the workflow alone.

I will blog how to migrate SharePoint Designer Workflow with lookup columns in next blog series.

 

Workflow Errors SharePoint 2013

In most of the cases, we will be getting workflow cancelled errors for SharePoint Designer Workflows:

Workflow cancelled Possible causes:

1. As a first step, create a simple workflow with only one step probably log message and check to see if that works. Trouble shoot with different users, different sites etc.
2. Check whether logged in user has appropriate permissions on the list/library, workflow task list, workflow history list.
3. Republish with new task list and new workflow history list.
4. Identify and check where the workflow gets cancelled and why cancelled by going to internal status to see if you can find more information.
5. Check Workflow Manager configured properly and verify the workflow service is in connected status in Central Admin. If you have configured Workflow Manager for Multiple Server Farm, you have to install Workflow Manager in Application Servers and Workflow Manager client in all the WFE servers connected.
6. Most of the errors will be 401 error, 400 error
7. Also check User Profile Service Application is configured and full User Profile Synchronization has been done.
8. Check workflow related features are activated under Site features like Workflow can use app permissions.
9. Restart Central Admin Timer job named “Refresh Trusted Security Token Services Metadata feed”
10. Re-register workflow service.
11. Check ULS logs and enable workflow management logs in Event viewer.
12. Check Application Management Service is configured in Central Administration or not.
13. Reset IIS

Excel Services based Report for SharePoint List Data

In this post I’m showing how to generate Report/Dashboard or Pivot Table/Pivot Chart for SharePoint List 2013 Data.

1. Firstly we need to create connection to SharePoint list to pull Data. For this, Open Excel 2013, Go to Data tab and choose From Other Sources and select OData Data Feed( used to import data into Excel as a Table or Pivot Table)

2.  From Data Connection wizard choose the link or file from which we want to get data. We can use listdata.svc for getting SharePoint list Details and Data. Enter the service url as shown below and Logon credential details and click Next:

https://<siteurl>/_vti_bin/listdata.svc

3. Select the table that you want to import and click Next and save the connection with relevant name and finish.

4. Choose your selection from various options like Table, Pivot Table report, Pivot chart etc and click on Ok.

5. By this you will be having a connection to SharePoint list and all the SharePoint List columns will be displayed Field List pane and Chart to bind this data. You can choose columns that are need for pivot chart to display graphically.

I have selected  Project Name and Work Hours column to be displayed in chart. You can still refine your Chart like showing Title,  Legend, Axis, Data Labels etc. You can even add Slicers to filter based on selections.

You can even add Pivot Table and Chart both.

Here is the complete overview of my Excel Services Report and save and publish to your SharePoint BI Center center.

 

Central Administration v4 Application pool 2013 Stops automatically

I found recently that Security token service application pool, Central Administration Application Pool and SharePoint sites application pool stops automatically when I browse them through IIS which results in Service Not available error and found couple of solutions for this

1. Either Password in Active Directory Services might be expired.

In this case, we need to update Identity for Application pool under Advanced settings with updated credentials(applicable to all Application pools like STS, Central Admin, SharePoint site Application Pool).

2. If still issue occurs, Ensure that all the application pools run under .NET framework 4.0 and change enable 32-bit application set to False and have a try.

3. If still issue occurs, here is the final workaround in my case, Rerun the SharePoint Configuration Wizard and this will fix the issue. Also make sure that there is no blocking antivirus which will not allow the required services from starting.