Using SPServices to export list items into a spreadsheet template

May 1, 2015 at 2:29 PM
I think I'm on the right track here, but need some guidance. I have a script that uses SPServices to parse through list items from my SP2013 list. I'm then trying to write that data into an existing Excel spreadsheet that's already formatted and ready to go.

I'm using the blog article below as reference, but I'm running into an issue when creating the ActiveX object. Has anyone on here successfully done this before?

http://spevilgenius.blogspot.com/2010/12/using-spservices-and-excel-to-create.html
May 8, 2015 at 1:11 PM
Edited May 8, 2015 at 1:24 PM
Jacob,

This is cool as I wrote that post! Do you receive an error when you try to create the object? If so, can you describe what it states the issue is? This has always worked for me up to Office 2010. I have not tried to do this on 2013 and may need to test it to see if it works. As long as your SPServices stuff is working, we may need to move this discussion. I am also in the process of moving those blog posts to a new site.

[Edit:] This also was an older post that I never had the chance to update. I had actually made this save the xml file to a document library. I am wondering if it is worth an attempt to test doing this in 2013 and seeing if there are other options that may be more practical

Dan
spevilgenius
May 8, 2015 at 2:46 PM
I found a later post where maybe this will be better. I realized that I may have posted an update!

http://spevilgenius.blogspot.com/2011/03/using-spservices-and-excel-to-create.html

Hopefully this will help!!

Dan
May 15, 2015 at 5:32 PM
Sorry for the late reply. After a little more digging, it looks like my error is occurring when it attempts to build the content for exporting. I get an error stating that "IndexOf" is an unsupported method.

I'll take a look at the updated link and see if that works any better. I'm skipping the part that adds it to the Actions menu, and jumping right into the meat of it. In my case, I have a button that's launching the PrintList() function directly from the onClick event.
May 15, 2015 at 10:12 PM
fyi: The "I" in IndexOf (in JavaScript) is usually lower case, as in: indexOf

Paul
-- sent from mobile
May 19, 2015 at 3:48 PM
Edited May 19, 2015 at 6:09 PM
@spevilgenius,

Same issue with the new code - it gets down to the part where it's building the content and then throws back a jQuery error for 'indexOf'

Image

Maybe this is something on my end? No idea what, though, and jQuery is working fine everywhere else on the same site (it's loaded through the master page).

Update1: After adding some console logging, it looks like the issue is with the strip function. My error occurs when calling this line in the main PrintList function:
camlQuery = stripcontents(xData.responseXML.xml, "Where");
This would appear to be either a problem with the script or with my implementation of it. It doesn't look like any kind of SPServices issue, though.

Update2: After taking apart the function piece by piece, it looks like it doesn't like the view GUID being passed, so my xData result is coming back undefined. However, I can't find documentation for the "GetViewHtml" operation here. Is that a default SPServices operations or something custom?
May 19, 2015 at 7:21 PM
Great question. I used SPServices solely in this case. I believe I used jQuery to get the selected view but I think that was based on a 2007 list view page and is not the same in 2010. I am not sure what code you used, but I believe in the updated blog post a month or 2 later, I may have changed it a bit. I may have just queried the list to get the views with a call to get the view collection which returns the names and guids. I then used the guid that corresponded to the view I was using to use the GetViewHtml function. I think that is documented on the SPServices doc links that take you to: here

Hope that helps!!

Dan
May 19, 2015 at 8:28 PM
Edited May 19, 2015 at 8:30 PM
At this point, I've stripped it down and I'm hard-coding the URL, view GUID, and CAML query/options into the function directly. The 'indexOf' error seems to be because I'm passing an undefined value to the strip function, so I'm attempting to ratchet it down even further.

At the moment, I'm skipping the GetViewHtml bit altogether and I'm just trying to return a valid list of items (via the next operation after GetViewHtml). It's telling me my xData is undefined, so I'm grappling with that. The highlighted line is the one throwing back an 'undefined' error:

Image
Coordinator
May 19, 2015 at 9:14 PM
Jacob:

xData.responseXML.xml will be undefined. You just need xData.responseXML. What you probably want to alert is xData.responseText.

M.
May 20, 2015 at 1:13 PM
Edited May 20, 2015 at 1:15 PM
At this point, I'm not sure what's going on. I've tried several variations but always the same error. I'm using SPServices throughout the site with no issues, so I know it can't be my environment. One thing I just noticed is that the error is coming from the jQuery script itself, not my script, which is weird. When I click the script link in the IE debugger console, it jumps to the following line in jquery-1.11.0.min.js:

Image

I'm not sure what that snippet is doing, and I'm not doing anything cross-domain. Everything is located locally on the site, using relative URLs wherever possible.
Coordinator
May 20, 2015 at 1:25 PM
I notice that your webURL is "http://sub.company.com". Are you trying to do a cross domain call? If not, then either you don't need the webURL (if the list is in the site where you currently are) or it should be just "/" to indicate the root site.

M.
May 20, 2015 at 1:53 PM
Edited May 20, 2015 at 6:10 PM
I had just changed that for the screenshot - in the script it's my webapp root URL. I'll try "/" instead and see if that helps.

Update: Awesome - now I'm getting list items back! I had no idea you could use a relative URL for the webURL parameter on the operation. I'm back to the original 'indexOf' error, which I'm pretty sure is because of what I'm passing to spevilgenius' strip function. It's definitely a huge step in the right direction, though - now I can verify what I'm passing. Thanks Marc!

Update2: Well, after changing "xData.ResponseXML" to "xData.ResponseText" throughout the script, plus a couple of other tweaks, I'm now getting an error about my spreadsheet template. So at least I'm getting pretty far now - enough that I can see the light at the end of the tunnel, at least.