{"id":4583,"date":"2019-02-08T18:18:40","date_gmt":"2019-02-08T23:18:40","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4583"},"modified":"2019-02-08T18:18:40","modified_gmt":"2019-02-08T23:18:40","slug":"did-you-know-you-can-perform-crud-operations-on-sharepoint-lists","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4583","title":{"rendered":"Did you know \u2026 you can perform CRUD operations on SharePoint Lists?"},"content":{"rendered":"

Not crud like \u201cugg, that\u2019s a bunch of crud \u2026 let\u2019s load it up in a SharePoint list to store it forever!\u201d \u2013 that wouldn\u2019t make sense at all. In programming-speak, CRUD is an abbreviation for Create, Update, Read, Delete \u2013 the basic types of operations for data storage. And you can<\/em> create, update, read, and delete SharePoint list items through the REST API.<\/p>\n

First, you\u2019ll need a list. Here, I am using a sample list that has columns for SiteID, MailingAddress, City, State, and ZipCode \u2013 the usual information if you\u2019re going to use a LOOKUP column to correlate a location in a record with address details for the location (i.e. there\u2019s no reason to type 1925 Enterprise Parkway and such in every<\/em> order you want to ship to the Twinsburg office).<\/p>\n

<\/p>\n

And you need some sort of code that communicates with the REST API \u2013 something that sends HTTPS calls. In the example code, I am using Python. Functions, along with example code to use<\/em> those functions can be found at https:\/\/github.com\/ljr55555\/spoRestAPICRUD<\/a>. Clone the repository locally.<\/p>\n

You will find a config.sample \u2013 I use this as a template for storing user-specific configuration parameters. Copy config.sample to config.py and edit config.py. The actual config.py is included in the .gitignore file, so retrieving updates from the repository won\u2019t wipe our your settings.<\/p>\n

There are a handful of values you will need to set. Most of the values you can get from your list\u2019s web address. Open your list in the web browser of your choice and find the information in the address line:<\/p>\n

<\/p>\n

There are three values we need to extract from this URL \u2013 the SharePoint tenant address<\/span>, your SharePoint site name<\/span>, and the list name<\/span><\/p>\n

<\/p>\n

Edit config.py and modify the following variables with your list-specific information<\/p>\n

strConnectURL = “tenant.sharepoint.com<\/span>”<\/p>\n

strContextURI = “https:\/\/tenant.sharepoint.com<\/span>\/sites\/SiteName<\/span>\/_api\/contextinfo”<\/p>\n

strListInfoURI = “https:\/\/tenant.sharepoint.com<\/span>\/sites\/SiteName<\/span>\/_api\/web\/lists\/GetByTitle(‘ListName<\/span>‘)”<\/p>\n

Then you need some credentials \u2013 this config file will need to be updated when the account password changes, so you may wish to use a non-user account with a very long password that changes less often.<\/p>\n

Obviously, typing a username and password in clear text is a bad idea. I\u2019m using simplecrypt<\/a> to keep an encrypted password in the config file which is decrypted using a key in the script file. Anyone who obtains both<\/em> files can decrypt the password \u2013 in my production code, the key comes from another location to reduce the probability of someone accessing the key file.<\/p>\n

Use stashStringForConfig.py to generate the string to use for the username and password values \u2013 change strKey to match whatever you are using for your key, and change strString to your user id. Run the script and copy the output into your config.py file. Change strString to your password and repeat the process.<\/p>\n

C:\\ljr\\git\\spoRestAPICRUD>python stashStringForConfig.py<\/p>\n

b’c2MAAnHWW1nqXuc4bO+pt8q1FjTG6Q5CYNz1O5ORHnJxl8vBOpGKj0HxVSYdGa1o+Ij\/VicrQLTWTyU7P0StspMEJ7zBe\/qtFWuHGrfEvnLO5dU=’<\/strong><\/p>\n

That\u2019s it for configuration \u2013 at this point, if you have a list with the same columns I do, you can run the script.<\/p>\n

<\/p>\n

Voila, records!<\/p>\n

<\/p>\n

What\u2019s the script doing? Well \u2013 CRUD, of course!<\/p>\n

Connecting To SharePoint Online \u2013 <\/em><\/strong>I am using a modified version of sharepy which can be found in the develop branch of my fork of the repository<\/a>. This is a requests wrapper that handles authentication to SharePoint Online. The connection, in my script, is named spoConnection. The arguments supplied are sourced from config.py<\/p>\n

spoConnection = sharepy.connect(strConnectURI,strUID,strPass)<\/p>\n

Creation<\/em><\/strong> \u2013 You need a dictionary with your data. The required metadata type value is retrieved from your list. The remaining key:value pairs in the dictionary are the column names and record values, respectively.<\/p>\n

{“__metadata”: { “type”: strListItemEntityTypeFullName}, “Title”: “Bedford Office”, “SiteID”: ‘123456’, “MailingAddress”: “17500 Rockside Road”, “City”: “Bedford”, “State”: “OH”, “ZipCode”: “44146”}<\/p>\n

The writeNewRecord function will insert the record into your list. The dictionary containing my record is called strBody (because it ends up being the HTTP POST body).<\/p>\n

iNewRecordResult = writeNewRecord(spoConnection, strContextURI, strListDataURI, strBody)<\/p>\n

Read<\/em><\/strong> \u2013 Now that we have records, we can retrieve the full list or filter to find specific records. To find all records, run findSPRecord \u2013 the arguments are the SharePoint connection and the URI for the list.<\/p>\n

jsonResult = findSPRecord(spoConnection, strListDataURI)<\/p>\n

If you want to return a filtered subset of data, add the column on which to filter, the filter operator, and the value. You can construct more complex ODATA filters \u2013 see the ODATA query operations supported in the SharePoint REST API<\/a> for more information.<\/p>\n

jsonResult = findSPRecord(spoConnection, strListDataURI, “SiteID”, “eq”, “234567”)<\/p>\n

Update<\/em><\/strong> \u2013 I intentionally included incorrect data in one of my create lines \u2013 the Twinsburg office isn\u2019t in Rochester NY! To update a record, you need it\u2019s internal ID number. The findSPRecordID function has the same parameters as findSPRecord, but instead of returning the full record, it returns the integer record ID.<\/p>\n

iRecordToUpdate = findSPRecordID(spoConnection, strListDataURI, “SiteID”, “eq”, “345678”)<\/p>\n

Now that we have a record number, we also need a dictionary with the new values. Values that are not<\/em> changing do not need to be included \u2013 just anything value you want to update. As with the record creation, the metadata type is determined programmatically.<\/p>\n

dictRecordPatch = {“__metadata”: { “type”: strListItemEntityTypeFullName}, ‘Title’: “Rochester Office”}<\/p>\n

And then updateRecord is called to write the new information into the selected record.<\/p>\n

iRecordPatchResult = updateRecord(spoConnection, strContextURI, strListDataURI, dictRecordPatch)<\/p>\n

Delete<\/em><\/strong> \u2013 Delete operations are similar to update operations \u2013 you need to find the internal record ID number to delete it. There\u2019s no validation \u2013 nothing checks that the City for item #x is<\/em> Rochester.<\/p>\n

iDeletionResult = deleteRecord(spoConnection, strContextURI, strListDataURI, iRecordToDelete)<\/p>\n

By combining CRUD operations, you can use a SharePoint list as a user-created and user-administered database. SharePoint still stores its information in a Microsoft SQL database, and going through the REST API to interact with your data adds overhead \u2026 so this isn\u2019t a good approach for someone with an enormous data set where views would speed up data access or complex join operations are warranted. But for someone with fairly straight-forward database requirements, you may be able to do-it-yourself using SharePoint lists.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Not crud like \u201cugg, that\u2019s a bunch of crud \u2026 let\u2019s load it up in a SharePoint list to store it forever!\u201d \u2013 that wouldn\u2019t make sense at all. In programming-speak, CRUD is an abbreviation for Create, Update, Read, Delete \u2013 the basic types of operations for data storage. And you can create, update, read, …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,677],"tags":[415,747,750],"class_list":["post-4583","post","type-post","status-publish","format-standard","hentry","category-coding","category-office-365","tag-sharepoint","tag-sharepoint-online","tag-sharepoint-rest-api"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4583","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4583"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4583\/revisions"}],"predecessor-version":[{"id":4589,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4583\/revisions\/4589"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}