Challenges faced after exporting Excel data to SharePoint

In previous article, we have seen how to connect to SharePoint site from excel for exporting data. Today, let us see the challenges faced in SharePoint after exporting the data.

  1. Number columns with greater than or equal to 4 digits in excel – Lets consider, Application ID column with value 20158 for better understanding. As number column in excel are associated with number column in SharePoint, they are displayed with commas as 20,158
    • To remove the commas, never try converting it into Single line of text in SharePoint because it will be converted into exponential form as 20+158e
    • If you would like to show them without commas, create a calculated column with the below formula:
    • =TEXT([Application ID], “0”)
    • By using this formula, Commas will be removed but calculated columns will be shown only in display form
    • So, before exporting it is recommended to convert number field with more than 4 digits in excel to text column as to associate it with single line of text. Now, to prevent the users from entering number in Single line of text field, follow the below steps:
    • Go to List->List settings and click on Application ID column
    • It takes us to Change Column page for Application ID where we need to enter the below formula in Data validation section and click ok
    • =ISNUMBER([Application ID]+0)
  2. Person or Group column in SharePoint for user names like Manager in excelAs text columns are associated with  Single line of text, they cannot be converted to Person or group column. What if we need to use People Picker(Person or Group) for user names. Below is a small workaround which will be useful if there are thousands of items.
    •  If number of items is less, create a person or group column, open edit form of each item and copy paste the value manually from single line of text to Person or group field. When there are large number of items(say 2000), create below two columns in SharePoint:
    • Flag – Yes/No type
    • Mgr – Person or group type in SharePoint
    • Go to SharePoint designer and create a workflow which should run whenever an item is changed
    • Create the below step:
      • If flag equals Yes
      • set Mgr(Person or group column) to Manager(Single line of text)
    • Now, go to Datasheet view of the list and simply select Yes and drag the Yes value till the last item
    • Advantage: It reduces the time than doing it manually
    • Disadvantages:
      • Workflow fails when exact match is not found meaning either the person has left the organization or their Id in active directory is changed
      • We need to manually fill for those items where workflow has failed
      • Remove the workflow from the list once all the user names are copied to Person or group column
  3. Missing “Add new item” link in SharePoint 2010 – To overcome this, create a Standard view and set it as default view

If the above post is helpful, do not forget to like my article and rate it.


Exporting Excel table to SharePoint

SharePoint is used everywhere especially for document and content management. Nowadays, data that were stored in excel are being moved to SharePoint , thus making the ease of access and management of data. To facilitate this, SharePoint has an app(list) called “Import Spreadsheet” as shown below:

import-spreadsheet creating-sp-list

This approach is known to everyone where we select Import Spreadsheet list, provide a name and description, browse the excel file, select the range of cells to be imported. But only few of us know how to connect from excel to SharePoint. This article will cover the later approach. Let us see how to do.

  • Open desired sheet in excel and select the range of cells to be moved to SharePoint(both rows and columns)
  • Click on Insert->Table. In create table dialog box, check the range and hit ok


  • Now, you will be able to see Design Tab in the ribbon.
  • Under External Table Data section, click on Export->Export Table to SharePoint List


  • In Export table step 1 dialog box, give the address of SharePoint site, check create a read-only connection box, provide a name and description for SharePoint list and click Next


  • “Create a read only connection to the new SharePoint list” box is used to keep the excel table up-to-date with the changes made in SharePoint list. This is only one way meaning, you cannot update changes from excel to SharePoint once it is exported. When you check the box, it creates two columns in the excel table
    • Item Type  – Item/Folder
    • Path – folder path for a list item
  • In step 2, we can verify the data types of excel columns associated with data types in SharePoint. For example, Student Name of type text in excel is associated with Single line of Text in SharePoint. After verification, click Finish button.


  • Table has been published successfully and two columns(Path and Item Type) are created in excel as said previously


Excel Table

SharePoint List
  • To demonstrate the power of “Create a read-only connection to the new SharePoint list”, let us edit the mark of student, Chitra in SharePoint list


  • Then go to excel and click Design->Refresh. Excel table is updated as depicted below


  • To remove this read-only connection, click on Design->Unlink



  • This approach is useful when we need to check the associated datatypes(shown within braces) before exporting data to SharePoint. If we need to change the data type, cancel step 2, change datatype in excel and follow step 1 & 2 again


  • Creating a read-only connection allows to keep excel report up-to-date rather than often exporting updated data from SharePoint list.

