{"id":323,"date":"2016-06-24T16:51:52","date_gmt":"2016-06-24T14:51:52","guid":{"rendered":"http:\/\/le-moulin-de-verre.com\/fieldnotes\/?p=323"},"modified":"2016-06-24T16:51:52","modified_gmt":"2016-06-24T14:51:52","slug":"list-sharepoint-documents-through-rest-api-and-python","status":"publish","type":"post","link":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/?p=323","title":{"rendered":"List Sharepoint documents through REST API and python"},"content":{"rendered":"

Albert uses Sharepoint 2013 to store several GB of documents.<\/p>\n

Albert needs to be able to list and possibly extract the documents in a comuter readable format, like ls<\/code> would do on Unix. Of Course, he is a bit in a rush and has nothing but a corporate laptop without much privileges to do the job….<\/p>\n

To make things simpler, although the Sharepoint site is hosted by his company, he has only a standard user web access and no specific provilege on the server or a powershell environment that would allow to interrogate it.<\/p>\n

What’s next ?<\/p>\n

Using Python, we can quite simply rely on Sharepoint REST API to perform basic queries.<\/p>\n

Setup<\/h1>\n

Use winpyton<\/h2>\n

A portable Pyhton<\/h2>\n

Installing Python 3 and needed libraries may be tricky in a corporate environment, in particular when the web is accessed through a NTLM proxy that also rewrites SSL certificates.<\/p>\n

I ended up installing Winpython (portable environment that bundles a recent PIP version).<\/p>\n

using pip to access pypi.org behind a proxy<\/h3>\n

By default something like pip --proxy=http:\/\/user:pass@proxyAddress:proxyPort<\/code>
\nshould do the trick.
\nI rely on a local CNTML proxy to authenticate my requests to the corporate proxy, as this avoid having to manage the account\/password\u00a0 in the commands
\npip --proxy=http:\/\/localhost:3128 install my_special_package<\/code><\/p>\n

However in my case the proxy rewrites SSL certificates and sign them with a corporate cert. I need to tell pip to ignore pypi.org certificate validation.<\/p>\n

C:\\WinPython-64bit-3.4.4.2\\scripts>pip --proxy=http:\/\/localhost:3128 --trusted-host pypi.python.org install my_special_package\n<\/code><\/pre>\n
Ideally we should be able to tell pip to trust the corporate certifcate (did not work in my case though, possibly due to a problem of cascading certs.):<\/p>\n
pip --cert \/etc\/ssl\/certs\/FOO_Root_CA.pem install my_special_package\n<\/code><\/pre>\n
Installing dependencies<\/h3>\n
We will also need to authicate through NTLM to access the Sharepoint server(basic auth as shown in most online sample does not work here, we get error 401).<\/p>\n
pip --proxy=http:\/\/localhost:3128 --trusted-host pypi.python.org install requests\n# requests_ntlm package to authenticate http requests\npip --proxy=http:\/\/localhost:3128 --trusted-host pypi.python.org install requests_ntlm\n# sharepoint lib... that did not work though\npip --proxy=http:\/\/localhost:3128 --trusted-host pypi.python.org install sharepoint\n<\/code><\/pre>\n
the script<\/h1>\n
import requests\nimport json\nfrom requests.auth import HTTPBasicAuth\nfrom requests_ntlm import HttpNtlmAuth\nimport getpass\n\n# 24-06-2016 - olivier de Meringo... and a lot of nice guys on the web.\n#\n# Experimental listing files and directories and some metadata from a sharepoint 2013 site.\n# More or less like ls-al on sharepoint where you only have non-admin access (i.e. only REST API).\n\n\n# ITEMS = \"http:\/\/mysite.corp\/site\/subsitename\/_api\/lists\/getbytitle('ESD repository')\/items\"\n# SITE = \"http:\/\/http:\/\/mysite.corp\/site\/subsitename\/_api\/web\/getfilebyserverrelativeurl('\/')\"\n#USER_FOLDERS= \"http:\/\/mysite.corp\/site\/subsitename\/_api\/web\/getfolderbyserverrelativeurl('\/site\/subsitename\/Documents')\/Folders\"\n\nMYDOC_REPOSITORY = \"http:\/\/mysite.corp\/site\/subsitename\/_api\/web\/getfolderbyserverrelativeurl('\/site\/subsitename\/Documents')\"\n\n\n# User name with nt domain in case of NTLM (USERNAME = \"mydomain\\\\mylogin\")\nUSERNAME = \"mydomain\\\\mylogin\"\n# prompt password\nPASSWORD = getpass.getpass('Password for '+USERNAME+':')\n\n\n\n\ndef get_json_ntlm(query):\nheaders = {'accept': 'application\/json;odata=verbose'}\nr = requests.get(query, auth=HttpNtlmAuth(USERNAME,PASSWORD), headers=headers)\nreturn r\n\n# display json in human readable format\ndef pp_json(json_thing, sort=True, indents=4):\nif type(json_thing) is str:\nprint(json.dumps(json.loads(json_thing), sort_keys=sort, indent=indents))\nelse:\nprint(json.dumps(json_thing, sort_keys=sort, indent=indents))\nreturn None\n\n\n\ndef print_files_recursivelly(folderURI):\n# get current folder as json\nfolderJson = get_json_ntlm(folderURI).json()\n\n# gets the URI that describes the files of this folder\nfilesURI = folderJson[\"d\"][\"Files\"][\"__deferred\"][\"uri\"]\nprint_files_detail(filesURI)\n\n# Continue on subfolders\nsubURI = folderJson[\"d\"][\"Folders\"][\"__deferred\"][\"uri\"]\nsubfolders = get_json_ntlm(subURI).json()\nfor sub in subfolders[\"d\"][\"results\"]:\nprint_files_recursivelly (sub[\"__metadata\"][\"uri\"])\nreturn None\n\ndef print_files_detail(filesURI):\nfiles = get_json_ntlm(filesURI).json()\nfor afile in files[\"d\"][\"results\"]:\nprint(afile[\"Name\"]+ \";\"+ afile[\"ServerRelativeUrl\"]+\";\"+ afile[\"Length\"] +\";\"+ afile[\"TimeCreated\"] +\";\"+ afile[\"TimeLastModified\"] +\";\")\nreturn None\n\ndef print_folder_detail(folderJson):\n# print it's name and relative URL\nprint ( \"{0} ({1} child(s))\".format(folderJson[\"d\"][\"ServerRelativeUrl\"], folderJson[\"d\"][\"ItemCount\"]))\nreturn None\n\ndef print_folder_hierarchy(folderURI):\n# get the folder as json\nfolderJson = get_json_ntlm(folderURI).json()\nprint_folder_detail(folderJson)\n\n# gets children files\n# filesURI = folderJsonRes[\"d\"][\"Files\"][\"__deferred\"][\"uri\"]\n# print_files_detail(filesURI)\n\n# get subfolders uri\nsubURI = folderJson[\"d\"][\"Folders\"][\"__deferred\"][\"uri\"]\nsubfolders = get_json_ntlm(subURI).json()\nfor sub in subfolders[\"d\"][\"results\"]:\nprint_folder_hierarchy (sub[\"__metadata\"][\"uri\"])\nreturn None\n\nprint_folder_hierarchy(MYDOC_REPOSITORY)\nprint(\"-------------------------------------------------\")\nprint_files_recursivelly(MYDOC_REPOSITORY)\n\n<\/code><\/pre>\n
quick notes – improvements<\/h1>\n
I should rather use python-sharepoint library<\/h2>\n
Python-sharepoint library is expected abstract the REST API but I did not manage to use it, possibly due to a problem with NTLM opener.<\/p>\n
See [https:\/\/github.com\/ox-it\/python-sharepoint\/issues\/3]<\/p>\n
Use CAML to filter queries<\/h2>\n
REST API can use CAML to reduce and optimize queries. However, it will requiere that REST queries are send through POST (instead of GET) which may conplexify the code…. but definitly somehing to explore.<\/p>\n
See
\n– [http:\/\/sharepoint.stackexchange.com\/questions\/78612\/why-do-i-have-to-use-post-for-rest-queries]
\n– [https:\/\/chuvash.eu\/2014\/03\/25\/using-caml-with-sharepoint-rest-api\/]<\/p>\n
Json format reference for files and folders<\/h2>\n