SharePoint REST API results in browsers


Rest api results for SharePoint lists can be seen in Google Chrome in xml format.

What if you don’t have Chrome and still interested in viewing results ?

To view results in IE:

Feed in IE

  • Go to Settings and click on Internet Options

IE options

  • Click on Settings under Feeds and Web Slices in Content tab and uncheck the turn on feed reading view box.

Settings

  • You will see the results in Xml format as shown below:

xml

To view results in Mozilla Firefox:

  • Open Firefox and type the rest url. You will see the same feeds page as IE

Firefox feed

Result in firefox

Advantages of viewing results in browsers:

  • You can immediately verify the rest operation results
  • You can find the column name to use it in rest operation

Exclude AllItems.aspx from Search Results in Content Search Web Part


When I was working with Content Search web part in one of my SharePoint sites, I experienced a problem where the search result for list items is displayed along with list views.
In this article, I will show you how to remove the list views in CSWP search results.
For better understanding, consider a list, EMPINFO from which you want to show the items in CSWP. To show specific list items, you might have carried out the below steps

  • Edit the CSWP and click on Change query

  • Choose Documents (System) in Select a query
  •  Choose Specify a URL and enter the list URL for Restrict by app

  • Switch to Advanced mode and remove the highlighted file extension query text.

  • You can see AllItems.aspx view along with the list items in search results.

To remove the view, you can follow any one of the below step:

  • Select “Only return items “ in keyword filter and click on “Add Keyword Filter”
  •  Just append [contentclass:STS_ListItem] after Path in Query text

Upgrading SharePoint App


Whenever you make changes and redeploy the app, data that were created previously by the app will be lost. To make changes in the app without losing the data, you need to upgrade the app.

Let us see how to upgrade the app:

  • Open AppManifest.xml and increment the version number by 1 whenever a change is made
  • In my case, version is 1.0.0.2. Hence I am changing it to 1.0.0.3 and publishing the app as shown below:

Publishing app

  • Click on Finish in Publish summary.

Publish summary

  • Explorer window opens as depicted below:

Published app version

  • Copy the location of the file and go to the developer site
  • You can see the list of apps in App Testing. Select the app and click on upgrade an app

Upgrade

  • Click on Upload and browse the file from copied location

Upgrade - Upload

  • Click on Upgrade

Upgrad - Complete

  • You can see the upgrade progress as shown below:

Upgradeinprogress

  • Once upgraded, the version is changed to 1.0.0.3 and if you check for the previous data, it actually exists.

That’s it!

Reference: http://www.c-sharpcorner.com/article/upgrade-sharepoint-hosted-add-insapps-in-sharepoint-2013/

 

PART 2 : SharePoint-Hosted App


Have you read PART 1? Cool. Continue reading how to create an app in host web.

Let us see the same create operation for better understanding. We need two URLS for this:

  • HostURL
  • AppWebURL

To retrieve the URLS, QueryStringParameter() is generally used. But this does not work in all the cases especially when we move from one page to another in app web. So, let’s use the below one:

  • appWebUrl = window.location.protocol+”//”+window.location.host+_spPageContextInfo.webServerRelativeUrl;
  • hostUrl = _spPageContextInfo.siteAbsoluteUrl;

To change the context site from app web to other sites, AppContextSite() is used. To change the current context(i.e, app web) to host web, pass it as
var appctx = new SP.AppContextSite(ctx, hostUrl);

Let’s combine all these with previous code. Here you go:

