Friday, August 01, 2014

Display page properties in SharePoint page, like “last modified date” & “modified by” using SPServices (& jQuery)

A colleague got the request to add the latest modification date & modifier name to a SharePoint page.
This is convenient for users visiting the page, to see when the page is changed and by who.

I didn’t have much time (as usual) for this assignment and different options ran through my mind. At first I played a little bit with a custom page-layout where I would stuck the Modify data & modifier properties into the aspx; I quickly ran into all kinds of trouble using this option (publishing features must be on, the test O365 SharePoint site began throwing errors at me, (even after activating built-in page layouts), this was out of scope for my assignment so I quickly went out looking for other technology that could help me out!

The SharePoint 2013 Client Side Object Model (CSOM) is one of those technology options. Another option is jQuery, SPServices is such a jQuery library. But how do you implement this? Lots of examples but little of them are in context / complete and therefore not very useable for beginners. This is one of the reasons I wanted to blog about my solution, to create a full working example that can get you started. Visit this blog on when to use the CSOM / when to use the jQuery libraries.
SPServices is an initiative from Marc. D. Anderson. Visit his site for loads of resources !

 I started my journey with the SPServices “framework” which you can find here:
This codeplex project is full of usable resources and is providing most of the content of my final script. To start off you will need to reference 2 links; one to include the JQuery classes and the other one to include the SPServices classes. SPServices provides you with easy to use code that in the back talks to the SharePoint object model. This way you can create effective functions that are not available in SharePoint 2013 out of the box. For example: (all examples from the documentation part of the codeplex project with detailed info available!)

Form Enhancements/Assistance
SPCascadeDropdowns; It allows you to easily set up cascading dropdowns on a list form. (What we mean by cascading dropdowns is the situation where the available options for one column depend on the value you select in another column.) This is not possible with SharePoint OOB components!

SPDisplayRelatedInfo; This function lets you display related information on forms when an option in a dropdown is chosen.

SPLookupAddNew; This function allows you to provide a link in forms for Lookup columns so that the user can add new values to the Lookup list easily.

SPRedirectWithID; This function allows you to redirect to a another page from a new item form with the new item's ID. This allows chaining of forms from item creation onward.

SPSetMultiSelectSizes; Sets the size of the boxes in a multi-select picker based on the values they contain.

SPArrangeChoices; Rearranges radio buttons or checkboxes in a form from vertical to horizontal display to save page real estate.

SPAutocomplete; The SPAutocomplete lets you provide values for a Single line of text column from values in a SharePoint list. The function is highly configurable and can enhance the user experience with forms.

SPUpdateMultipleListItems; SPUpdateMultipleListItems allows you to update multiple items in a list based upon some common characteristic or metadata criteria.

SPFilterDropdown; The SPFilterDropdown function allows you to filter the values available in a Lookup column using CAML against the Lookup column's source list.

SPComplexToSimpleDropdown; Converts a "complex" dropdown (which SharePoint displays if there are 20+ options) to a "simple" dropdown (select).

SPFindPeoplePicker; The SPFindPeoplePicker function helps you find and set People Picker column values.

SPFindMMSPicker; The SPFindMMSPicker function helps you find an MMS Picker's values.

SPGetCurrentSite; This utility function, which is also publicly available, simply returns the current site's URL. It mirrors the functionality of the WebUrlFromPageUrl operation.

SPGetCurrentUser; This function returns information about the current user. It is based on an insightful trick from Einar Otto Stangvik.

SPGetLastItemId; Function to return the ID of the last item created on a list by a specific user. Useful for maintaining parent/child relationships.

SPGetDisplayFromStatic; This function returns the DisplayName for a column based on the StaticName.          

SPGetStaticFromDisplay; This function returns the StaticName for a column based on the DisplayName.

SPScriptAudit; The SPScriptAudit function allows you to run an auditing report showing where scripting is in use in a site.

SPGetQueryString; The SPGetQueryString function returns an array containing the Query String parameters and their values.

SPListNameFromUrl; Returns the current list's GUID *if* called in the context of a list, meaning that the URL is within the list, like /DocLib or /Lists/ListName.

SPFilterNode; Can be used to find namespaced elements in returned XML, such as rs:data or z:row from GetListItems.   

SPXmlToJson; SPXslToJson is a function to convert XML data into JSON for client-side processing.

SPConvertDateToISO; Convert a JavaScript date to the ISO 8601 format required by SharePoint to update list items.

SPGetListItemsJson; SPGetListItemsJson combines several SPServices capabilities into one powerful function. By calling GetListItemChangesSinceToken, parsing the list schema, and passing the resulting mapping and data to SPXmlToJson automagically, we have a one-stop shop for retrieving SharePoint list data in JSON format. No manual mapping required!

SPDropdownCtl; The function finds a dropdown in a form based on the name of the column (either the DisplayName or the StaticName) and returns an object you can use in your own functions.

The solution:
First of all creating a script; Then adding the script to a page where you want to display the page’s properties. This way, users can still edit the page content without messing with the script code. The code can be inserted into a script editor webpart or in a content editor webpart, depending on your situation you can also upload the script to a resource library and link the content editor webpart to your script. This way users won’t see the script at all, you can modify the script on 1 location and without the need to edit your page.

The script:

The script I put together looks like this; I’ve added some comments in there to explain what’s going on:
<!-- Reference jQuery on the Google CDN (content delivery network), if you want to make an external link to the (for this script correct) jquery library -->
<script type="text/javascript" src="//"></script>
<!-- Alternatively Reference jQuery on the locale Site Assets , uncomment to activate and comment out the above one to deactivate the CDN method-->
<!--<script type="text/javascript" src="https://<yoursite>"></script>-->
<!-- Reference SPServices on cdnjs (Cloudflare)  if you want to make an external link to the (for this script correct) SPServices library -->
<script type="text/javascript" src="//"></script>
<!-- Alternatively Reference jQuery on the locale Site Assets, uncomment to activate and comment out the above one to deactivate the CDN method -->
<!--<script type="text/javascript" src=""></script>-->
<script language="javascript" type="text/javascript">
<!—A little helper to format the date like you would like it, in this case.. Dutch formatting-->
function SPConvertDate(t)
     var d = new Date(t.substr(0,4), parseInt(t.substr(5,2)) - 1, t.substr(8,2), t.substr(11,2), t.substr(14,2), t.substr(17,2));

     var month = ((d.getMonth()+1) < 10) ? "0" + (d.getMonth()+1) : (d.getMonth()+1);
     var date = (d.getDate() < 10) ? "0" + d.getDate() : d.getDate();
     var minutes = (d.getMinutes() < 10) ? "0" + d.getMinutes() : d.getMinutes();
     var amPm = (d.getHours() < 12) ? "am" : "pm";

     return date + "-" + month + "-" + d.getFullYear() + " " + d.getHours() + ":" + minutes+amPm;
<!—here starts the main programà
$(document).ready(function() {
<!—here you can populate the only 2 parameters; the list-name where the page resides and the page ID of the page you want to show the modified date for; I’m using the pageID to prevent disfunction of the script if someone decides to rename a page ..à

var ListName = "2AAC79F6-98B3-45D4-BD77-EDE404B99C39"
var PageID = "3"

<!—then we define our query that will filter all pages in the list to the one we would like to show. Of course you can modify this query and even show multiple pages information. Via these methods we can access all properties / metadata from a SharePoint page, you just need to know the column names and ways to extract it; another nice thing is that it’s a query, which is familiar to a lot of people!

var CamlQuery = "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Number'>" + PageID + "</Value></Eq></Where></Query>";
<!—Then we define which fields (metadata) we would like to extract and show in our page à
var CamlViewFields = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='Author' /></ViewFields>";
<!—now let’s build and execute our query à
    operation: "GetListItems",
    async: false,
    listName: ListName,
    CAMLViewFields: CamlViewFields,
    CAMLQuery: CamlQuery,
    completefunc: function (xData, Status) {

<!—we’re getting a XML back, let’s get our data out of it and fill some variables with the dataà
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
                var Author = new String($(this).attr("ows_Author"));
                Author = Author.split(";#")[1]
                ModifiedDate = $(this).attr("ows_Modified")

<!—making a liHtml variable which in this specific case I turned into a DIV type later, if you want to display more than one pages’ info, you might want to construct an <li>info</li> kind of html
        var liHtml = "Latest update:&nbsp;" + SPConvertDate(ModifiedDate) + "&nbsp;<br>By:&nbsp;" + Author;
<!—and then display your html à
<h1>Page properties:</h1><br/>
<div id="tasksUL"/>

Ok, we’ve got our script.. Now what? Let’s insert it into a web part and see if it works, the end result:

First, we add a script editor or content editor web part to the page;

 Then add the script into it and save it.

Now we can add other content to the page, when the page is saved the info updates automatically!

You can download the script here:

Wednesday, June 25, 2014

TomTom Issue: Connecting to your computer

A bit of a different post, not about SharePoint this time !

I finally resolved an issue with my TomTom today and thought to share this solution with the world :)

My problem was that after a few seconds of starting my navigation in the car, it would display this message:
"Connecting to your computer".

And this message would stay there forever, until you click OK. Because this message would pop up after 15 / 40 seconds, you almost always are driving. So it was very annoying to pay attention to it and press the OK button while driving.

I logged a ticket with TomTom support, they suggested to reload the software on it, this didn't help. I decided to do some serious debugging and after a few moments I found the answer... The error / message was caused by plugging the TomTom into the Peugeot's own USB connection and not using the TomTom provided 12v ->USB connector:

Because I was using the Peugeot powered USB, TomTom thought it was connected to a computer; showing me the annoying message ! Using the normal 12v connection and TomTom USB converter that comes with the navigation device, no message; problemo solved :)


Wednesday, June 18, 2014

No Managed Metadata columns in Power Query

When using Power Query to retrieve data from an O365 SharePoint list, you run into trouble when that list data contains columns connected to the Term Store (managed metadata).

These columns are not retrieved by Power Query, not using the "SharePoint List" connection and also not using the "oData" connection.

So, the managed metadata column "organisatie eenheid" is in this list:

But not in the Power Query result sets (oData):
Or in the "From SharePoint List" resultset:

I've notified Microsoft of this issue here, let's wait and see if there are any workarounds or fixes !
Will keep you posted...

Update 24 June 2014:

Ben Martens from Microsoft searched around and asked the product team about this issue and unfortunately the conclusion is that right now, it's not possible to get MMD data extracted from SharePoint.
Both the ListData.mvc as OData connections won't bring back these columns from your SharePoint lists.
Sorry for you ...
Let's hope these services get modified by the SharePoint team and start exposing this data, there are many scenarios where people really want to work with the Managed Metadata ! (why use it if it's not supported !?!). Thanks Ben for this update !

Thursday, June 12, 2014

Use English as your #0365 (sub) site's default language or else...(use oData)

Hi there,
a quick post on something I discovered yesterday during the creation of a proof of concept a #0365 site that provides it's list data to an Excel Power Query & Power Pivot report. (Which will be published on a Power BI site, that will be described on it's own blog post :) )

I was having trouble getting the data from the O365 SharePoint list into Excel. After making the connection with Power Query:

The "navigator" which should show me all available lists on a given SharePoint URL was empty:
I tried different security settings, checking the data connections, removing all data connections and adding them again, blaming the latest Power BI update, googling, nothing worked. And the strange thing was that I'd seen it work in an earlier POC I did...
Eventually I tried another site URL (on the same O365 web application), that one worked !
Now I had to find out what the difference was between the working and none working sites. Soon I realized it was the regional and language settings. I made the regions the same, no difference. But when I created a new site and gave English as the default language upon creation, Power BI could instantly connect to it. 

I created a new site to test because changing the default language in O365 SharePoint is not as easy, I think I'll need Power Shell to do this but of course I need to focus on my original POC first :) 

I also found a post from Kasper de Jonge, a senior program manager at the Microsoft SQL Server BI division and a good friend; He said he'll take this issue up with the PQ team and just posted an update on this:
"Update: Yes it is a known issue which they are working on with SP, the workaround is to connect to the sharepoint API using OData, rather than using the From SharePoint option in PQ" 

Thanks Kasper !

Monday, May 19, 2014

SharePoint performance presentation

I've uploaded the presentation I gave at the Information Worker Community evening Microsoft Johannesburg in October 2013: (download the PPT for fun animations)

Thursday, January 30, 2014

#PowerPivot refresh schedule disappears after creating new version of #excel file on #SP2013 #help

The challenge is as follows:
When you create a new version of your PowerPivot excel file and you load it to SharePoint 2013, the Data Refresh Schedule you had configured is now gone.. As soon as there is a new version (or a new file with versions disabled) the schedule is gone and you have to re-create it.
This problem only seems to happen on SharePoint 2013, SharePoint 2010 keeps a reference to the selected schedule after updating the excel document.

The problem visual:
A Power Pivot Excel document with a Schedule enabled:

Schedule is enabled:

Make a new version & save document:

Overwrite existing file:

Check PowerPivot Data Refresh:

Gone ...

No fix yet, will update this post when I find it !
(Suggestions are welcome ..) 

4 Feb Update:
I found the Microsoft KB that describes this issue (Sql Bug 1377755)  in the CU6 update for SQL Server 2012:

I installed the CU8 but so far no luck..the issue is still there!

5 Feb Update:
Posted question on the Microsoft PowerPivot for SharePoint forum

15 April Update:
It seems that "psiodmakFuture-Processing" found the cause of this bug:
We have this problem as well.

I think I found a possible cause. Try the following script:


for a working file it should spew out an escaped XML. It's your schedule that was serialized and cached here probably to speed things up. When you save the document with Excel, this value is trimmed to 255 characters, rendering it broken, so the UI (and the timerjob) thinks that the schedule is missing. This might as well be a bug in Excel.

Now we just need a solution from Microsoft :)

