Handling File Attachments in SharePoint Hosted apps using REST API

SharePoint List items can have one or more attachments. This post describes how to save/upload attachments and how to retrieve/download them for a specific list item in a list using SharePoint client REST API using Javascript.

Uploading an Attachment to SharePoint List using REST API using Javascript

First we have to read the file content and then upload the file to the list item as an attachment. I have used HTML 5 FileReader in order to read the file content. You have to pass the control id of your file input control to the following function. The function returns a promise that we can use to get the output.

   1: function readFile (uploadControlId) {

   2:  

   3:      if (!window.FileReader)

   4:          throw "The browser does not support HTML 5";

   5:  

   6:      var def = new $.Deferred();

   7:  

   8:      var element = document.getElementById(uploadControlId);

   9:      var file = element.files[0];

  10:      var parts = element.value.split("\\");

  11:      var fileName = parts[parts.length - 1];

  12:  

  13:      var reader = new FileReader();

  14:      reader.onload = function (e) {

  15:          def.resolve(e.target.result, fileName);

  16:      }

  17:      reader.onerror = function (e) {

  18:          def.reject(e.target.error);

  19:      }

  20:  

  21:      reader.readAsArrayBuffer(file);

  22:  

  23:      return def.promise();

  24:  }

 

The following function upload the attachment to the specified list. It takes the ID of the list item as the first parameter, list name as the second parameter, file name and buffer go as third and fourth parameters which we get from the above function.

   1: function uploadAttachment (id, listName, fileName, buffer) {

   2:         var url = _spPageContextInfo.webServerRelativeUrl +

   3:             "/_api/web/lists/getByTitle('" + listName + "')/items('" + id.toString() + "')/AttachmentFiles/add(FileName='" + fileName + "')";

   4:  

   5:         return $.ajax({

   6:             url: url,

   7:             type: "POST",

   8:             data: buffer,

   9:             processData: false,

  10:             headers: {

  11:                 Accept: "application/json;odata=verbose",

  12:                 "X-RequestDigest": $("#__REQUESTDIGEST").val(),

  13:                 "Content-Length": buffer.byteLength,

  14:                 "IF-MATCH": "*"

  15:             }

  16:         });

  17:     },

Now we have the function to read the file content and the other function to upload the content as an attachment to a list item, so next we have to chain them together to make a proper call. We do the chaining using the .done function.

   1: function executeUploadAttachment (id, listname) {

   2:         readFile("uploadControlId").done(function (buffer, fileName) {

   3:             uploadAttachment(id, listname, fileName, buffer).done(function () {

   4:                 alert("success");

   5:             }).fail(function () {

   6:                 alert("error in uploading attachment");

   7:             })

   8:         }).fail(function (err) {

   9:             alert("error in reading file content");

  10:         });

  11: }

 

Downloading the attachments of a list item

This is pretty straight forward.  The following URL will give the relative URLs of the attachments of a list item.

_spPageContextInfo.webServerRelativeUrl + /_api/web/lists/getbytitle(‘” + listname + “’)/items(‘” + id.toString() + “’)/AttachmentFiles

   1: function getAttachments (id, listname) {

   2:         return $.ajax({

   3:             url: _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/getbytitle('" + listname + "')/items('" + id.toString() + "')/AttachmentFiles",

   4:             type: "GET",

   5:             contentType: "application/json;odata=verbose",

   6:             headers: {

   7:                 "Accept": "application/json;odata=verbose"

   8:             }

   9:         });

  10:     }

Use the following code snippet get the output.

   1: getBillingAttachments(1, "mylist").done(function (data) {

   2:                 var result = data.d.results;

   3:  

   4:                 for (var i = 0; i < result.length; i++) {

   5:                     alert(result[i].ServerRelativeUrl);

   6:                }

   7:             })

Advertisement

Script Loading Error in SharePoint Hosted Apps

If you are into developing SharePoint Hosted apps there’s a high probability that you might have encountered script loading issues. In SharePoint Hosted apps this is common because most of the SP Javascript libraries are loaded on demand.

