Large Lists, BCS, Excel REST, JSOM, CSVs and Office 365 OH MY!

An interesting challenge came across my desk last week:

"How can we import a CSV into Office 365 and use that data to tag other items within the site.  The CSV currently has ~7600 rows and is expected to grow.  We'll also need to re-import the CSV on an ad-hoc basis when the data changes."

The last part of that was the real issue.  There was simply no easy way of doing that.  After trying a few things, falling flat on my face, I'm prepared to share my exploration into the different options.

• BCS
• Custom List - JSOM
• Excel REST API
• CSV

Each have their own merits and pitfalls.  This post will examine each and try to shed some light on the pitfalls I've found using each of these.

BCS

Wiring up an ECT on Office 365 can be a little finicky. I initially had some issues due to 2 BDC Models that were created for the same ECT.  After calling in the eagle-eyed BCS guru, +Fabian Williams, I got squared away.

Immediately after that, I could tell that I was not going to be able to use BCS for what I needed.  In Office 365, there is a hard limit of 2000 items that can be retrieved.  Ironically, the error message that is displayed is not a supported cmdlet for Office 365.

Office 365 BCS Throttle Error

Adding insult to injury, I decided to run a simple test using JSOM.  I wanted to clarify if JSOM would provide me PagingInfo with a BCS List.  Using the code below, you'd expect line 68 to produce a value instead of nothing at all.

	var $1_10_2 = jQuery.noConflict();
	(function( $, undefined ) {
	var $formTable = null,
	results = [],
	queryPosition = null
	;
	// Why wait for the DOM to be ready?
	ExecuteOrDelayUntilScriptLoaded($.noop, "sp.js");
	$(document).ready(function() {
	$formTable = $(".ms-formtable").eq(0);
	ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");
	});
	function retrieveListItems() {
	var cachedItems = localStorage.getItem("pl-listItems");
	if(cachedItems) {
	results = JSON.parse(cachedItems);
	renderTypeAhead(results);
	} else {
	queryList();
	}
	}
	function queryList() {
	var query = "<View>" +
	"<Query><Neq><FieldRef Name='VendorID' /><Value Type='Text'>0</Value></Neq></Query>" +
	"<OrderBy><FieldRef Name='VendorID' Ascending='True' /></OrderBy>" +
	"<RowLimit>5</RowLimit>" +
	"<ViewFields><FieldRef Name='BdcIdentity' /><FieldRef Name='Name' /><FieldRef Name='VendorID' /><FieldRef Name='City' /></ViewFields>" +
	"</View>"
	;
	var clientContext = new SP.ClientContext.get_current();
	var oList = clientContext.get_web().get_lists().getByTitle("BCS Vendor List");
	var camlQuery = new SP.CamlQuery();
	camlQuery.set_viewXml(query);
	camlQuery.set_listItemCollectionPosition(queryPosition);
	var collListItem = oList.getItems(camlQuery);
	clientContext.load(collListItem);
	clientContext.executeQueryAsync(
	onQuerySucceeded.bind(collListItem),
	onQueryFailed
	);
	}
	function onQuerySucceeded() {
	var items = this.get_data();
	var item = null;
	while (item = items.shift()) {
	item = item.get_fieldValues();
	results.push({
	"name": item.Name,
	"vendorId": item.VendorID,
	"city": item.City,
	"bdcId": item.BdcIdentity,
	"value": item.Name,
	"tokens": [ item.Name, item.VendorID ]
	});
	}
	// console.log(results.length);
	queryPosition = this.get_listItemCollectionPosition() && this.get_listItemCollectionPosition().get_pagingInfo();
	if(queryPosition) {
	queryList();
	} else {
	localStorage.setItem("pl-listItems", JSON.stringify(results));
	renderTypeAhead(results);
	}
	}
	function onQueryFailed(sender, args) {
	alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
	}
	}( $1_10_2 ));

view raw JSOM_BCS hosted with ❤ by GitHub

Since BCS will not work and due to the limitation of the API, I had to look for another solution.

Custom List

