Reading from & writing to Sharepoint using Sharepoint REST API via SAP iRPA

Lately I had to develop a bot that needed to utilize Sharepoint functionality. I came to a conclusion that the fastest way would be by using Sharepoint REST API.

1. Goals of this blog:
   • How to authenticate yourself in Sharepoint?
   • How to read items from a list?
   • How to search a list?
   • How to write items to a list?
   • How to upload a file to a sharepoint folder?
2. Prerequisites:
   • Dependencies – I used only one dependency irpa_core version 1.24.58 but I guess you can use the newest one as well. We will need only Call Web Service activity,
   • Install Postman – we will need it to test a connection to Sharepoint, We could do it from SAP iRPA but just to make sure that we can connect from anywhere,
   • Make sure you are an owner of your sharepoint site collection. If you do the next steps and you still can’t connect to Sharepoint you may not have the right privileges and some help from your Sharepoint administrator is required to get things started. If you’re not an expert in Sharepoint I suggest you don’t waste time trying to figure out how to make it work,
   • Register Sharepoint add-in (go to Register using AppRegNew)AppDomain and Redirect URI you need to fill in yourself and they can contain valid but dummy addresses. Click the two Generate button in order to retrieve the new values that will be set for the fields respectively Client Id and Client Secret. This is something you need to save somewhere for future reference. You will see them only once. If I were to register an add-in I would have to go for example here:
   • Check the existence of your add-in. When you’re done and you saved everything go to the same URL, just change the text after the last forward slash from AppRegNew.aspx to AppPrincipal.aspx to confirm that the add-in is there. Copy to clipboard your add-in id which consists of your client id and ‘@’.character and your tenant id.
   • Add proper privileges to your add-in. Go to the same URL, just change the text after the last forward slash from AppPrincipal.aspx to AppInv.aspx and paste your add-in id from the clipboard and paste this xml (see the print screen for instructions) . Hit the buttons to save everything.

     <AppPermissionRequests AllowAppOnlyPolicy="true">
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl"/>
     </AppPermissionRequests>​

     or if you come across some errors

     <AppPermissionRequests AllowAppOnlyPolicy="true">
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="FullControl"/>
     </AppPermissionRequests>

     

   • Verify whether your add-in is registered on Azure. Look for registered apps. It should be visible within seconds but perhaps it is better to give it 15 minutes and then check it. If you can’t find it seek assistance from your Sharepoint Support Team. This concludes the prerequisites. Let’s test it.
3. Testing in Postman – in order to test connection you can create a file and paste this code

   {
   	"variables": [],
   	"info": {
   		"name": "sharepoint",
   		"_postman_id": "d44c8c24-1c1a-74fc-865b-e6259c4929a5",
   		"description": "",
   		"schema": "https://schema.getpostman.com/json/collection/v2.0.0/collection.json"
   	},
   	"item": [
   		{
   			"name": "sharepointRequest",
   			"request": {
   				"url": "https://accounts.accesscontrol.windows.net/{{YOUR_TENANT}}/tokens/OAuth/2",
   				"method": "POST",
   				"header": [
   					{
   						"key": "Content-Type",
   						"value": "application/x-www-form-urlencoded",
   						"description": ""
   					},
   					{
   						"key": "Accept",
   						"value": "application/json",
   						"description": ""
   					}
   				],
   				"body": {
   					"mode": "urlencoded",
   					"urlencoded": [
   						{
   							"key": "grant_type",
   							"value": "client_credentials",
   							"type": "text"
   						},
   						{
   							"key": "resource",
   							"value": "00000003-0000-0ff1-ce00-000000000000/{{YOUR_COMPANY}}.sharepoint.com@{{YOUR_TENANT}}",
   							"type": "text"
   						},
   						{
   							"key": "client_id",
   							"value": "{{YOUR_ADD-IN_ID}}",
   							"type": "text"
   						},
   						{
   							"key": "client_secret",
   							"value": "{{YOUR_CLIENT_SECRET}}",
   							"type": "text"
   						}
   					]
   				},
   				"description": ""
   			},
   			"response": []
   		}
   	]
   }​

   change the variables highlighted yellow with your own values that you managed to retrieve in the previous steps or create global variables in Postman and from there set their values. Save the file as json. Now you can import the file to Postman and test it.