Sometimes JS libraries aren’t loaded into the application properly. Since this is a common issue and it’s better practice to load all Javascript files using a single loader rather than referencing all the required Javascript files in every we use script loaders. Script loaders are again Javascript libraries which loads the Javascript into the page and have events attached to this task.

Commonly when we use jQuery we do start our Javascript functionality from the well known $(document).ready(function () { }) event. This event is triggered by jQuery once the HTML DOM graph is being generated in the memory. Then we start doing our work.

But in SharePoint Hosted app space we might encounter a problem, that some script files will not load even after the jQuery document ready is fired. This could happen due to several reasons, internal errors, files are too large and still in the transition from the server, or custom settings (such as the on demand load in SharePoint).

The problem is though it is said to be on demand Javascript loading sometimes the components aren’t loaded when ever we need them. So using custom script loaders and making sure that all the required are loaded is a good a practice. Some can argue that this will delay the startup; it is true but the benefits you get definitely overrun the drawbacks.

There are plenty of script loaders but my favorite is yepnope.js. It is very light weight and works fast, and does not collide with any other Javascript libraries. I recommend this especially for SharePoint Hosted apps development.

You can download the yepnope.js here

The following a sample code on how to use yepnope to load the libraries. yepnope.js has many other methods as well.

   1: spinit_array = [];

   2:  

   3: spinit_array.push("url1");

   4: spinit_array.push("url2");

   5:        

   6: yepnope({

   7:     load: spinit_array,

   8:     complete: function () {

   9:         callback();

  10:     }

  11: });

You can include the above code in the page itself within the script tags, or can have one self invoking function and refer that in the page and call this. You can carry on your tasks in the callback function. Mostly I used my jQuery $(document).ready( function () { } ) as my callback.

CRUD operations in SharePoint hosted apps using Javascript Object Model (JSOM)

SharePoint hosted apps can be developed using only Javascript, but still we have 2 options and they’re Javascript Object Model (JSOM) for the SharePoint and SharePoint client REST API. This post explains how to perform CRUD operations in a list using JSOM. Click this link to see how to perform CRUD operations using REST API and Javascript.