23 April Update:
CU 9 for SQL Sever 2012 SP1 is now downloadable, let's give it a try and see if this resolves the outstanding issue...

Wednesday, January 22, 2014

Quick tool: Dev / Demo / Test environment in the cloud

When I got the question to create a proof-of-concept for exporting the number of emails in custom Microsoft-Outlook (exchange) folders, my thoughts went straight back to the period where I used a cloud solution for my team of developers because IT couldn't provide us with a Team Foundation Server.

On you can create an environment with one or more VM's which you then can access via Remote Desktop (or your browser). The nice thing of this cloud solution is that they have several prepared VM's which you can "spin up" in a couple of minutes !
One of these VM's is a Microsoft Exchange 2010 SP3 environment (Exchange installed AND CONFIGURED, AD installed & configured etc.) but they have more ! :
(As per 22 January 2014)

CentOS 5 W/ MySQL
CentOS 5.4 Server
CentOS 5.8 Server
CentOS 6 Server
CentOS 6.3 Server
Dynamics CRM Server 2011
Exchange Server 2010 SP1
Exchange Server 2010 SP3
Exchange Server 2013 RTM
Microsoft Forefront TMG 2010
PostgreSQL 9.1
Ruby On Rails
SharePoint 2007
SharePoint 2007 Additional Server Bits Only
SharePoint 2010 Additional Server Bits Only
SharePoint 2010 Enterprise - No Configuration
SharePoint 2010 Enterprise SP1
SharePoint 2010 Enterprise SP1 W/ Duet
SharePoint 2010 Enterprise SP1 W/ SQL 2012 and BI Stack
SharePoint 2010 SP1 (Information Worker)
SharePoint 2010 SP1 Enterprise W/ FAST Search and SQL Server 2012
SharePoint 2010 SP1 Enterprise W/ SQL Server 2012
SharePoint 2010 SP1 Standard W/ SQL 2012
SharePoint 2010 SP1 W/ FAST Search Server
SharePoint 2010 SP1 W/ PowerPivot
SharePoint 2010 SP1 W/ Project Server
SharePoint 2010 Standard - No Configuration
SharePoint 2010 Standard SP1
SharePoint 2010 W/ Fast for SharePoint Additional Server Bits Only
SharePoint 2013 Additional Server Bits Only
SharePoint 2013 with October 2013 Update
SharePoint Foundation 2010 SP1
SharePoint Foundation 2010 SP1 W/ Dev Tools
SharePoint Server 2013
SQL Server 2008 R2 SP1 Standard
SQL Server 2012 Standard
SQL Server 2014 CTP2
Team Foundation Server 2010
Team Foundation Server 2012
Team Foundation Server 2013
Ubuntu 10.04 Desktop
Ubuntu 10.04 Server
Ubuntu 12.04.3 Desktop
Ubuntu 12.04.3 Server LTS 64 bit
Ubuntu 12.04.3 Server LTS 64 bit /W Docker 0.7
Ubuntu 13.10 Server 64 bit
Windows 2003 R2 W/ Oracle 11g
Windows Server 2003 R2 Enterprise
Windows Server 2008 R2 x64 Enterprise SP1
Windows Server 2008 R2 x64 Enterprise SP1 W/ Active Directory
Windows Server 2008 R2 x64 Enterprise SP1 W/ IIS Server
Windows Server 2008 R2 x64 Standard
Windows Server 2008 SP2 x86 Standard
Windows Server 2008 x64 W/ SQL Server 2012 SP1 BI Edition
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Standard W/ Visual Studio 2013
Windows Server 2012 x64 Standard
Windows Server 2012 x64 W/ Active Directory
Windows Server 2012 x64 W/ SQL Server 2012 SP1
Windows Server 2012 x64 W/ SQL Server 2012 SP1 BI Edition
Xubuntu 8

Each of these VM's ready to spin in a couple of minutes.
The nice thing of this solution is that you can actually invite people (developers or customers) to join-in on the environment and start testing or watching what you build.
You get a free and full-functional trial period which makes things even faster. Imagine you suddenly got that invite at the biggest customer ever, you need that PowerPivot demo area for yourself and ready NOW!!
You can do it, with !!

Other than RDP'ing to this environment (You'll get a local-admin account & password), you can also use "Cloud Folders' to FTP from and to the VM. This way you can actually export your code / results from the cloud to your own machine.
SnapShot's are also available, so no more re-doing all that hard work :)

Some caveats:
- In the trial period, your environment will shut-down automatically when you don't use it. When you are using it, you can "extend" the period before it goes down. This makes sure that cloudshare is not using valuable resources for nothing :P When cloudshare terminates your environment you can "spin it up" at any time again, it's right where you left it !
- You are depending on your connection to the cloud.. no internet = no environment
- Make sure you look at all pricing options, depending what you are going to do with the tool