Sharepoint with Python

When using Microsoft Sharepoint with python, you may need the ability for ntlm authentication when using requests to access data.  There is a python module that does exactly that – install requests-ntlm module.

https://pypi.python.org/pypi/requests_ntlm/0.2.0

Sharepoint Lists

It would be really cool if I could save a Sharepoint List to an XML file on a Unix server.  I see a bunch of articles but I can’t seem to get it to work.

First, go to the Sharepoint site with your list – in the list ribbon menu, click ‘Export to Excel’.  If you’re on a Windows machine, don’t open the file with Excel.  Save the file and then open it with a text editor.  On a Mac, you will likely get a message saying that you need a sharepoint foundation compatible app – click okay, and the browser should open the file you need.

Line 3 of the file is the url you need:

https://my.server.com/folder/group/_vti_bin/owssvr.dll?XMLDATA=1&List={list-number}&View={view-number}&RowLimit=0&RootFolder=%2fops%2fag%2fLists%2fSubdivisions

Using the url, here is the command to extract the xlm (all on a single line):

curl --ntlm -g -k -o file.html -u “user:password" https://server/path/site/_vti_bin/owssvr.dll?XMLDATA=1'&'List={list-number}'&'View={view-number}'&'RowLimit=0

Notes on this command line:

  • I’ve removed everything after &RowLimit=0 just because I wanted the minimum needed for the url.  It isn’t necessary otherwise.
  • You must enclose the & symbols in quotes.
  • You must include the -g flag.  This turns off the “URL glowing parser.”  I don’t know what this means, but it allows you to use square and curly braces ( {} [] ) in a url.
  • The ntlm flag enables Windows authentication.  I don’t know anything else about it.  I don’t think it’s necessary if you’re doing this on a Windows machine.
  • The o flag saves the file to the specified file name
  • The u flag contains login credentials.  In my case, I did not need to include the domain.  you may need to.

 Link to the curl manual.

Sharepoint: Adding Columns with Null Values

In Sharepoint, if you are trying to create a calculated value, you may need ‘isnull’ logic. In the case I was working on, had three columns:

  Year1_Spend
  Year2_Spend
  Year3_Spend

In a fourth column called “LRP_Total_Cost”, I wanted to add these three values. If each column had a number in it, the formula is easy:

=sum(Year1_Spend,Year2_Spend,Year3_Spend)

But if a column was blank, the formula wouldn’t work. One way to handle this is to have a default value of 0 (zero) in the column and to make sure all cells are populated.
However, from a user point of view, blank means zero and visually, not showing zero makes the output easier to read.
So I could set all my output screens and reports to hide the value if it is zero, or I can create a formula that assumes a blank cell is zero. I went with the formula:

=
 sum(
 (IF(ISNULL(Year1_Spend),0,Year1_Spend)),
 (IF(ISNULL(Year2_Spend),0,Year2_Spend)),
 (IF(ISNULL(Year3_Spend),0,Year3_Spend))
 )