The following code is written in ‘Revealing Module Pattern’ using the self invoking function.  A simple built it Contact List is used in this sample.

   1: var Qbe = window.Qbe || {};

   2: Qbe.Contacts;

   3: Qbe.ContactCRUD = Qbe.ContactCRUD || {};

   4:  

   5: Qbe.ContactCRUD = function () {

   6:  

   7:  

   8:     createItem = function (fname, lname, phone) {

   9:         var ctx = new SP.ClientContext.get_current();

  10:         var list = ctx.get_web().get_lists().getByTitle('ContactList');

  11:         ctx.load(list);

  12:  

  13:         var listItemCreationInfo = new SP.ListItemCreationInformation();

  14:  

  15:         var newContact = list.addItem(listItemCreationInfo);

  16:         newContact.set_item('Title', lname);

  17:         newContact.set_item('FirstName', fname);

  18:         newContact.set_item('WorkPhone', phone);

  19:  

  20:         newContact.update();

  21:         ctx.executeQueryAsync(success, error);

  22:     },

  23:  

  24:     readAll = function () {

  25:         var ctx = new SP.ClientContext.get_current();

  26:         var query = "<View><Query><OrderBy><FieldRef Name='Title'/>" +

  27:         "<FieldRef Name='FirstName'/></OrderBy></Query>" +

  28:         "<ViewFields><FieldRef Name='ID'/><FieldRef Name='Title'/>" +

  29:         "<FieldRef Name='FirstName'/><FieldRef Name='WorkPhone'/></ViewFields></View>";

  30:  

  31:         var camlQuery = new SP.CamlQuery();

  32:         camlQuery.set_viewXml(query);

  33:  

  34:         var list = ctx.get_web().get_lists().getByTitle('ContactList');

  35:         ctx.load(list);

  36:  

  37:         Qbe.Contacts = list.getItems(camlQuery);

  38:         ctx.load(Qbe.Contacts, 'Include(ID,Title,FirstName,WorkPhone)');

  39:  

  40:         ctx.executeQueryAsync(readAllSuccess, error);

  41:     },

  42:  

  43:     readAllSuccess = function () {

  44:         var html = [];

  45:         html.push("<table><thead><tr><th>ID</th><th>First Name</th>");

  46:         html.push("<th>Last Name</th><th>Title</th></tr></thead>");

  47:  

  48:         var listItemEnumerator = Qbe.Contacts.getEnumerator();

  49:  

  50:         while (listItemEnumerator.moveNext()) {

  51:             var listItem = listItemEnumerator.get_current();

  52:             html.push("<tr><td>");

  53:             html.push(listItem.get_item("ID"));

  54:             html.push("</td><td>");

  55:             html.push(listItem.get_item("FirstName"));

  56:             html.push("</td><td>");

  57:             html.push(listItem.get_item("Title"));

  58:             html.push("</td><td>");

  59:             html.push(listItem.get_item("WorkPhone"));

  60:             html.push("</td><td>");

  61:         }

  62:         html.push("</table>");

  63:         $('#displayDiv').html(html.join(''));

  64:     },

  65:  

  66:     updateItem = function (id, fname, lname, phone) {

  67:         var ctx = new SP.ClientContext.get_current();

  68:         var list = ctx.get_web().get_lists().getByTitle('ContactList');

  69:         ctx.load(list);

  70:  

  71:         var item = list.getItemById(id);

  72:         item.set_item('Title', lname);

  73:         item.set_item('FirstName', lname);

  74:         item.set_item('WorkPhone', phone);

  75:  

  76:         item.update();

  77:         ctx.executeQueryAsync(success, error);

  78:     },

  79:  

  80:     deleteItem = function (id) {

  81:         var ctx = new SP.ClientContext.get_current();

  82:         var list = ctx.get_web().get_lists().getByTitle('ContactList');

  83:         ctx.load(list);

  84:  

  85:         var item = list.getItemById(id);

  86:         item.deleteObject();

  87:         

  88:         ctx.executeQueryAsync(success, error);

  89:     },

  90:  

  91:     success = function () {

  92:         readAll();

  93:     },

  94:     error = function (sender, args) {

  95:         alert(args.get_message());

  96:     }

  97:  

  98:     // public interface

  99:     return {

 100:         createContact: createItem,

 101:         updateContact: updateItem,

 102:         deleteContact: deleteItem

 103:     }

 104: }();

CRUD operations in SharePoint hosted apps using REST API

SharePoint hosted apps can be developed using only Javascript, but still we have 2 options and they’re Javascript Object Model (JSOM) for the SharePoint and SharePoint client REST API. This post explains how to perform CRUD operations in a list using REST API.