var appWebUrl;
var hostUrl;
$(document).ready(function ()
 {
appWebUrl = window.location.protocol+"//"+window.location.host+_spPageContextInfo.webServerRelativeUrl;
hostUrl = _spPageContextInfo.siteAbsoluteUrl;
 $("#create").on('click', function ()
 {
                createList();
  });
});
//create List
function createList()
 {
    var listTitle = $("#listName").val();
    var ctx = new SP.ClientContext(appWebUrl);
    var appctx = new SP.AppContextSite(ctx, hostUrl);
    var oweb = appctx.get_web();
    var listCreationInfo = new SP.ListCreationInformation();
    listCreationInfo.set_title(listTitle);
    listCreationInfo.set_templateType(SP.ListTemplateType.genericList);
    oweb.get_lists().add(listCreationInfo);
    ctx.executeQueryAsync(onQuerySucceeded, onQueryFailed);
}
function onQuerySucceeded() 
{
    alert("List Created");
}
function onQueryFailed(sender, args) {
    alert("Operation Failed" +"\n"+ args.get_message());
}

After deploying the app, when you create the list, the below error is shown

Access Denied for Host Web

  • This is because to access or create resource in host web, we need to give FULL CONTROL permission for web and list. To give permission, open AppManifest.xml, move to permission tab and give the permission as shown below:

Fullconrol Permission

  • Now when you deploy it, you will get the below page:

Trust the app

  • Click on trust and create a list using the app. List is created in the host web and can be seen in the site contents page.

If you have noted that when you deployed the app again after making some changes, the data created previously were lost. Continue reading the next article, Upgrading SharePoint app to learn how to deploy the app without losing the data.

PART 1: SharePoint-Hosted App


Apps for SharePoint are also called as SharePoint Add-ins. In SharePoint 2013, everything is considered as an app. Lists, libraries are all called as apps. Apps can be hosted

  • In SharePoint farm (SharePoint-hosted)
  • In separate server(Provider-hosted)
  • In cloud(Auto-hosted)

SharePoint-Hosted App:

It includes only SharePoint components. SharePoint Site where the app is installed is called Host web. All the resources such as list, libraries are in isolated sub site called App web.

Let us create a SharePoint-hosted app to create a list in SharePoint On-Premise environment.

  • Run Visual Studio as administrator and click on File->New->Project

New Project

  • Select Apps for SharePoint 2013 and give a name(say, createListinHost) and hit ok button

Host

  • Next, Specify the site url ( say,Developer site), select the type as SharePoint Hosted and click on Finish

SharePoint Hosted app

  • App is created and default aspx page is shown.

Default aspx

  • Replace the highlighted code with the below one to create a textbox and button for create operation.

<label for=”listName”>List Name to be created</label>
<input type=”text” id=”listName” Placeholder=”Enter the listname”/>
<input type=”button” id=”create” value=”Create”/>

  • Branding(Optional):
    • You can change the title of the page in
      <asp:Content ContentPlaceHolderID=”PlaceHolderPageTitleInTitleArea” runat=”server”> List Creation </asp:Content
    • You change the icon for app in AppManifest.xml. Open AppManifest and change the image for Icon. Image should be of 96*96 pixel

App Icon

  • To insert create functionality in this app, expand Scripts, open App.js and insert the below code:
$(document).ready(function () 
{ 
    $("#create").on('click', function () {
                createList();
    });
});
//create List
function createList()
{
    var listTitle = $("#listName").val();
    var ctx = SP.ClientContext.get_current();
    var oweb = ctx.get_web();
    var listCreationInfo = new SP.ListCreationInformation();
    listCreationInfo.set_title(listTitle);
    listCreationInfo.set_templateType(SP.ListTemplateType.genericList);
    oweb.get_lists().add(listCreationInfo);
    ctx.executeQueryAsync(onQuerySucceeded, onQueryFailed);
}
function onQuerySucceeded() {
    alert("List Created");
}
function onQueryFailed(sender, args) {
    alert("Operation Failed" +"\n"+ args.get_message());
}
  • Press F5 or click on Solution->Deploy to deploy the code. Default aspx page will be opened in browser. If not go to the site->Site Contents and click on the app, “createListinHost”

App in Site Contents

  • Create a list say, Sample

 

Below page is shown where you can see that sample list created under “createListinHost”

Sample list settings

Site lists and libraries