If you found this post useful, please press like button, rate this article and share your comments.

PART 2 : Working in MOSS using SPServices

In this article, we will see how to set and get People Picker value using SPFindPeoplePicker.

Get value from the people Picker:


var ppl = $().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "User", //Display Name of People Picker Field
checkNames: true


Set value to the People Picker:

To set static value in  the People Picker:


peoplePickerDisplayName: "User",
valueToSet: "kamal",
checkNames: true


To set logged in user name to the People Picker:


var user= $().SPServices.SPGetCurrentUser();
peoplePickerDisplayName: "User",
valueToSet: user,
checkNames: true


PART 1 : Working in MOSS using SPServices

After working in SharePoint 2010 and 2013 platform, it was bit difficult for us to work in SharePoint 2007.  SPServices came to our rescue. I have consolidated different scenarios we encountered and placed them in a single post below. So, it might help someone in the future.

Download the below files, save and provide a reference to these scripts before using the below script:

SPServices to populate people picker with logged-in username and to disable it in IE and Chrome:

<script type="text/javascript"> 
    var currentUserName ;   
    $(document).ready(function() {
    currentUserName = $().SPServices.SPGetCurrentUser({
    fieldName: "Title",
    debug: false
 if((navigator.userAgent.indexOf("MSIE") != -1 ) || (!!document.documentMode == true )) //IF IE > 10
          alert("Browser used for display is IE");          
      $("div[title='People Picker']").text(currentUserName);
      $("div[title='People Picker']").prop('disabled',true);
       $('img[title="Check Names"]').css('display','none');
      else if(navigator.userAgent.indexOf("Chrome") != -1 )
          alert("Browser used for display is Chrome");  
   $("textarea[title='People Picker']").val(currentUserName);
      $("textarea[title='People Picker']").prop('disabled',true);
      $('img[title="Check Names"]').css('display','none');    

SPServices to check whether the user belongs to the specific SharePoint group(say, Admin):

<script language="javascript" type="text/javascript">
      operation: "GetGroupCollectionFromUser",
      userLoginName: $().SPServices.SPGetCurrentUser(),
      async: false,
      completefunc: function(xData, Status) {
        if($(xData.responseXML).find("Group[Name='Admin']").length == 1)
      alert("You belong to admin group");
  //your code for admin users
          alert("You do not belong to admin group");
  //your code for non-admin users

SPServices to perform read operation:

function getListItems(){
var method = "GetListItems";
var list = "Demo";
var fieldsToRead =  '<ViewFields></ViewFields>';
var query = "<Query><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>"+ID+"</Value></Eq></Where></Query>";            $().SPServices({
operation: method,
async: false,
listName: list,
CAMLViewFields: fieldsToRead,
CAMLQuery: query,
completefunc: function (xData, status) {
$(xData.responseXML).SPFilterNode("z:row").each(function () {
var eTitle = ($(this).attr("ows_Title"));

To get the latest version of SPServices and to know more about it, visit this link

Query Strings in SharePoint

Let us see some of the query strings used in SharePoint:

  • contents=1 ->used to troubleshoot the sharepoint page.When it is appended at the end of the url as:


it takes us to webpart maintenance page where we can find the webpart causing the error by trying to close each one and deleting the problematic webpart

  • IsDlg=1->used to hide the header and quick launch(i.e. chrome).Generally used in SharePoint forms.
  • FilterField1=Gender&FilterValue1=Male(FilterField1=columnname&FilterValue1=columnvalue)->used to filter sharepoint list webpart with the condition, Gender=”Male”
  • FilterName=Hobbies&FilterMultiValue=Painting;Reading(FilterName=columnname&FilterMultiValue=columnvalue1; columnvalue2)->used to filter sharepoint list with multiplevalues in singlecolumn
  • PageView=Shared&ToolPaneView=2->used to show the edit mode of the page, browse and add the webpart
  • PageView=Shared&ToolPaneView=3->used to show the edit mode of the page, search and add the webpart
  • SortField=Hobbies&SortDir=Desc(SortField=ColumnName&SortDir=Desc/Asc) ->used to sort the sharepoint list by Hobbies in Descending order
  • _layouts/viewlsts.aspx -> To go to “All Site Content” Page
  • /_layouts/listedit.aspx?List= GUID OF LIST -> Direct us to List settings page of a list. Ex:
  • /_layouts/settings.aspx -> used to go to Site Settings page

Printing Webpart

Let us consider that you have one of the below scenario:

Scenario 1:

You have multiple web parts in a page, say a list and a chart web part and you need to print only chart web part

Scenario 2:

You have an sharepoint list form with quick launch and you need print out without quick launch.


  • Go to the desired page and edit it
  • Insert script editor web part and paste the below code or refer the below code in content editor webpart
  • To find the webpart element ID, press F12, point to the webpart and get the ID

<input onclick="javascript:void(PrintWebPart())" type="button" value="Print Web Part"/>

var WebPartElementID = "WebPartWPQ2";
//change the webpart element ID with yours

function PrintWebPart()
var bolWebPartFound = false;
if (document.getElementById != null)
var PrintingHTML = '\n\n';
if (document.getElementsByTagName != null)
var HeadData= document.getElementsByTagName("HEAD");
if (HeadData.length > 0)
PrintingHTML += HeadData[0].innerHTML;
PrintingHTML += '\n\n\n';
var WebPartData = document.getElementById(WebPartElementID);
if (WebPartData != null)
PrintingHTML += WebPartData.innerHTML;
bolWebPartFound = true;
bolWebPartFound = false;
alert ('Cannot Find Web Part');
PrintingHTML += '\n\n';
if (bolWebPartFound)
var PrintingWindow ="","PrintWebPart", "toolbar,width=800,height=600,scrollbars,resizable,menubar");;


We have seen how to check if user belongs to a particular sharepoint group or not in Part 1. Let’s see how to show/hide/disable sharepoint fields using SPUtility.

About SPUtility

It is a javascript library used to

  • set or get field values
  • Show or hide fields
  • Make the fields as read-only

It is compatible with SharePoint 2010, SharePoint 2013, O365 and MOSS 2007.


Download the below files, copy and save it as .js

To set values when form is loaded:

function Default()

You can also use in jquery as:


To get the values:

var status = SPUtility.GetSPField('Status').GetValue();

To show, hide and disable the fields:


Insert these codes as per your requirement into previous posted code.

Check if user belongs to SharePoint group

In SharePoint, we need not allow all the users to see all the controls in the form. We may need to show, hide or disable the SharePoint controls for users in particular SharePoint group. Lets see this as two parts.

  • Check if logged in user belongs to SharePoint group(in SharePoint 2010, 2013)
  • Show/Hide/Disable fields using sputility.js

PART 1: Check if logged in user belongs to SharePoint group

  • Create a SharePoint group with the desired permissions and add users to it.
  • When we go to the address bar, we can see the url of SharePoint group as ../_layouts/people.aspx?MembershipGroupId=7
  • ID in the above url is 7. Use this ID for groupCollection.getById(ID) in SharePoint 2010 and use the groupname in sharepoint 2013

SharePoint 2010

ExecuteOrDelayUntilScriptLoaded(IsUserExists, "sp.js");
var group;
var users;
var ctx;
var groupCollection;
var user;
var currentuser;
function IsUserExists()
ctx = SP.ClientContext.get_current();
groupCollection = ctx.get_web().get_siteGroups();
group = groupCollection.getById(7);
ctx.executeQueryAsync(Function.createDelegate(this, this.OnGetGroupSuccess), Function.createDelegate(this, OnFailure));
function OnGetGroupSuccess()
ctx.executeQueryAsync(Function.createDelegate(this, this.OnGetuserSuccess), Function.createDelegate(this, OnFailure));
function OnGetuserSuccess()
var userEnumerator = users.getEnumerator();
while (userEnumerator.moveNext())
var user = userEnumerator.get_current();
if (user.get_id() == currentuser.get_id())
alert("You have access to this form");
//sputility code to show the controls
function OnFailure(sender, args) {
alert("You do not have Permission");
//sputility code to hide/disable the controls

SharePoint 2013:

ExecuteOrDelayUntilScriptLoaded(IsCurrentUserHasContribPerms, 'SP.js');
function IsCurrentUserHasContribPerms()
IsCurrentUserMemberOfGroup("Owners", function (isCurrentUserInGroup) {
// sputility code to show the controls
//sputility code to hide/disable the controls
function IsCurrentUserMemberOfGroup(groupName, OnComplete) {
var currentContext = new SP.ClientContext.get_current();
var currentWeb = currentContext.get_web();
var currentUser = currentContext.get_web().get_currentUser();
var allGroups = currentWeb.get_siteGroups();
var group = allGroups.getByName(groupName);
var groupUsers = group.get_users();
function OnSuccess(sender, args) {
var userInGroup = false;
var groupUserEnumerator = groupUsers.getEnumerator();
while (groupUserEnumerator.moveNext()) {
var groupUser = groupUserEnumerator.get_current();
if (groupUser.get_id() == currentUser.get_id()) {
userInGroup = true;
function OnFailure(sender, args) {

We will see Part 2 in next article.

If my post is helpful to you, please like my article and post your valuable feedback or query in comments.

JS Link

JS Link is one of the client side rendering(CSR) mechanism.

What is meant by CSR?

If the data is transformed by the client rather than the server by using client side technologies such as HTML,CSS and JavaScript, then it is known as CSR.

In Previous version of SharePoint, server side rendering like XSLT and CAML were used. We can even prefer server side rendering(XSLT) in SharePoint 2013, by referring XSL file in XSL Link and checking the Server render box as highlighted in red below.


JS Link can be found in List view web parts under Miscellaneous section(highlighted in black above). It links to a JavaScript file where rendering logic is written. To support JS Link, new content type called JavaScript Display template has been introduced with special site columns such as Target Control type, Target Scope etc. to ensure that CSR is targeted to the correct list web part.

Using JS Link, we can customize or control the rendering of

  • List Views
  • Forms(Display, Edit and New Forms)
  • Fields

Two events used for customization are:

  • OnPreRender
  • OnPostRender

To render a custom template, we need to register the template using the below javascript method:


where options takes the below structure:

var options = {
View: /* function or string */,
Body: /* function or string */,
Header: /* function or string */,
Footer: /* function or string */,
Group: /* function or string */,
Item: /* function or string */,
Fields: {
'Field Internal Name': {
View: /* function or string */,
EditForm: /* function or string */,
DisplayForm: /* function or string */,
NewForm: /* function or string */
OnPreRender: /* function or array of functions */,
OnPostRender: /* function or array of functions */

To find the internal name of the field, navigate to the list settings and click on the field under columns. This will take you to the “Edit Column” page. In “Edit column” page, click on the url to see the internal name of the field located at the end of URL as shown below:

For MDS(Minimal Download Strategy) disabled site:

We wrap the rendering logic in anonymous function so that it executes itself when the page is loaded and the global namespace will not be polluted.

Example 1:

var markContext  = {};
markContext.Templates = {};
markContext.Templates.Fields = {
'Mark': { 'View' : customizemark }
function customizemark()
var tem= ctx.CurrentItem[ctx.CurrentFieldSchema.Name];
return "<span style='color:red;font-weight:bolder'>"+tem+"</span>";
else if(tem==50)
return "<span style='color:orange;font-weight:bolder'>"+tem+"</span>";
else if(tem>50)
return "<span style='color:green;font-weight:bolder'>"+tem+"</span>";



Let us consider one more column called Status(Pass/Fail) and have requirement to show only status and not the mark. We can hide the column, ‘Mark’ from view using “Modify View” page but what about the forms. So, in order to hide the Mark field from forms, follow the below example:

Example 2:

(function () {
(window.jQuery || document.write('<script src="//"></script>'));
var hiddenFiledContext = {};
hiddenFiledContext.Templates = {};
hiddenFiledContext.Templates.OnPostRender = hideField;
hiddenFiledContext.Templates.Fields = {
"Mark": {
"NewForm": hiddenFiledTemplate,
"DisplayForm": hiddenFiledTemplate,
"EditForm": hiddenFiledTemplate
function hiddenFiledTemplate() {
return "<span class='csrHiddenField'></span>";
function hideField(ctx) {




For MDS Enabled site:

To apply Example-1 for MDS Enabled site, we need to register the template using:
RegisterModuleInit(relative path to file, function name);

RegisterModuleInit('/SiteAssets/CSR.js', Mark);
function Mark()
var markContext  = {};
markContext.Templates = {};
markContext.Templates.Fields = {
'Mark': { 'View' : customizemark }
function customizemark()
var tem= ctx.CurrentItem[ctx.CurrentFieldSchema.Name];
return "<span style='color:red;font-weight:bolder'>"+tem+"</span>";
else if(tem==50)
return "<span style='color:orange;font-weight:bolder'>"+tem+"</span>";
else if(tem>50)
return "<span style='color:green;font-weight:bolder'>"+tem+"</span>";

When we use JS Link in a page where there are more than one list view, the rendering or customization may not appear correctly. To ensure that it is applied to the respective views, three additional options are used:

  • List Template Type – ID of list template
List template type Template ID
Custom List 100
Document Library 101
Survey 102
Links 103
Announcements 104
Contacts 105
Calendar 106
Tasks 107
Discussion Board 108
Picture Library 109
DataSources 110
Form Library 115
No Code Workflows 117
Custom Workflow Process 118
Wiki Page Library 119
CustomGrid 120
No Code Public Workflows<14> 122
Workflow History 140
Project Tasks 150
Public Workflows External List<15> 600
Issues Tracking 1100
  • View Style – ID of different styles available in “modify view” page. To find the ID of each view style:
    • Go to Modify view page
    • Right click on the page and select view source
    • you can find the ID of the style in <option>
    • Below is the list of styles with ID


0 Basic Table
12 Boxed, No Label
13 Boxed
15 Newsletter
16 Newsletter, no lines
17 Shaded
20 Preview Pane
Default Default
  • Base View ID: Attribute of view element that can be found in SharePoint Designer
    • In General, BaseViewID of “AllItemsView” is equal to 1 and BaseViewID of “Summary view” is equal to 0

Uploading JS File:

It is recommended to upload js file to master page gallery. To upload in master page gallery, follow the below steps:

  • Go to Site settings ->Master pages under Web Designer Galleries (In case of Publishing site, it will be Master pages and page layouts under Web Designer Galleries)
  • In Master page Gallery, click on FILE tab and choose Upload Document
  • Browse the js file from your computer and select the content type as JavaScript Display Template.

Javascript Display TemplateJS Display Template.png

  • Target Control Type: Type of control for which customization is applied to. It can be
    • Form
    • View
    • Field
  • Standalone: Specifies to include js file to override during view selection. Options are
    • Override
    • Standalone
  • Target Scope: URL of the website to which override applies to.
  • Target List Template ID: ID of the list template to which override applies to. Refer the above table under List Template ID
  • Fill these columns and hit Save button
  • Don’t forget to publish it if you are uploading it in publishing site.


  • If you do not have permission to master page gallery, upload it to SiteAssets
  • Now, go to the list web part and refer the uploaded js file as:
    • ~site/_catalogs/masterpage/csr.js if you have uploaded js file in master page gallery
    • ~site/SiteAssets/csr.js if you have uploaded it in Site Assets
  • Use the token ~sitecollection instead of ~ site if you are referring the js file in list web part of subsite
    • Ex:~sitecollection/_catalogs/masterpage/csr.js
  • To refer more than one js file in JS Link, use pipe symbol, |
    • Ex:~site/SiteAssets/csr.js|~site/SiteAssets/Demo.js
  • NOTE: Use CTRL+F5 (Hard Refresh) if changes to the code are not reflected in the browser


  • Performance is better as it rendered by client rather than the server
  • Ease of development


  • JS Link is not supported in Survey and events lists like calendar
  • Browser Compatibility – works differently in different browsers
  • JS Link does not work for custom list forms created in SharePoint designer

Refer more sample codes here

Site Template

How to create a site from existing template?

  • Go to the site (whose template is to be used for new site)
  • Click on Gear wheel->Site Settings
  • Click on Save site as template under Site Actions as highlighted below

Site Settings

  • On clicking the link, it takes to Save as  Template page where file name and Template name should be given
  • Check “Include Content” box and hit OK button.

Save As Template

  • The below message will be shown. Click on the “Solution gallery” hyperlink in it

Operation Completed

  • In Solution Gallery, you can see the template of the site that is created before few minutes.
  • Click on it to download the .wsp file. Now, we have a local copy of the template in our system.

Solution Gallery

  • Go to the Central admin site to create a new site collection
  • Click on New->Private Site Collection

New Site.png

  • Do not select in-built templates. Just click on custom tab and hit OK button.

New Site Collection

  • In Template selection, you cannot find the custom template as you have not yet uploaded.
  • To upload the solution, click on “Solution Gallery”

Upload Custom Template

  • Click on Upload Solution in the ribbon.
  • Choose the file from Computer and press OK button.


Add Solution

  • In Add a Solution dialog, click on “Activate” to activate the solution.


  • If you go to the new site, you can see the custom tab with our template.
  • Select the template and Set up groups for the site.

Create from Custom


  • Now, we can see the contents of previous site in our new site as highlighted below.

New site from Template.png