The following code is written in ‘Revealing Module Pattern’ using the self invoking function.  A simple built it Contact List is used in this sample.

   1: var Qbe = window.Qbe || {};

   2: Qbe.RestContacts;

   3:  

   4: Qbe.RestContactsCRUD = Qbe.RestContactsCRUD || {};

   5:  

   6: Qbe.RestContactsCRUD = function () {

   7:     

   8:     createItem = function(newContact) {

   9:         $.ajax({

  10:             url: _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/getByTitle('ContactList')/items",

  11:             type: "POST",

  12:             contentType: "application/json;odata=verbose",

  13:             data: JSON.stringify(

  14:                 {

  15:                     '__metadata': {

  16:                         'type': 'SP.Data.ContactListListItem'

  17:                     },

  18:                     'Title': newContact.fname,

  19:                     'FirstName': newContact.lname,

  20:                     'WorkPhone': newContact.phone.toString()

  21:                 }),

  22:             headers: {

  23:                 "Accept": "application/json;odata=verbose",

  24:                 "X-RequestDigest": $("#__REQUESTDIGEST").val()

  25:             },

  26:  

  27:             success: function () {

  28:                 readAllContacts();

  29:             },

  30:  

  31:             error: function (err) {

  32:                 alert(JSON.stringify(err));

  33:             }

  34:         });

  35:     },

  36:  

  37:     updateItem = function (updatedContact) {

  38:         $.ajax({

  39:             url: _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/getByTitle('ContactList')/" +

  40:                 "getItemByStringId('" + updatedContact.id.toString() + "')",

  41:             type: "POST",

  42:             contentType: "application/json;odata=verbose",

  43:             data: JSON.stringify(

  44:                 {

  45:                     '__metadata': {

  46:                         'type': 'SP.Data.ContactListListItem'

  47:                     },

  48:                     'Title': updatedContact.fname,

  49:                     'FirstName': updatedContact.lname,

  50:                     'WorkPhone': updatedContact.phone.toString()

  51:                 }),

  52:             headers: {

  53:                 "accept": "application/json;odata=verbose",

  54:                 "X-RequestDigest": $("#__REQUESTDIGEST").val(),

  55:                 "IF-MATCH": "*",

  56:                 "X-HTTP-Method" : "PATCH"

  57:             },

  58:  

  59:             success: function (data) {

  60:                 readAllContacts();

  61:             },

  62:             error: function (err) {

  63:                 alert(JSON.stringify(err));

  64:             }

  65:  

  66:         })

  67:     },

  68:  

  69:     deleteItem = function (id) {

  70:         $.ajax({

  71:             url : _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/getByTitle('ContactList')/" +

  72:                 "getItemByStringId('" + id.toString() + "')",

  73:             type: "DELETE",

  74:             headers: {

  75:                 "accept": "application/json;odata=verbose",

  76:                 "X-RequestDigest": $("#__REQUESTDIGEST").val(),

  77:                 "IF-MATCH": "*"

  78:             },

  79:             success: function () {

  80:                 readAllContacts();

  81:             },

  82:             error: function (err) {

  83:                 alert(JSON.stringify(err));

  84:             }

  85:         })

  86:     },

  87:  

  88:     readAllContacts = function () {

  89:         $.ajax(

  90:             {

  91:                 url: _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/getByTitle('ContactList')/items" +

  92:                     "?$select=Id,FirstName,Title,WorkPhone" +

  93:                     "&$orderby=ID",

  94:                 type: "GET",

  95:                 contentType: "application/json;odata=verbose",

  96:                 headers: {

  97:                     "Accept": "application/json;odata=verbose"

  98:                 },

  99:                 success: function (data) {

 100:                     var html = [];

 101:                     html.push("<table><thead><tr><th>ID</th><th>First Name</th>");

 102:                     html.push("<th>Last Name</th><th>Phone</th><th>ETag</th></tr></thead>");

 103:  

 104:                     var result = data.d.results;

 105:  

 106:                     for(var i = 0; i < result.length; i++)  {

 107:                         html.push("<tr><td>");

 108:                         html.push(result[i].ID);

 109:                         html.push("</td><td>");

 110:                         html.push(result[i].FirstName);

 111:                         html.push("</td><td>");

 112:                         html.push(result[i].Title);

 113:                         html.push("</td><td>");

 114:                         html.push(result[i].WorkPhone);

 115:                         html.push("</td><td>");

 116:                         html.push(result[i].__metadata.etag);

 117:                         html.push("</td><td>");

 118:                     }

 119:                     html.push("</table>");

 120:                     $('#displayDiv').html(html.join(''));

 121:                 },

 122:                 error: function (err) {

 123:                     alert(JSON.stringify(err));

 124:                 }

 125:             })

 126:     },

 127:  

 128:     success = function () {

 129:         readAllContacts();

 130:     },

 131:  

 132:     error = function (sender, args) {

 133:         alert(args.get_message());

 134:     }

 135:  

 136:     return {

 137:         createContactREST: createItem,

 138:         updateContactREST: updateItem,

 139:         deleteContactREST: deleteItem,

 140:         displayContactREST: readAllContacts

 141:     }

 142: }();

