topic Re: Post to SharePoint 2010 list in SAS Programming

Post to SharePoint 2010 list

dwaynejarman — Sun, 08 Apr 2018 14:13:19 GMT

I am trying to figure out how to add a new item or update an existing item using SAS PROC HTTP in a SharePoint 2010 list.

Here is a javascript/jquery button action that is tied to a temporary list to test posting data form SAS to SharePoint 2010. I've verified that the below code updates the first item's title to "I am a robot" and inserts a new item with the title "I am a robot too" This is javascript that is updating the list item or creating the list item using the SharePoint API (JSON data). The data update in the list is attached as a screenshot.

$('#testerbtn').click(function(event) {
			var beforeSendFunction = function (xhr) {
								   xhr.setRequestHeader("If-Match", "*");
								   // Using MERGE so that the entire entity doesn't need to be sent over the wire.
								   xhr.setRequestHeader("X-HTTP-Method", 'MERGE');
			}
			
			//Update an existing item, Item 1, with I am a robot as the title
	        var d = {};
			d.Title = 'I am a robot';
			var URL = thisbase.toLowerCase() + "/_vti_bin/ListData.svc/Tester2(1)";
			console.log('data: '+JSON.stringify(d));
			console.log(URL);
			var call = $.ajax({ 
		                   type: "POST",
		                   contentType: "application/json; charset=utf-8",
		                   processData: false,
		                   beforeSend: beforeSendFunction,
		                   url: URL,
		                   data: JSON.stringify(d),
		                   dataType: "json"		
			});					                   
			call.done(function (data,textStatus, jqXHR){
				alert('success');			
			});
			call.fail(function (jqXHR,textStatus,errorThrown){ 
				alert('error. see logs');
				console.log("Error in calendar data transmission: " + jqXHR.responseText); 
			}); 
	
	
			//Create a new item with I too am a robot as a title
			var d = {};
			d.Title = 'I too am a robot';
			var URL = thisbase.toLowerCase() + "/_vti_bin/ListData.svc/Tester2";	        
			console.log('data: '+JSON.stringify(d));
			console.log(URL);
			
			var call = $.ajax({ 
						   type: "POST",
		                   contentType: "application/json; charset=utf-8",
		                   processData: false,
		                   headers: {
					            "Accept": "application/json;odata=verbose",
					            "X-RequestDigest": $("#__REQUESTDIGEST").val()
					       },
		                   url: URL,
		                   data: JSON.stringify(d),
		                   dataType: "json"
			});
			call.done(function (data,textStatus, jqXHR){
				alert('success');			
			});
			call.fail(function (jqXHR,textStatus,errorThrown){ 
				alert('error. see logs');
				console.log("Error in calendar data transmission: " + jqXHR.responseText); 
			}); 
	
		event.preventDefault();
	
});

Here is what the data looks like that is submitted to sharepoint:

data: {"Title":"I am a robot"}

data: {"Title":"I too am a robot"}

So I tried to repeat this with SAS PROC HTTP. I think PROC JSON will only output an array, so I tried a string with no luck.

filename resp TEMP;
proc json out=resp pretty;
	export work.tester (where=(one='A')) / nosastags;
run;
 
filename topics "C:\temp\tester_resp_json.txt" encoding="UTF-8";

/*		                   type: "POST",
		                   contentType: "application/json; charset=utf-8",
		                   processData: false,
		                   beforeSend: beforeSendFunction,
		                   url: URL,
		                   data: JSON.stringify(d),
		                   dataType: "json"		
*/

proc http url="http://sharepoint.site/subsite/_vti_bin/ListData.svc/Tester2(1)" 
	method="POST" in=resp /*"{'Title':'I am not a robot'}" */
	out=topics AUTH_NEGOTIATE; 
	debug level = 0;
	headers
		"contentType "="application/json;odata=verbose;charset=utf-8"
		"processData"="false"
		"beforeSend"="function (xhr) {xhr.setRequestHeader('If-Match', '*'); xhr.setRequestHeader('X-HTTP-Method', 'MERGE');"
		"Accept"="application/json; charset=utf-8" 
		"dataType"="json";
	
run;

When I look at the output, because I get this error:

NOTE: PROCEDURE HTTP used (Total process time):
real time 0.31 seconds
cpu time 0.00 seconds

NOTE: 405 Method Not Allowed

This is what the output says