Using a custom list initially worked great.  I'm able to use JSOM, query the list for 5000 items per trip to the server, AND get PagingInfo.  Using the code below works great for this scenario.

	var $1_10_2 = jQuery.noConflict();
	(function( $, undefined ) {
	var $formTable = null,
	results = [],
	queryPosition = null
	;
	// Why wait for the DOM to be ready to download sp.js?
	ExecuteOrDelayUntilScriptLoaded($.noop, "sp.js");
	$(document).ready(function() {
	$formTable = $(".ms-formtable").eq(0);
	ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");
	});
	function retrieveListItems() {
	var cachedItems = localstorage.getItem("pl-listItems");
	if(cachedItems) {
	results = JSON.parse(cachedItems);
	renderTypeAhead(results);
	} else {
	queryList();
	}
	}
	function queryList() {
	var query = "<View>" +
	"<Query><Neq><FieldRef Name='VendorID' /><Value Type='Text'>0</Value></Neq></Query>" +
	"<OrderBy><FieldRef Name='VendorID' Ascending='True' /></OrderBy>" +
	"<RowLimit>5000</RowLimit>" +
	"<ViewFields><FieldRef Name='BdcIdentity' /><FieldRef Name='Name' /><FieldRef Name='VendorID' /><FieldRef Name='City' /></ViewFields>" +
	"</View>"
	;
	var clientContext = new SP.ClientContext.get_current();
	var oList = clientContext.get_web().get_lists().getByTitle("Vendor List");
	var camlQuery = new SP.CamlQuery();
	camlQuery.set_viewXml(query);
	camlQuery.set_listItemCollectionPosition(queryPosition);
	var collListItem = oList.getItems(camlQuery);
	clientContext.load(collListItem);
	clientContext.executeQueryAsync(
	onQuerySucceeded.bind(collListItem),
	onQueryFailed
	);
	}
	function onQuerySucceeded() {
	var items = this.get_data();
	var item = null;
	while (item = items.shift()) {
	item = item.get_fieldValues();
	results.push({
	"name": item.Name,
	"vendorId": item.VendorID,
	"city": item.City,
	"bdcId": item.BdcIdentity,
	"value": item.Name,
	"tokens": [ item.Name, item.VendorID ]
	});
	}
	// console.log(results.length);
	queryPosition = this.get_listItemCollectionPosition() && this.get_listItemCollectionPosition().get_pagingInfo();
	if(queryPosition) {
	queryList();
	} else {
	localStorage.setItem("pl-listItems", JSON.stringify(results));
	renderTypeAhead(results);
	}
	}
	function onQueryFailed(sender, args) {
	alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
	}
	}( $1_10_2 ));

view raw JSOM_Paging hosted with ❤ by GitHub

Importing the Excel into Office 365 is relatively straightforward and will work for most needs.  The file I used had about ~7600 rows of data.  After importing the file, I noticed the Server Resource Quota was tapped.

Office 365 Server Utilization

So using this approach has 2 problems.  I will not be able to do a mass import again of my data (the list already exists) and the Server Resource Quota points are exhausted.

Excel REST

This seemed like a cool way of getting around the limitations above, so I dove in to find out if this will work for my needs.  After all, I'm allowed to have a *lot* of rows in Excel and I'll be able to easily update the file, since it's in a document library.  Using the code below, I ran into a showstopper though.

	jQuery.ajax({
	type: "GET",
	url: "/sites/siteName/_vti_bin/ExcelRest.aspx/listName/Vendors.xlsx/OData/Table1?$inlinecount=allpages",
	headers: {
	"Accept": "application/json;odata=verbose",
	"X-RequestDigest": __REQUESTDIGEST.value
	}
	})
	.done(function(data, status) {
	console.dir(data);
	})
	.fail(function(data, status) {
	console.dir(arguments);
	});

view raw Excel_REST hosted with ❤ by GitHub

There is a hard limit in the API set at 500 rows.  That would be painfully slow to get all of the items or even worse; a user may try to use the form control while this is still querying for data.

Excel REST API - 500 row limit

So that leaves us with our raw data that was exported from SQL and given to us to use.

CSV