Power BI – Data Management Gateway

Power BI for Office 365 has a component known as Data Management Gateway which allows us to expose the on premise data sources to cloud. On premise boxes which host the data sources do not require a public IP but you servers still require an Internet connection. Currently Data Management Gateway supports SQL Server, Oracle and Power Query sources. This article guides you how to setup the Power BI Data Management Gateway and expose a SQL Server database to cloud. First Go to Power BI Admin Center from your Office 365 subscription.

image

In the Power BI Admin Center click gateways and then click new gateway

image Enter a name for you gateway and an optional description. If you want to store the credentials of the data sources you want to link to Office 365 in the cloud you can check the “Enable cloud credential store to archive business continuity for gateway” option. By checking this you agree to store the data source credentials in the cloud in a secure way. Storing the credentials in the cloud gives you the ability to restore the gateway without entering credentials. Most of the cases this would be an organizational decision based on the trust. This link explains the advantages of enabling cloud credential store. 

image

Once you click Create you will be directed to the second step. Here you will get the link to download the Data Management Gateway and the key to build the trust between the gateway and Office 365.

image

Copy the key (later you can change this). Download the Data Management Gateway and install in your on premise machine. Data Management Gateway is a Windows Service. Once the installation is done, you will get the Microsoft Data Management Gateway Configuration Manager in your machine. Open it and paste your gateway key and register the gateway.

image

In the settings you can configure the protocol and the port for the communication between your Data Management Gateway service and Office 365.  As of now we have completed setting up the gateway to communicate to the Office 365 environment. Below diagrams explains how the communication happens between your on premise machine and Office 365.

image

Click on the Settings tab in the Microsoft Data Management Gateway Configuration Manager in order to configure protocol and the port of the communication.

image

Next step is to create data sources, go to data sources tab in the Power BI admin center and setup a data source. Here we’ll setup a SQL Server data source.

image Then we have to select how to expose the data source, only as a cloud source or as well as a OData feed. Enabling the data sources as OData feed provides the opportunity to use this data source in Power Query. Cloud Access makes the data source available in SharePoint online and refreshing is possible.

image In the next wizard enter the connection details. Select the Gateway, data source type and the appropriate provider. In this case my on premise database is SQL Server 2012 so I’ve used SQL Server Native Client 11.0. Then enter the server name and database name in order to construct the connection string. If you select the connection string option (rounded in blue) you need not to provide these details one by one, you can simple enter your connection string.

image

After setting up the server and database details along with the provider information, you can see that the credentials button is enabled. Click on the credentials this will launch a one click application. In that application enter your database credentials either Windows Authentication or SQL Authentication.

image

Test the connection and if the connection is successful click OK. This saves the credentials in the local machine specific to the application. The credentials are not transferred to the Office 365 since we didn’t select the Enable Cloud Credential Store. Now the data source setup is complete. Go to the data settings tab in the data sources and select the desired tables and views to be exposed from the Office 365.

image

That’s it, we have connected our on premise data source to Office 365. Go to data source section and click the properties shortcut (triple dots) in the context menu you can see the OData Feed URL for your on premise data base.

image

It’s amazingly simple to expose your on premise data sources across the organization and even to extranet through Power BI for Office 365. You can get the OData URL and start accessing your data source from Excel Power Query.

image 

Paste the URL (note that OData feeds are exposed via HTTPS) and select the Organizational Account to enter your Office 365 credentials.

image 

Then you can access the data exposed through the OData feed in Excel. The below image shows the data exposed in this sample.

image

How does this entire process work ?

mm

What is Power BI

Last week I delivered an introductory presentation about Power BI covering all the features of it in the SQL Server Universe user group meeting. I got a fair amount of time to showcase the features of Power BI to the audience.

I concluded my presentation giving the following definition to Power BI from Microsoft’s point of view or what Microsoft has been trying to do.

 

Productizing self service BI tools and services coupled with the company’s cloud based organizational strategy

