Talking to Sharepoint Lists with Perl

ramblings

I’ve recently done some work to talk to Sharepoint with Perl and thought I would share my experiences. I couldn’t find any example code out there in the wild for doing this, so I had to figure a lot of this out by trial and error. It’s actually quite simple once you’ve got it set up. I hope this helps someone.

This code shows you how to connect via the Web Services interface with NTLM authentication (i.e. standard Windows authentication) to manipulate Lists, but you could do almost anything.

You will need:

  • SOAP::Lite for talking to Sharepoint Web Services interface
  • LWP::Authen::Ntlm to enable LWP to talk NTLM
  • Authen::NTLM which LWP::Authen::Ntlm uses for the NTLM authentication

Some information sources that you’ll find useful:

  • MSDN Sharepoint Web Services documentation
  • Go to /_vti_bin/lists.asmx on your Sharepoint server for a lot of useful information
  • Go to /_vti_bin/lists.asmx?WSDL for the WSDL definitions (if all else fails)

So here’s how to get started:

use LWP::UserAgent;
use LWP::Debug;
use SOAP::Lite on_action => sub { "$_[0]$_[1]"; };
import SOAP::Data 'name', 'value';
our $sp_endpoint = 'http://sp.example.com/sites/mysite/_vti_bin/lists.asmx';
our $sp_domain = 'sp.example.com:80';
our $sp_username = 'DOMAINusername';
our $sp_password = 'xyz';

