Microsoft Adds Excel Support to the Office 365 REST API

Microsoft has added support for interacting with Excel documents, formulas and reporting through the Office 365 REST API.

Breakdown of tasks in a pie chart

Key scenarios that this could enable include:

  • Reading and writing data to an Excel document stored within Office 365
  • Calling an Excel formula for calculations
  • Having Excel online render a chart or graph based on Excel data

Code samples and documentation are available here.

Read More

Power BI REST API is now v1.0

The Power BI Preview service introduced a new REST API for pushing data into Power BI.  I previously blogged about the preview REST APIs and these seem to be similar with a few minor updates.  It has now been published as version 1.0 to coincide with the Power BI general availability release.

The value of the Power BI REST API is that you can push your own custom datasets, tables and rows into Power BI from any source using either JavaScript for client side scenarios or C# for server side scenarios.  You can authenticate via web or as a console application.

image

As with the preview version, version 1.0 seems to be missing a few basic API methods such as you cannot actually delete a dataset or change a dataset and you cannot delete a table once it is created. 

Read More

Differences Between URL and Publishing Image when Developing SharePoint Apps with JavaScript

We’re building a basic slider App Part in SharePoint using all client side code.  We created an App Part for SharePoint and constructed our slider using some publically available JavaScript libraries.

image

The App Part pulls the data from a list mapped by site columns in the App Part’s properties.  Our list has a title, description, and background property to populate the slider’s content. 

There are a couple different methods for storing the image:

  • Custom site column with a URL type
  • Add an existing column such as Page Image or Rollup Image which are of type Publishing Image (these are added to SharePoint when you turn on the publishing features)

There are some significant differences between the two for the end-user supplying the image:

image

The Page Image user interface is definitely better – instead of forcing the end-user to supply the URL to the image, it has a link to the default Site Collection Images library and the user can pick the image from the gallery.  The Page Image also supports Image Renditions which automatically convert images to standard sizes.

image

If you’re writing code that dynamically supports both types of site columns, you’ll run into challenges because parsing out a URL is different than parsing a Publishing image.

The first step is figuring out which site column type you’re dealing with when you fetch the data.  You can query the properties of the list itself and get the field type of the column. 

function schema(listName) {
     this.listName = listName;
     this.fields = [];
     this.addField = function (field) {
         this.fields.push(field);
     };
     this.getField = function (fieldName) {
         var matchingField = null;

        for (i = 0; i < this.fields.length; i++) {
             if (this.fields[i].name == fieldName) {
                 matchingField = this.fields[i];
             }
         }
         return matchingField;
     };
     this.loadSchema = function () {
         hostWebUrl = decodeURIComponent(manageQueryStringParameter(‘SPHostUrl’));
         appWebUrl = decodeURIComponent(manageQueryStringParameter(‘SPAppWebUrl’));

        // create an object to wait on until the Async method below returns
         var deferred = $.Deferred();
         var ctx = new SP.ClientContext(appWebUrl);
         var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl);

        var web = appCtxSite.get_web(); //Get the Web
         var list = web.get_lists().getByTitle(listName); //Get the List
         var fields = list.get_fields();
         ctx.load(fields);
         //Execute the Query Asynchronously
         ctx.executeQueryAsync(
             Function.createDelegate(this, function () {

                var enumerator = fields.getEnumerator();

                while (enumerator.moveNext()) {
                     var listfield = enumerator.get_current();
                     var fieldType = listfield.get_fieldTypeKind();
                     var fieldName = listfield.get_internalName();
                     var field = new schemaField(fieldName, fieldType);
                     this.addField(field);
                 }
                 deferred.resolve(“Success”);
             }),
         Function.createDelegate(this, function () {
             addErrorMessage(“Operation failed  ” + arguments[1].get_message(), 1);
             deferred.reject(“Operation failed”);
        }));
         return deferred.promise();

    };
}

This piece of code takes a list and creates a schema that is stored in memory that describes each of the columns.  This function provides a schema that will allow you to look up the site column in memory and figure out the type. 

SharePoint provides an enumeration called SP.FieldType that you can compare your site column with to figure out its type.  URL, for example, has a field type of 11 and matches to SP.FieldType.URL.

When you query the field type of a Publishing Image, the field type is 0 – this means it’s an “invalid” field type.  Publishing Image isn’t recognized as part of the core standard because its part of the publishing feature infrastructure. 

When you try to pull the value of the field in JavaScript (you’ll see similar behavior in the C# or REST APIs), there are some significant differences:

  • When you retrieve a URL in JavaScript, it comes as a specialized URL object that has a get_url() method for fetching the URL.  This behavior is unique to URL fields.
    url = currentListItem.get_item(imageField).get_url();
  • When you retrieve a Page Image in JavaScript, the field is treated like a normal field in that you can fetch the value with the call:
    url = currentListItem.get_item(imageField);
    However, the format of the content isn’t the URL but the entire image tag, e.g. <img src=…

If you try to call get_item on a URL field, you’ll get an error.  If you’re writing code that needs to fetch the URL out of a Publishing Image field, you’ll have to parse out the image tag to just grab the src attribute.

Read More

Office 365 and OneDrive APIs Now have CORS Support: Key for JavaScript Apps

JavaScript, by default, implements a “Same Origin Policy’”, which means that JavaScript can only make calls back to its originating domain.  For application developers using JavaScript to call external services through REST APIs, this is a big limitation as these services can live anywhere on the Internet across multiple domains.

Cross-origin Resource Sharing (CORS) is a standard mechanism to allow JavaScript applications to make call across domains.  The specification defines a set of headers in the HTTP call that allow the browser and the server to negotiate authorization as requests cross domains. 

Microsoft has just announced that CORS support is now available for Office 365 APIs, specifically the Sites APIs and the OneDrive APIs.  Mail/Calendar/Contacts APIs will support CORS soon.

The support for CORS is an ongoing evolution of the Office 365 APIs to support JavaScript and frameworks such as AngularJS as first class programming frameworks and to remove the need for server side code to work with Office 365 from your JavaScript code. 

Read More