Personally I think Microsoft is determined to have a product in the self service BI market, they’ve been trying to bring all the tools and services under one umbrella named Power BI.

It works perfectly well, it’s a very impressive product to deliver a showcase. I have delivered product introductory presentations for Azure and Windows 8 in other user group meetings. Compared to them Power BI is a small product which draws the interest of few people, but it didn’t fail to impress the audience, so thought of writing about Power BI.

Power BI has components categorized under 3 topics.

Excel Features

  • Power Query
  • Power Pivot
  • Power View
  • Power Map

Power BI for Office 365

  • Power BI Sites
  • Power BI Q&A
  • Query and Data Management
  • Power BI Windows App

IT Infrastructure service for Power BI

  • Provision Power BI for Office 365
  • Power BI Admin Center
  • Data Management Gateway

Excel features are available for download for free. You can download them from this URL

Detailed blog posts about each component will follow in the coming days.

Setting up Office 365 for SharePoint App Development

This is fairly straight forward. Sign up for the Office 365 account for developers using this link. You can sign up for a 30 days free Office 365 account with the developer license.

Once you get the Office 365 environment, you can start developing apps for SharePoint online. There 3 types of SharePoint apps (this MSDN article describes it better) to be developed, this categorization is done using the hosting model of the application.

In your office 365 setup go to SharePoint and create a SiteCollection with the Developer Site template. Make sure to that the template is Developer Site otherwise there’s a high chance that you’ll get an error while debugging the apps which says  “Error occurred in deployment step ‘Install app for SharePoint’: Sideloading of apps is not enabled on this site”

image

Now you’re done, you can develop the apps 🙂

Office 365 – SharePoint app development using Napa

This post contains few important screenshots of Office 365 SharePoint app development using Napa.

Napa is a free Office 365 app which brings a development environment within the browser, though this not enough for most of the advanced developments this is quite handy it has debug mode, intellisense and other cool features. And also you can download your project to your machine along with all the deployment settings and edit in your Visual Studio as well. Napa is a good place for a beginner to start and to do quick tweaks in the code.

Once you activate your Office 365 account you can start jump into development (your Office 365 subscription should be Microsoft Office 365 Enterprise Developer).

Since all the developers do not have the subscription I captured few screenshots and put it here.

1) First you have to install Napa in your Office 365. It’s free.

2) You select what app you are going to develop.

Selecting the app to develop

 

3) Napa opens the app template for you in the browser.

2

As you can see in the left hand side we have a toolbox (thin gray layer) with commands for debug, run in local Visual Studio, Publish and more. Next to that another thick light gray bar which serves the purpose of the Solution Explorer of the VS. Here the SP app have a file named App.js. This is the startup file for our SP apps. Then we can have our custom ASPX pages as we want.

When I click the debug, a pop up comes to show the progress of packaging and deploying for the debug. (as shown in the screen).

4) First Run

3The template is configured to print the logged in username in the app, this code is set in the App.js. So when I ran the app without making any changes in the code I got the above screen.

5) Editing and Debugging

4I edited the code and put some controls and ran the app. Wow I’m a techie now 😛

 

6) Connecting to Local Visual Studio to download the project

5

When you try to connect to the local VS the above popup is shown, and you can choose your preferred language and continue. (Wait, if I were to select the language now so far I developed the app. The point is when you create a project in Napa it creates the project for you based on the JavaScript development. – Remember I get the App.js) So when I try to import my project to the local VS and edit it asks what is my preferred language of development.

When you first import a project from Office 365 Napa we need some additional plugins to be installed in our local machine. This is mostly based on what pre installed VS configurations. But need not to worry MS Web Platform installer will take of your download.

Forget the web parts and develop the Apps. 🙂

I think along with Office 365 and SharePoint MS has framed their development sector ‘Office Development’ properly. Earlier though we say Office Development, SharePoint development was going in a different way and Office module development was going in another way. Now things are merged not only in words but also by the scenarios and environments as well.