Now, question arises. Should all the resources reside in app web only? What If I need to create a resource or access a resource that is in host web? Can I do it? Yes, you can create and access resources in host web. Continue Reading in PART 2

Comparing Dates in SharePoint


SCENARIO:

Let us assume that we need to hide some fields in SharePoint form based on other field value such as text box, what will we do? We will just check the value of the field on page load in edit form as shown below:

<script src="../Scripts/jquery.min.js"></script>
<script type="text/javascript"> 
$(document).ready(function()
{
if($("input[title='Title']").val()=="Text1") 
{
//hide fields
} 
});
</script>

This is pretty much simple. But assume that we need to hide some fields based on date and time value where Date and Time field is split into three controls:

  • Text box for Date along with Date Picker
  • Dropdown box to select Hours
  • Dropdown box to select Minutes

On seeing the three controls, we may think it is very complicated. Certainly not. We are going to achieve this by retrieving the id of date textbox and then append the id with the words, “Hours” and “Minutes” to get the value of Hours and Minutes dropdown. So, this will be quite interesting. Let’s start.

Retrieving value from Date and Time field:

The below code shows how to retrieve value from date and time field in edit form on page load. If we need to check the value of date and time field in new form on save button for some other requirement, use PreSaveAction function instead of document.ready function

<script src="../Scripts/jquery.min.js"></script>
<script type="text/javascript"> 
$(document).ready(function()
 {
 var startDate=getDateField("Start Date");//Display name of Date and Time field
 alert(startDate);
 });
 function getDateField(field)
 {
 var date = $(":input[title='" +field+ "']").val(); //retrieves date from date text box which is in mm/dd/yyyy format
 var d = new Date(date);
 var month =d.getMonth() + 1; //adding 1 as January starts at 0
 var day =d.getDate();
 var year = d.getFullYear();
//appending 0 for single digit date ad month
 if (month.length < 2) month = '0' + month; 
if (day.length < 2) day = '0' + day;
var dateID = $(":input[title='"+field+"']").attr("id");
var dateHours = $(":input[id='"+ dateID + "Hours" +"']").val();
var Meri=dateHours.split(' ')[1];
dateHours = dateHours.split(' ')[0];
//converting it into 24 hour format
 if(Meri == "PM" && dateHours<12)
    dateHours = parseInt(dateHours)+12;
 else if(Meri == "AM" && dateHours==12)
   dateHours = parseInt(dateHours)-12;
var dateMinutes=$(":input[id='"+dateID+"Minutes"+"']").val();
 var requiredDate=new Date(year,month-1,day,dateHours,dateMinutes,0);
 return requiredDate;
 }
</script>

To retrieve date and time in display form, use the below code:

<script src="../Scripts/jquery.min.js"></script>
<script type="text/javascript"> 
$(document).ready(function()
 {
var start = $.trim($('h3:contains("Start Date")').closest('td').next('td').text());
alert(start);
});
</script>

Comparing Current Date and time with Date in DateTime field:

After retrieving the value from Date and Time field, let’s say we need to check the current date and time and hide if current date is greater than Start Date field.

<script src="../Scripts/jquery.min.js"></script>
<script type="text/javascript"> 
$(document).ready(function()
 {
 var startDate=getDateField("Start Date");//Display name of Date and Time field
 var current=new Date();
 if(current>=startDate)
 {
 //hide fields
 }
 });
 function getDateField(field)
 {
var date = $(":input[title='" +field+ "']").val(); //retrieves date from date text box which is in mm/dd/yyyy format
var d = new Date(date);
var month =d.getMonth() + 1; //adding 1 as January starts at 0
var day =d.getDate();
var year = d.getFullYear();
//appending 0 for single digit date ad month
 if (month.length < 2) month = '0' + month; 
if (day.length < 2) day = '0' + day;
var dateID = $(":input[title='"+field+"']").attr("id");
var dateHours = $(":input[id='"+ dateID + "Hours" +"']").val();
var Meri=dateHours.split(' ')[1];
dateHours = dateHours.split(' ')[0];
//converting it into 24 hour format
 if(Meri == "PM" && dateHours<12)
    dateHours = parseInt(dateHours)+12;
 else if(Meri == "AM" && dateHours==12)
   dateHours = parseInt(dateHours)-12;
var dateMinutes=$(":input[id='"+dateID+"Minutes"+"']").val();
var requiredDate=new Date(year,month-1,day,dateHours,dateMinutes,0);
 return requiredDate;
 }