Updating the CSV will be easy, since it will be stored in a document library.  Now all we need to be able to do is make sense of it.  Using the code below, I'm able to parse the CSV and create an array of objects that I need to pass off to another library.  Also note the use of localStorage.  This is a nice way to cache the data and prevent the retrieval/processing of the data client-side on every page load.  If the CSV is updated, simply clear the browser cache and you'll get the latest and greatest.

	var $1_10_2 = jQuery.noConflict();
	(function( $, undefined ) {
	var results = []
	;
	retrieveListItems();
	function retrieveListItems() {
	var cachedItems = JSON.parse(localStorage.getItem("pl-Vendors"));
	if(!cachedItems) {
	$.ajax({
	url: "/sites/siteName/listName/Vendors.csv",
	dataType: "text"
	})
	.done(parseCSV)
	.fail(errorCSV);
	} else {
	$(function () {
	renderTypeAhead(cachedItems);
	});
	}
	}
	function parseCSV(data, status) {
	// Split on line feeds.
	var items = data.split(/\n/),
	headers,
	headersLength
	;
	// Get rid of empty array on the end.
	items.pop(items.length -1);
	headers = items.shift();
	if(!headers) {
	return;
	}
	headers = headers.split(",");
	headersLength = headers.length;
	// console.dir(headers);
	// console.dir(fields.length);
	var itemsLength = items.length;
	// Loop all of the items.
	for (var i = 0; i < itemsLength; i++) {
	var item = items[i].split(",");
	var output = {};
	// Loop the header row and stuff the output
	// with properties:value. headers[j]: item[j]
	for(var j = 0; j<headersLength; j++) {
	output[headers[j]] = item[j];
	}
	// Used for typeahead.js
	output.value = output.VendorName;
	output.tokens = [output.VendorName, output.VendorID];
	// For each item, push into results array.
	results.push(output);
	}
	localStorage.setItem("pl-Vendors", JSON.stringify(results));
	$(function () {
	renderTypeAhead(results);
	});
	}
	function errorCSV(sender, args) {
	alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
	}
	}($1_10_2));

view raw Parse_CSV hosted with ❤ by GitHub

Conclusion

All approaches have their merits and pitfalls... BCS and PagingInfo, I'm looking at you!  If the ad-hoc mass-import wasn't needed, then using list driven data would have been my choice.  If I used that approach, I would have still used localStorage though.  It makes sense to cache the processed data since it'll not change very much.  Since my solution works client-side, I'll have to take into consideration the amount of time this takes to render.  I'm getting good performance out of the CSV approach, so I'm going to stick with it for the time being.

Fire Workflows with Initiation Parameters using #SPServices

Firing Workflows using Javascript, I've never had to pass in Initiation Parameters.  This post takes a look at how to do just that and provides some code that will allow easy use of Workflows in Javascript.

Setup Workflow

The workflow has to be set for Manual starting, otherwise this will not work.  Also, to pass in parameters to the workflow, you'll need to have Initiation Variable(s) within the workflow. I've only fiddled with Number and Single Line of Text fields, so if you use other column types, feel free to share your experience.

Workflow Initiation Parameters

This workflow is simply logging the variables out to the Workflow History.  Easy peazy...

Workflow Parameters

Looking over the documentation for SPServices and StartWorkflow, I found some examples that were a great starting point.  After fiddling a bit with 1 field, I decided to test this a little more.  I created a column with spaces in the Name field *gasp*.  I only did this to see how to handle this programmatically, so a word to the wise: Friends don't let friends create columns with spaces...

Reading over the examples, if you have multiple parameters, it says you have to change from passing the column name to this weird pattern: 

<Data><Parameter1>" + parameter1 + "</Parameter1><Parameter2>" + parameter2 + "</Parameter2></Data>

I've found this to not work at all for me at all [sad_panda]... Back to the drawing board, I guess. Then an idea came to me.  Since I'm targeting a column with a space in it, I tried what normally happens to spaces in Static Names: _x0020_.  So, I tried this next:

<Data><TextField>Will it blend?</TextField><With_x0020_Spaces>42</With_x0020_Spaces></Data>

However, this didn't work either!  Very curious to find a resolution, I set out to find out why this didn't work...  Using SPD (SharePoint Designer), you are able to view the files generated by the workflow. Opening up the XML file as text, you can clearly see that SPD removed the space in the Static Name.

Workflow wfconfig.xml

Within this file, all of the Initiation Parameters are visible and it's now easy to tell what's exactly going on.

Workflow Parameter Names

For all of this to work while using multiple parameters, you have to use the exact Static Name as defined in the XML.  The workflow parameters below work just fine for me now.

<Data><TextField>Will it blend?</TextField><WithSpaces>42</WithSpaces></Data>

Code to Fire the Workflow

This function will handle the pain of getting a workflow to fire.  All you need to know is the correct URL, the workflow name, and the workflow parameters ( if any ).