The SOAP::Lite module needs to be told how to construct the SOAPAction header properly for Sharepoint. The on_action does just this, and means you’ll end up with a SOAPAction appending the URL and the method name together without anything in between (stops the default # that Sharepoint doesn’t want).

if ($debug) {
    LWP::Debug::level('+');
    SOAP::Lite->import(+trace => 'all');
}

Use the above code to turn on debugging if you get errors.

my @ua_args = (keep_alive => 1);
my @credentials = ($sp_domain, "", $sp_username, $sp_password);
my $schema_ua = LWP::UserAgent->new(@ua_args);
$schema_ua->credentials(@credentials);
$soap = SOAP::Lite->proxy($sp_endpoint, @ua_args, credentials => @credentials);
$soap->schema->useragent($schema_ua);
$soap->uri("http://schemas.microsoft.com/sharepoint/soap/");

This complete mess is the necessary steps to get SOAP::Lite to use a properly configured LWP UserAgent to do NTLM authentication. SOAP::Lite uses two UserAgents, one for the main SOAP calls and one for the Schema fetching. Although you don’t need to fetch a schema, I’ve included the proper set up above in case you want to call $soap->service(“$sp_endpoint?WSDL”); for some reason.

$lists = $soap->GetListCollection();
quit(1, $lists->faultstring()) if defined $lists->fault();

That’s all you need to do to get a list of all the lists on your Sharepoint site. And we can print them out:

my @result = $lists->dataof('//GetListCollectionResult/Lists/List');
foreach my $data (@result) {
    my $attr = $data->attr;
    foreach my $a qw/Title Description DefaultViewUrl Name ID WebId ItemCount/ {
        printf "%-16s %sn", $a, $attr->{$a};
    }
    print "n";
}

Or if you need to find a particular list to do operations on it, search for it in the results by looking up the Title with something like this:

sub lists_getid
{
    my $title = shift;
    my @result = $lists->dataof('//GetListCollectionResult/Lists/List');
    foreach my $data (@result) {
        my $attr = $data->attr;
        return $attr->{ID} if ($attr->{Title} eq $title);
    }
    return undef;
}

And here’s another useful subroutine to get all the items in a list:

sub lists_getitems
{
    my $listid = shift;
    my $in_listName = name('listName' => $listid);
    my $in_viewName = name('viewName' => '');
    my $in_rowLimit = name('rowLimit' => 99999);
    my $call = $soap->GetListItems($in_listName, $in_viewName, $in_rowLimit);
    quit(1, $call->faultstring()) if defined $call->fault();
    return $call->dataof('//GetListItemsResult/listitems/data/row');
}

That will use the default view. The 99999 is a hack to get all the items and stop the server “paging” the results. Putting this together you’d do something like this:

my $list_id = lists_getid('MyList');
print "List ID is: $list_idn";
my @items = lists_getitems($list_id);
foreach my $data (@items) {
    my $attr = $data->attr;
    # print Dumper($attr);
}

Here’s some code to add a new list item:

my $field_id = name('Field', 'New')->attr({ Name => 'ID'});
my $field_linktitle = name('Field', $title)->attr({ Name => 'Title'});
my $field_something = name('Field', $something_else)->attr({ Name => 'Something_x0020_Else'});
my $method = name('Method', [$field_id, $field_linktitle, $field_something])->attr({ ID => "anything", Cmd => 'New'});
my $batch = name('Batch', $method);
my $in_listName = name('listName' => $list_id);
my $in_updates = name('updates' => $batch);
my $call = $soap->UpdateListItems($in_listName, $in_updates);
quit(1, $call->faultstring()) if defined $call->fault();

The content for Name=”ID” must be “New”. Where it says “anything” it really can be anything, it’s just an identifier for responses. You can also see that spaces are encoded as _x0020_.

my $field_id = name('Field', $sp_id)->attr({ Name => 'ID'});
my $field_something = name('Field', $something_else)->attr({ Name => 'Something_x0020_Else'});
my $method = name('Method', [$field_id, $field_appname])->attr({ ID => $jira_name, Cmd => 'Update'});

The above is for modifying an item. In this case the $sp_id must be set appropriately from the “id” attribute of a list item you previously fetched.

I hope that helps someone. Perhaps one day someone can put the effort in to writing a module to do all this.

James

18 thoughts on “Talking to Sharepoint Lists with Perl”

  1. Trying to use your code but the line $soap->schema->useragent($schema_ua); is giving me the error: Can’t call method “useragent” on an undefined value at (filename.pl) line number.
    Also, I think you have a typo at line my @credentials = ($sp_domain, “”, $sp_endpoint, $sp_password);
    The $sp_endpoint should be $sp_username shouldn’t it?

  2. Yes you are correct the @credentials line should be $sp_username.

    $soap->schema->useragent call works for me. It would be nice to know why that doesn’t work for you. What version of SOAP::Lite are you using? Just skip the line, you probably don’t want to use $soap->service anyway.

    James

  3. Very interesting!!!

    Is it possible to upload a file to a directory using Perl? I couldn’t find any examples.

  4. I hear that sharepoint support WebDav.

    Not tried it yet, but fully intend to try it usingsome detaail I have seen on perlmonks

  5. I fought with the Perl WebDAV modules for quite a long time and ended up just generating temp files and calling curl from within my script to upload them. Most of my problems with WebDAV were related to getting the NTLM authentication working properly.

    curl -ntlm -u DOMAIN/loginid:password -T https://my.sharepoint.com/path/to/location/

  7. Ahh… very awesome!! Thank you so much for posting!! I’ve been struggling to get to the data on our MOSS sites via Perl, but have been stymied by credentials sometimes working, sometimes not. Your instructions helped tremendously!!

    The $soap->schema->useragent call didn’t work for me either, but that may be because I am using an “ancient” SOAP::Lite version… dated 2002. Prolly wouldn’t hurt to upgrade… 🙂

    Thanks very much!

  8. Hey James,

    This info is really useful… i’m almost there, but keep hitting the dreaded “401.1 Unauthorised” error.

    I’m running this code against an IIS 6.0 server and using the perl module Authen::Ntlm version 1.05. I noticed that you commented on a defect that is supposedly fixed in this version because you were having some problems accessing an IIS/60 server. (http://rt.cpan.org/Public/Bug/Display.html?id=9521)

    Could you provide more information about the patch you applied because I think i’m having the same problem and no matter what I do, it doesn’t change the outcome 🙁

    Thanks in advance for any help!

  9. Hi Andy – I completely forgot about that issue I had. I really should have mentioned that in my post. I simply did what meep suggested – replace:

    $domain = substr($challenge, $c_info->{domain}{offset}, $c_info->{domain}{len});

    with:

    $domain = &unicode($domain);

    If that doesn’t help, you’re going to have to turn on debugging on LWP and get the full headers and see if you work out what’s going wrong. Feel free to email them directly to me and I may be able to provide some insight.

    James

  11. Hey! thanks! I am going to use it. I needed this badly. I hope I will meet success, which is much needed.

    bbye
    Great Work!!!!

  12. Thanks for posting this info – Here are some things to add I learned while getting it to work on my Sharepoint server…

    I am using Perl 5.8.8 with SOAP::Lite 0.710.08 – all built from source – on CentOS 5.3

    1. Escape the backslash in $sp_username = ‘DOMAIN\username’;

    2. The update would NOT work with a Library Document Item – you need to supply the ID *AND* FileRef – uses spaces – not %20 – for example:

    $sp_ref = “http://sp.example.com/sites/mysite/My Library/file 1.txt”;

    my $field_id = name(‘Field’, $sp_id)->attr({ Name => ‘ID’});
    my $field_ref = name(‘Field’, $sp_ref)->attr({ Name => ‘FileRef’});
    my $field_something = name(‘Field’, $something_else)->attr({ Name => ‘Something_x0020_Else’});
    my $method = name(‘Method’, [$field_id, $field_appname])->attr({ ID => 1, Cmd => ‘Update’});

    FYI – FileRef was NOT required when updating a List Item

    3. Not sure what the $jira_name was set to in the example above – but all other examples (mostly VB/.NET based) I saw on the web had ID = 1 – this worked for me

    4. I also had to make the change in comment #9 above to get the authentication to work – using Authen::NTLM 1.05

  13. Just a note: I hacked for literally a day on this, getting the 401 error issue, until I changed the following line:

    my @credentials = ($sp_domain, “”, $sp_endpoint, $sp_password);

    to

    my @credentials = ($sp_domain, “”, $sp_username, $sp_password);

    Now it works great!

  15. I got this to work! As an old Perl hacker transformed into SharePoint hacker, I’m thrilled at the new(old) possibities. The .NET guys think I’m crazy, but I think the same about them.

    Doing more with less,

    @InvoiceGuy

  16. Thank you, this is very useful.

    My SharePoint is using HTTPS, so I had to change the the $sp_domain value to “my_domain.com:443”, and it worked well.

  17. I have this working to send form data to a SharePoint list. It is actually cached form data stored in xml for an offline client, but would be the same for a live scenario.

    I am using ActivePerl 5.10.1.1007

    I used ppm to install the following packages
    SOAP::Lite
    NTLM
    LWP

    I then created a subroutine, called as follows with a hash of the name->value form data:

    &sendFormSP(%data);

    In the subroutine the form data is loaded as follows:

    my $params = shift;
    my %data = %$params;

    Then I create dynamic SOAP variables for each of the fields and load onto an array:

    my @fieldvars = ();

    foreach my $k (keys (%data)){
    ${‘field_’ . $k} = SOAP::Data->name(‘Field’, $data{$k})->attr({ Name => $k });
    push(@fieldvars, ${‘field_’ . $k});
    }

    Finally the $method SOAP variable is assigned as follows.

    my $method = name(‘Method’=> @fieldvars)->attr({ ID => “anything”, Cmd => ‘New’});

    And then the final few lines of code same as in the original post.

    If anyone has any code to share please do so.

    Thanks,
    K

  18. Update for better error handling.

    In calling function:
    eval {
    &sendFormSP( %data );
    };

    if ($@) {
    if ( substr( $@, 0, 3 ) == 401 ) {
    $msg = “Incorrect password for $username.”;
    }
    else {
    $msg = “Error: $@”;
    }
    }

    In subroutine change following:
    quit(1, $lists->faultstring()) if defined $lists->fault();
    to
    die $lists->faultstring() if defined $lists->fault();

    quit(1, $call->faultstring()) if defined $call->fault();
    to
    die $call->faultstring() if defined $call->fault();

    return undef;
    to
    die “List does not exist.”;

    If anyone is working on this and has questions please post here or email.

Comments are closed.