{
"error": {
"code": "", "message": {
"lang": "en-US", "value": "The URI 'http://sharepoint.site/subsite/_vti_bin/ListData.svc/Tester2(1)/' is not valid for POST operation. For POST operations, the URI must refer to a service operation or an entity set."
}
}

Results after using the javascript (no success yet using SAS)

success post using javacript

Re: Post to SharePoint 2010 list

ChrisNZ — Tue, 30 Jan 2018 21:14:43 GMT

Wouldn't

NOTE: 405 Method Not Allowed

mean that

	method="POST"

is disallowed?

Re: Post to SharePoint 2010 list

dwaynejarman — Sun, 08 Apr 2018 14:13:55 GMT

Hi,

Made some modification, but now am getting an HTTP error 415.

filename topics "C:\temp\tester_resp_json.txt" encoding="UTF-8";
filename submit "C:\temp\submit.txt" encoding="UTF-8";

proc http url="http://sharepoint.site/subsite/_vti_bin/ListData.svc/Tester2(1)" 
	method="POST" in=submit
	out=topics AUTH_NEGOTIATE; 
	headers
		"dataType"="json"
		"contentType"="application/json; charset=utf-8"
		"processData"="false"
		"X-HTTP-Method"="MERGE"
		"If-Match"="*" ;		
run;

The dataType matches what works using JavaScript. I'm not sure what could be going on as the CT= option has depreciated since 9.4 M3 from the documents. Any thoughts?

Re: Post to SharePoint 2010 list

DaveHorne — Thu, 08 Feb 2018 14:27:25 GMT

Using the SharePoint api's is on my "bucket list of SAS programs" to try and work out out some day (to be more portable across platforms).

On Windows, what I've been doing for many years is creating a Microsoft Access Database which contains a link to the SharePoint list. Then in SAS, I access the database (team site list) via SAS/Access:

2    libname teamsite access "D:\Public\SharePoint\Public.accdb";
NOTE: Libref TEAMSITE was successfully assigned as follows:
      Engine:        ACCESS
      Physical Name: D:\Public\SharePoint\Public.accdb
3
4    proc sql noprint;
5    create table work.subset as
6    select * from teamsite.admins;
NOTE: Table WORK.SUBSET created, with 88 rows and 26 columns.

7    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.36 seconds
      user cpu time       0.04 seconds
      system cpu time     0.04 seconds
      memory              5330.31k
      OS Memory           13796.00k
      Timestamp           02/08/2018 09:22:39 AM

Using proc sql, we can also update/delete/insert rows.

Re: Post to SharePoint 2010 list

dwaynejarman — Sat, 07 Apr 2018 11:40:17 GMT

Thanks DaveHome, that is a good approach. Sharepoint will only allow 5,000 records in one view. So the approach in access will work if you are only working with a small list or want to connect to the data in the default view which is less than 5,000 records.

I built a macro to recursively pull all data available in a sharepoint list into a sas data table. From there I would like to query and update. One of my lists has over 10,000 records and using access as the intermediary would not work. I did some digging and it seems like the http 415 is unsupported media type. From looking at other posts it is possible that my code is pulling in the JSON as an object not as a string which sharepoint expects.

When I get back to work I will try posting a string to the server using the test above, so instead of calling the file I would replace in the code

in='{"cpId": 1}' Taken from

https://communities.sas.com/t5/SAS-Procedures/Passing-parameters-using-PROC-HTTP-POST-method/td-p/420514

So in this case, I would try:

in=‘{"Title": "I am not a robot","One": "A"}’

Again, this is just guessing that the file is being read in as a file or as a JSON object when SharePoint’s api expects a string.

If this works, a macro could be built to loop over data using proc sql and mass update records. It would be something like:
- query data
- build macro variables from query
- build strings to submit, including the data to change
- iterate through the macro variables to post the update

Again, the primary goal here (not stated above) is to be able to manage sharepoint data in large lists from sas.

Re: Post to SharePoint 2010 list

dwaynejarman — Sat, 07 Apr 2018 13:54:26 GMT

Another possibility to try :
filename submit "C:\temp\submit.txt" encoding="UTF-8";
data _null_;
length text $32767;
retain text '';
infile msghtml flowover dlmstr='//' end=last;
input;
text=cats(text,_infile_);
if last then call symput('submit',text);
run;

Then on the proc http:
in=&submit

Modified from https://blogs.sas.com/content/sasdummy/2016/07/12/how-to-read-the-contents-of-a-file-into-a-sas-macro-variable/