*** Update *** I took my original idea and made it more or less a plug-in for SPServices. Add this function to the SPServices source and it'll work without any issues.

	$.fn.SPServices.StartWorkflow = function(options) {
	var opt = $.extend({}, {
	itemUrl: null,
	workflowName: null,
	workflowParameters: "<root />",
	completefunc: null, // Function to call on completion of rendering the change.
	debug: false // If true, show error messages;if false, run silent
	}, options),
	thisFunction = "SPServices.StartWorkflow",
	executeWorkflow = $.Deferred(),
	templates = null,
	getTemplatesForItem = "GetTemplatesForItem"
	;
	function getTemplates() {
	return $().SPServices({
	operation: getTemplatesForItem,
	item: opt.itemUrl
	});
	}
	function startWorkflow(workflowTemplates) {
	var workflowGUID = null,
	$workflowTemplates = $(workflowTemplates),
	startWorkflow = "StartWorkflow",
	output = {}
	;
	output[getTemplatesForItem] = workflowTemplates;
	$workflowTemplates.find("WorkflowTemplates > WorkflowTemplate").each(function(i,e) {
	var $this = $(this)
	;
	// find workflow name
	if ($this.attr("Name") === opt.workflowName) {
	var guid = $this.find("WorkflowTemplateIdSet").attr("TemplateId");
	if (guid) {
	workflowGUID = "{" + guid + "}";
	output.workflowGUID = workflowGUID;
	// Stops jQuery#each iteration
	return false;
	}
	}
	});
	if (workflowGUID) {
	$().SPServices({
	operation: startWorkflow,
	item: opt.itemUrl,
	templateId: workflowGUID,
	workflowParameters: opt.workflowParameters,
	completefunc: function(data, status) {
	output[startWorkflow] = data;
	executeWorkflow.resolve(output);
	if ($.isFunction(opt.completefunc)) {
	opt.completefunc(output, status);
	}
	}
	});
	} else if (opt.debug) {
	// create error message
	var workflowError = workflowTemplates.status === 404 ?
	"The item's URL was not found." :
	"Workflow name not found."
	;
	errBox(thisFunction, "workflowName: " + opt.workflowName, workflowError);
	executeWorkflow.reject(output);
	} else {
	// workflowGUID not defined or network error occured with the GetTemplatesForItem call.
	executeWorkflow.reject(output);
	}
	}
	// Fire Workflow
	// jQuery#always is used here, so we guarantee execution of startWorkflow which resolves/rejects the promise.
	getTemplates().always(startWorkflow);
	return executeWorkflow.promise();
	};
	// Example Usage
	var linkNavWrkFlow = $().SPServices.StartWorkflow({
	debug: true,
	itemUrl: "http://portal/SPDev/Lists/LinkNav/21_.000",
	workflowName: "Test Wrkflw Params",
	workflowParameters: "<Data><TestField>All up in your SharePoints.</TestField></Data>",
	completefunc: function(data, status) {
	// console.log("StartWorkflow Op Data: " + data.StartWorkflow.responseText);
	console.log("Status: " + status);
	}
	});
	// Chaining example
	$.when( linkNavWrkFlow ).then(
	function( linkNavWrkFlowData ) {
	// console.log("StartWorkflow Op Data: " + linkNavWrkFlowData.StartWorkflow.responseText);
	},
	function( linkNavWrkFlowData ) {
	// console.log("StartWorkflow Failed Op Data: " + linkNavWrkFlowData.StartWorkflow.responseText);
	}
	);

view raw SPServices.StartWorkflow hosted with ❤ by GitHub

Original function:

	// Example itemUrl for Document:
	// http://<site>/<DocLib>/doc7.docx
	// Example itemUrl for Item:
	// http://<site>/<customsite>/Lists/<ListName>/<ID>_.000
	function fireWorkflow ( itemUrl, workflowName, workflowParams ) {
	workflowParams = workflowParams || "<root />";
	$().SPServices({
	operation: "GetTemplatesForItem",
	item: itemUrl,
	completefunc: function ( data, status ) {
	var workflowGUID = ""
	;
	$(data.responseXML).find("WorkflowTemplates > WorkflowTemplate").each(function(i,e) {
	var $this = $( this )
	;
	// hard coded workflow name
	if ( $this.attr("Name") === workflowName ) {
	var guid = $this.find("WorkflowTemplateIdSet").attr("TemplateId");
	if ( guid ) {
	workflowGUID = "{" + guid + "}";
	}
	// Stops #each iteration
	return false;
	}
	});
	$().SPServices({
	operation: "StartWorkflow",
	item: itemUrl,
	templateId: workflowGUID,
	workflowParameters: workflowParams,
	completefunc: function( data, status ) {
	// Continue processing after workflow is fired.
	}
	});
	}
	});
	}
	fireWorkflow( "http://<site>/Lists/<listName>/<ID>_.000", "Workflow Name", "<Data><TestField>All up in your SharePoints.</TestField></Data>" );

view raw Fire Workflow with Initiation Parameters hosted with ❤ by GitHub