4. Building our automations in SAP iRPA
   • Authentication – first we need to start with retrieving the access token. It’s exactly the same thing we did in Postman but now we need to move it to SAP iRPA. Every our automation will contain only two steps – custom script and call web service activity. Custom script should contain the following. You should already know what is behind the blurred parts:🎓A reminder. Your client id = your add-in id. Below find the code for your convenience where you need to supply your values:

     const inputForWebApiCall = {
         method: "POST",
         url: "https://accounts.accesscontrol.windows.net/" + YOUR_TENANT + "/tokens/OAuth/2",
         headers: {
             "Accept": "application/json",
             "Content-Type": "application/x-www-form-urlencoded"
         },
     
         form: {
             "grant_type"    : "client_credentials",
             "resource": "00000003-0000-0ff1-ce00-000000000000/" + YOUR_COMPANY + ".sharepoint.com@" + YOUR_TENANT,
            "client_id"     :  sharepointSiteCredentials.login, //input variable
            "client_secret" :  sharepointSiteCredentials.password, //input variable
         },
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     return inputForWebApiCall; //output variable​

     so your final automation could resemble something like this: I’ll skip the part about the result because that’s exactly what we received in Postman.

   • Reading items from a list – must-read sources for this are Sharepoint pages but let’s figure it out together. To get started we need to know your site url, the name of your list and the access token. The last one we already know how to retrieve. In my case the url is this “myCompany.sharepoint.com/sites/RPA_test/”. The name of the list is “RPA checklist GL”. I’ll skip providing print screens with my automations because as in the case of the access token we need basically only two steps: custom script and call web service activity. So here’s the code for the custom script:

     const inputForWebApiCall = {
         method: 'GET',
         url: YOUR_URL + '_api/web/lists/getbytitle(\'' + YOUR_LIST_NAME + '\')/items',
         headers: {
             'Authorization': 'Bearer ' + YOUR_ACCESS_TOKEN,
             'Accept': 'application/json',  //; odata=verbose',
             'Content-Type': 'application/json'
         },
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     return inputForWebApiCall;​

     Since we are asking for all the items we are going to get the first 100 items plus a link to the next 100. If you want to get specific records you need to apply odata filtering in your url. We’ll get to it later. Here’s what you could find in the watch window if you inspect the value of your result in SAP iRPA:

     🎓Note that you don’t have to use the name of your list. I chose the name of the list but you can also use your list’s GUID which is unique but then you would have to change your url a bit (See Sharepoint pages for this)

   • Searching items on a list – we will apply odata filtering to retrieve only specific records. We’ll use a simplified example here. All text fields are single lines. If your field is a multi-line you need to create CAML query. I’ll provide a working example later. But now let’s assume that my list contains a field TicketNumber and I want to retrieve only records that contain in this field a specific value. Options for call web service activity could look like this:

     const inputForWebApiCall = {
         method: 'GET',
         url: MY_URL + '_api/web/lists/getbytitle(\'' + MY_LIST_NAME + '\')/items?$filter=TicketNumber eq \'' + ticketNumber + '\'', //ne null',
         headers: {
             'Authorization': 'Bearer ' + MY_ACCESS_TOKEN,
             'Accept': 'application/json',  //; odata=verbose',
             'Content-Type': 'application/json'
         },
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     return inputForWebApiCall;

     Now an example with a field of type Note. If I wanted to find now all records that in the field “TicketNumber” contain a specific value my options could resemble something like this:

     const queryPayload = { 
            'query':{ 
                '__metadata': { 'type': 'SP.CamlQuery' },
                'ViewXml': "<View><Query><Where><Contains><FieldRef Name='TicketNumber'/><Value Type='Note'>" + ticketNumber + "</Value></Contains></Where></Query></View>"
             } 
        }; 
     
     
     const inputForWebApiCall = {
         method: 'POST',
         url: YOUR_URL + '_api/web/lists/getbytitle(\'' + YOUR_LIST_NAME + '\')/getitems',
     
         
         headers: {
             'Authorization': 'Bearer ' + YOUR_ACCESS_TOKEN,
             'Accept': 'application/json',  //; odata=verbose',
             'Content-Type': 'application/json;odata=verbose'
         },
         body: JSON.stringify(queryPayload),
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     return inputForWebApiCall;

   • Writing an item to a list – first you need to know which fields in your list are mandatory and what the internal names of your fields are. I assume you already know this. In this automation the minimum number of steps is three – two custom scripts and call web service activity. The first custom script we will use to create a record that is to be written to our list. In my case a record could be like this:

     return {
         "Title": "PL01", //set
         "ProcessGroup": "RPA", //constant
         "ProcessName": "plik5.xlsx", //set
         "PostingNumber": "60000007", //set
         "TicketNumber": "SCTASK_6000011", //set
         "Period": "05.2020", //set
         "comment": "Hmm. Niech pomysle", //set
         "Frequency": "monthly", //constant
         "DeadlineDay": 3, //constant
         "DeadlineHour": 15, //constant
         "CompletionDay": 3, //constant
         "CompletionHour": 15, //constant
         "Work": "RPA", //constant
         "Ticket": "YES" //constant
     };

     The output of this automation is to be the input variable of the second custom script with options:

     const payload = {
       "__metadata": {
         "type": "SP.Data." + YOUR_LIST_NAME.replace(/\s/g, "_x0020_") + "ListItem"
       }
     };
     
     const entries = Object.entries(YOUR_RECORD); //record is an input variable of type any
     
     if (entries.length === 0) throw new Error("record contains no properties");
     
     for (const entry of entries) {
         payload[entry[0]] = entry[1];
     }
     
     const inputForWebApiCall = {
         method: 'POST',
         url: YOUR_URL + '_api/web/lists/getbytitle(\'' + YOUR_LIST_NAME + '\')/items',
         headers: {
             'Authorization': 'Bearer ' + YOUR_ACCESS_TOKEN,
             'Accept': 'application/json',  //; odata=verbose',
             'Content-Type': 'application/json; odata=verbose'
         },
         body: JSON.stringify(payload),
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     return inputForWebApiCall;

     Now we’re ready to add call web service activity and test creating items. The result is the item’s details after it has been created in your Sharepoint list. When you inspect the output value in the watch window you can see an object similar to:

   • Uploading a file to Sharepoint – for detailed information on uploading files please read Sharepoint Pages. My goal is to provide you a working example that can get you started. So we’re not going to talk about uploading files with versioning. Let’s assume you want to upload a file to a nested folder in shared documents nevermind whether the folder exists. Let’s say the path to your file is “folder1/folder2/folder3”. First we need to make sure your path exists. If not we have to create it. But you can’t create a nested path. You have to create each folder separately. If folder1 does not exist you first create folder1. If folder2 does not exist your path to create is folder1/folder2. If folder3 does not exist you have to create this path folder1/folder2/folder3. If you do otherwise you are going to get an internal error (500). This is not very informative. So basically you would need an automation that would output an array of paths that you could then utilize in for each loop to create all the necessary paths. You don’t need to test their existence. It won’t do any harm if you try to create an already existing folder. It does matter that you create them one at a time and in the right order. Let’s create a path. Again I will provide below just the custom script code for the options for call web activity:

     const payload = {
       "__metadata": {
         "type": "SP.Folder"
       },
       "ServerRelativeUrl":  "/sites/" + YOUR_SITE_NAME + "/Shared Documents/" + YOUR_NESTED_PATH
     };
     
     const inputForWebApiCall = {
         method: 'POST',
         url: YOUR_URL + '_api/web/folders',
         headers: {
             'Authorization': 'Bearer ' + YOUR_ACCESS_TOKEN,
             'Accept': 'application/json',  //; odata=verbose',
             'Content-Type': 'application/json; odata=verbose'
         },
         body: JSON.stringify(payload),
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     return inputForWebApiCall;​

     Now when your options are ready it is time to add call web service activity. If you inspect the response in the watch window you will something similar to this one:The above output tells us that we have just created a folder named “Test”. Now we are ready to upload a file.

     const inputForWebApiCall = {
         method: 'POST',
         url: YOUR_URL + '/_api/web/GetFolderByServerRelativeUrl(\'' + YOUR_SERVER_RELATIVE_URL + '\')/Files/add(url=\'' + YOUR_FILE_NAME + '\',overwrite=true)',
         headers: {
             'Authorization': 'Bearer ' + YOUR_ACCESS_TOKEN,
             'Accept': 'application/json',  //; odata=verbose',
             'Content-Type': 'application/octet-stream' //Probably the best option. You don't need to worry about the MIME type of your file. I have tried this with excel files and pictures.
         },
         readFileAsBufferToBody: YOUR_FILE_PATH,
         responseType: 'json',
         resolveBodyOnly: true
     
     };
     
     
     return inputForWebApiCall;

     🎓 Notice that YOUR_SERVER_RELATIVE_URL can be retrieved from the output of the last created path so if you plan to create folders dynamically you can pass the output of the automation responsible for creating paths to the one responsible for uploading files and interpret it there to get the value of ServerRelativeUrl. 🎓Last final remark: DON’T BE TEMPTED TO USE SHAREPOINT AS A DATABASE. It has so many limitations. If you need to upload many text files, store them and reuse them for analytics forget about it. Try a database like MongoDb instead. Maybe ftp is better. I haven’t tried it yet but I’m just about to find out.

5. Conclusion I decided to go for REST API in my project. Microsoft Graph generally is the preferred way and gives you access not only to Sharepoint but to all Office365 products. Here is an article that is worth reading.https://devblogs.microsoft.com/microsoft365dev/impact-of-azure-access-control-deprecation-for-sharepoint-add-ins/. Nevertheless if we consider my goals Sharepoint REST API is good enough and perhaps it can be good for you too. Even if SAP iRPA gives us dedicated activities to communicate with Office365 (they say this can happen at the end of this year) utilizing web api calls seems to be one of the cleanest solutions (at least in my view). Thank you for time and I hope someone can find it useful and informative. Please share your feedback and thoughts in the comments. 🙂