</script>

To compare two SharePoint date fields, we need to call getDateField function for both the dates as:

<script src="../Scripts/jquery.min.js"></script>
<script type="text/javascript"> 
$(document).ready(function()
 {
var startDate=getDateField("Start Date");//Display name of Date and Time field
var EndDate=getDateField("End Date");
 if(EndDate<=startDate)
 {
 alert("End Date should be greater than Start Date)
 }
 });
</script>

Hiding fields before three hours of start date in SharePoint

To hide the fields from three hours prior to start Date  time, lets subtract three hours from Start Date time and then compare with current date and time as shown below:

<script src="../Scripts/jquery.min.js"></script>
<script type="text/javascript"> 
$(document).ready(function()
 {
 var startDate=getDateField("Start Date");//Display name of Date and Time field
 var current=new Date();
 var subbed = new Date(startDate - 3*60*60*1000); // subtract 3 hours
 if(current>=subbed)
 {
 //hide fields
 }
});
function getDateField(field)
 {
var date = $(":input[title='" +field+ "']").val(); //retrieves date from date text box which is in mm/dd/yyyy format
var d = new Date(date);
var month =d.getMonth() + 1; //adding 1 as January starts at 0
var day =d.getDate();
var year = d.getFullYear();
//appending 0 for single digit date ad month
 if (month.length < 2) month = '0' + month; 
if (day.length < 2) day = '0' + day;
var dateID = $(":input[title='"+field+"']").attr("id");
var dateHours = $(":input[id='"+ dateID + "Hours" +"']").val();
var Meri=dateHours.split(' ')[1];
dateHours = dateHours.split(' ')[0];
//converting it into 24 hour format
 if(Meri == "PM" && dateHours<12)
    dateHours = parseInt(dateHours)+12;
 else if(Meri == "AM" && dateHours==12)
   dateHours = parseInt(dateHours)-12;
var dateMinutes=$(":input[id='"+dateID+"Minutes"+"']").val();
var requiredDate=new Date(year,month-1,day,dateHours,dateMinutes,0);
 return requiredDate;
 }
</script>

Please do not forget to like or rate the article 😛

 

 

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
import-dialog

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

create-table

  • 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

design-and-export

  • 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

step-1

  • “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.

step-2

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

exported-successfully

excel-table
Excel Table

sharepoint-list
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

edit-in-splist

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

autoupdated-excel

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

unlink

Advantages:

  • 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

advantage

  • 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:


<script>

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

</script>

Set value to the People Picker:

To set static value in  the People Picker:


<script>

$(document).ready(function()
{
$().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "User",
valueToSet: "kamal",
checkNames: true
});
});

</script>

To set logged in user name to the People Picker:


<script>

$(document).ready(function()
{
var user= $().SPServices.SPGetCurrentUser();
$().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "User",
valueToSet: user,
checkNames: true
});
});

</script>

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
});
 alert(currentUserName);
 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="Browse"]').css('display','none');
       $('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="Browse"]').css('display','none');
      $('img[title="Check Names"]').css('display','none');    
        
      }
 });
</script>

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

<script language="javascript" type="text/javascript">
$(document).ready(function() 
{
    $().SPServices({
      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
    }
        else
        {
          alert("You do not belong to admin group");
  //your code for non-admin users
        }
      }
   }); 
}); 
</script>

SPServices to perform read operation:

<script>
$(function(){
getListItems();
});
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"));
alert(eTitle);
});
}
});
}
</script>

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