How to Import SharePoint 2013 List Items to LibreOffice Calc

There are many blog posts that discuss how to use "Open Remote..." within LibreOffice to work with documents hosted in either a SharePoint 2010 or 2013 environment. This incredibly useful, but it leaves out how to interact with SharePoint list items, in the use-case of building reports or simply wishing to work with other spreadsheet-spliced data. For reference, this is a common feature between SharePoint and MS Excel, but does not appear in any discussions or documentation for LibreOffice. To solve this, a few steps are needed that are not difficult, but require knowledge of which features to use. For completeness, the following steps were successful using Brave browser and LibreOffice 7.3.7.2 on Ubuntu Linux 22.04.4. NOTE - this workaround approach was necessary, as ActiveX is not available in the Linux environment and is likewise being phased out (to my knowledge) in Windows environment as well. 

Things needed:

• User account and password with full rights to the desired SharePoint list.
• SharePoint list view that displays all needed fields.

Steps to import list items to LibreOffice Calc:

1)  View the desired SharePoint list as XML. This can be done by building URL like this

http://sharepoint.com/_vti_bin/owssvr.dll?Cmd=Display&List={E04E74B3-93F2-43C8-831E-28490AEF31A7}&View={c64759da-2241-45a8-9fea-3cedaa948687}&Query=*&XMLDATA=TRUE

Notes:

• The start of the link should correctly use either http: or https: as configured in your SharePoint environment. 
• If the desired list is within a sub-site, append the sub-site to the beginning - http://sharepoint.com/subsite/...
• The List and View GUIDs can be found editing the respective list view that contains all the desired columns or fields, then scrolling to the bottom of the settings and reviewing the "Web address for this mobile view:" section. Copy the respective string of characters to the proper place in the URL (List={} or View={}), placing the characters within the curly braces. NOTE - any uses of %2D should be replaced with a dash(-), as shown in the example.

List

View

2) Once the XML loads into the browser, right click within and select "Save as..."

3) Set the file type to "All Files", enter a file name within Name (ensuring that it ends with .XML), then save the file within the desired location. NOTE - This file will be needed in Step 5. Also, the browser may flag the file and prevent it from being downloaded. To continue, view the current downloads and select "Keep", to allow the XML file to be downloaded.

4) Within LibreOffice Calc, select Data, then "XML Source". NOTE it may be necessary to enable this feature by navigating to Tools, then Options, and enabling "Enable experimental features" under Advanced.

5) Click the folder button under "Source File" and select the XML file, saved from the browser.

6) Within "Ma to Document", select "ns3:row" (the fields underneath will all be selected). Set the "Mapped cell" to A1, using the selector to choose the A1 cell within Calc, then clicking the 

7) Click Import to complete the import. The fields can be updated and worked with as desired within Calc. 

The Community Information Age

Free Open Source Software (FOSS), when discussed, can lead to very polarized positions. There is a civil war among the open source community. Community-driven innovation has both the capacity to welcome creativity in an agile approach while also inviting diverse opinions, often oppositional. Paradoxically, the greatest strength of open source development is also its most formidable adversary. The most common appearance of such is when two FOSS-driven tools, built with the same objectives, are embraced not as equals, but as competitors. This can be seen with GIMP and Krita, which are graphical, illustrative tools that share many of the same features. Under close examination, each reveals strengths and weaknesses. Both take very different approaches to interface, workflow, and format. Yet each brings a value proposition which appeals to different artists. The trouble begins when these two “camps” single out the other as less valuable or less sophisticated. Instead of observing the use-cases for each tool, the respective communities exchange unpleasantries. Thankfully, this internal conflict does not extend to all FOSS. 

Putting aside the sibling rivalry within, FOSS does have the potential to be the future of software development. It is flexible, as anyone can use the ideas (and code) to create new things at will. It is powerful, leveraging more versatile frameworks than commercial counterparts. It is adaptive, not seeking to reinvent good ideas but to build alongside them, using components that already provide irrefutable value. This is why Google’s map service took over GPS. It’s why Kubernetes has captured application providers, who can offer their service without massive redevelopment. “If you can’t beat them, join them,” as the proverb maintains. 

It may seem tantamount to fantasy to suggest that FOSS will take the lead role for product development, but this can already be observed using content creators as an example. Artists, streamers, and video producers dominantly rely on FOSS tools for sharing broadcast needs, with Open Broadcast Software (OBS) being among the most popular. These tools are reaching a level of market permeation that could very well phase out much of the commercial product model. This shift could instantiate an “Information Age Part 2”, which I propose be dubbed “Community Information Age”. 

Why Microsoft Teams Will Become the New Intranet

Intranet technology has made quantum leaps over the last ten years, from simple information publishing and file collaboration to fully immersive, integrated platforms. As such, it has made an innovative shift into content management. Intranet platforms have raced to embrace socialization, making the intranet experience heavily content focused with rapid creation and high availability, seeking to ultimately engage information on an emotionally level. In following Microsoft this last year, they sought to unify all these concepts into one tool; A new flagship product called Teams (https://docs.microsoft.com/en-us/microsoftteams/teams-overview) has emerged, fast becoming the cornerstone of the Microsoft product line. Like SharePoint was once being developed as the hub for apps, connections, and integrations, the torch has been passed to Teams. 

The Transition

With the unexpected cultural and professional changes incurred this year from COVID-19, Microsoft fast tracked the vision behind Teams to serve remote working needs on a global scale. The tool virtually already replaces the need for email, network drives, physical phones, and finally, intranet-level information sharing and socialization. This year, Microsoft unveiled the planned release of the Home Site App for Teams, which allows fully embedding a SharePoint landing page directly within Teams to ensure the same experience between professional work streams (https://techcommunity.microsoft.com/t5/microsoft-sharepoint-blog/the-home-site-app-for-microsoft-teams/ba-p/1714255). Taking the concept one step further, Microsoft is developing integrations between Teams Search and SharePoint Search, allowing seamless handoff to SharePoint Enterprise Search Center pages. Not to be excluded would be the prompting at every corner to utilize "likes" and various emojis to encourage emotional investment in the content. The intended result is that Teams will become the one-stop-shop for all major informational exchanges. The move is bold, but logical and offers scalability unlike ever before. It is the future.

All the best to you, going forward into 2021! 

 -Nate

The Death of SharePoint? [UPDATED 3-18-2021]

SharePoint Right Now

For anyone comparing the history of SharePoint, the tool has evolved significantly over the last twenty years, from a very a simple file hosting tool to a powerful automation and content management platform. It can host web pages, collect data, and trigger condition-based workflow automation. It seemed for the last few years that SharePoint hit it's stride, guaranteed to continue forward for years to come. Versions 2016 and 2019 came around, yet an odd twist happened; Microsoft dropped hints that no other on-premises versions of SharePoint would be released. To date, no major developments have been announced for on-premises editions. This would confirm suspicions that any intent to keep SharePoint "in-house" will have a limited future. Extended support for version 2019 has already been set to expire as 7/14/2026 (https://docs.microsoft.com/en-us/lifecycle/products/sharepoint-server-2019). In more recent times, Microsoft announced the deprecating of the SharePoint 2010 Workflow Engine within Sharepoint Online, as well as a warning that the 2013 Workflow Engine will follow suit in the "near future". For reference, the 2013 Workflow Engine is the current standard of workflow operations within SharePoint Online. The intention is to force a shift over to the Power Apps suite. As it is, SharePoint as we know will undergo significant change over the next 5 years. 

***UPDATE 3-18-2021: I had the opportunity during GlobalCon5 to ask Mark Kashman (Senior Product Manager at Microsoft, Microsoft Lists and SharePoint in Microsoft 365) if any new SharePoint Server editions were being developed. He mentioned that Microsoft was not ready to talk about new SharePoint Server releases, but was working on something and expected to make an announcement later in the year. This does indicate that some flavor of "on premises" may be maintained past SharePoint Server 2019, but would have to be a hybrid of sorts, again noting that the SharePoint Workflow engine technology is being discontinued. 

The Future

First, SharePoint Online via Office 365 is still running the same technology as SharePoint 2013 enterprise edition. This is necessary, as SharePoint 2013 is still under extended support until 4/11/2023 (https://docs.microsoft.com/en-us/lifecycle/products/sharepoint-server-2013) and must provide migration paths between the two. Watching the new development trends and reading between the lines from MS Ignite 2020, I'm projecting the following changes:

• After SharePoint 2013 support formally expires, the SharePoint Online platform core will be upgraded to 2019
• SharePoint as a formal product will be maintained until SharePoint 2019 support formally expires, at which point, SharePoint will be diminished to a background service within Office 365
• New apps creating extended interfaces to work as "apps" within MS Teams and other popular high-integration services will be released to replace existing UI and UX from direct SharePoint access. The "Lists" app, "Forms" app, and similar apps already show some of the cards from Microsoft's hand. 
• LISTS = https://www.microsoft.com/en-us/microsoft-365/microsoft-lists
• FORMS = https://www.microsoft.com/en-us/microsoft-365/online-surveys-polls-quizzes

What This Means

SharePoint as a platform will likely not fully disappear, but I do expect the front end experience to vanish within the next five years and be assimilated within the Teams product. With the full integration of Search and AI within Office 365 technologies, it is only a matter of time of until the duplicate functions, once exclusively found within SharePoint, will be no longer needed. Scaled, unified connectors will instead be implemented. The high availability content creation features of SharePoint will remain, but it will no longer be branded as such. With these expected changes coming into focus, at the very least it means that the "SharePoint" specialization will evolve to embrace new integrated approaches, such as building data tools within the "DataVerse"(https://docs.microsoft.com/en-us/power-platform/admin/about-teams-environment) and implementing machine learning models through Syntex (https://docs.microsoft.com/en-us/microsoft-365/contentunderstanding/). This is not meant to be framed as "bad news", but rather inform of how the content management paradigm will expectedly shift. Knowing this, I sincerely hope that it gives the insight to plan for future scalability and build future-focused strategic technology plans. 

I wish you all the best as we approach 2021! 

-Nate

Search Options for Microsoft Stream

The Issue

If you've tried out Microsoft Stream within your Office 365 tenancy, then chances are you've begun to realize that its searching capabilities leave much to be desired (they stink). To be fair, the app offers searching across all videos, but only finds text matches and orders results by most views. This can be somewhat mitigated by organizing the videos into channels and then searching within, but again, results are surfaced by most views, rather than relevance to the search keywords. Do I expect YouTube class results? Certainly not, but it's next to impossible to find meaningful results without refiners who at least some basic algorithmic factors to match results. In my tests, results with vague transcript matches and more views were weighted higher than full text match results in the title, description, and transcript, but had no views, due to the poor searching mechanism. Every "discovery" feature of MS stream is focused on overall viewership, versus quality of metadata or interest. One more shortcoming is that the search results cannot be crawled or queried from SharePoint Online Search. More can be read in the official documentation:

SEARCHING: https://docs.microsoft.com/en-us/stream/portal-search-browse-filter

EXPLORING: https://docs.microsoft.com/en-us/stream/portal-explore-content 

One Workaround

A very simple approach, is to use an html input element to collect search term and apply simple javascript to concatenate a string from a search value, pointing to https://web.microsoftstream.com/browse and using the query string parameter "q" to set the search value. I tried using the built in SharePoint search web parts, but it always appends a extra "k" query string parameter, which breaks the string handed off to Stream. This works as a basic example, but again, is limited by Stream's lack of intelligence search options and compiling results based on popularity, rather then relation to the search terms. On the plus side, this does allow a formulated query to passed from SharePoint Online to Stream. See the following code example for this idea (which monitors keystrokes for a "return" or "enter" key press to execute the inline script):

<input type="text" onkeyup="javascript: if(event.keyCode === 13)window.open('https://web.microsoftstream.com/browse?q='+this.value,'openSearch');" />

Another Take

I looked for a simple way to use the same kind of search by query string parameter in the channel search, but it looks that Microsoft is using an asynchronous API call for channel search, different from result filtering by query string as with the general video search. In looking at it briefly, it seemed to be a JSON result that could be parsed and styled with a little work, but I didn't dig much further as the results will again be fueled by popularity.  

A Mix of Services

In the case of what I was designing, I found I could achieve the best of all my requirements for using Stream to buffer video, then using a SharePoint Online Asset Library, using the embedded video option, to create index-able records with metadata that would be accessible to Search. In this way, I can build pages and content around those results, using Stream only as host service, rather than a content platform. This proved to be the most effective of everything tried. 

Have you tried to tackle this challenge as well? What kinds of solutions did you try out? 

Using SPServices to Query Subsites

SPServices is really a fantastic JQuery library that is specifically designed to embrace SharePoint. My initial brush with it was positive, but then quickly put me into a difficult place to find the answer to (what I thought was) a simple question:

Can SPServices query subsite lists? 

Quick searches insisted the answer was "Yes!", but code suggestions did not prove successful. I found with some trial and error that I could query localized lists (Lists in the current site level) without any issue, but when adjusting the url for subsite lists, I would receive the xml response "Site does not exist".

To explain my scenario, I was attempting to use SPServices to reference a specific list and return a specific field from each submitted item. I narrowed my troubles to a single attribute called "webURL". Many forums (including the documentation I believe) shows the property as "webUrl", which does work and return localized list data. But only "webURL" (notice the case difference) returns data from subsite lists. After making this small, but significant discovery, my code took off!

Hope this helps someone!

<script type="text/javascript" src="jquery-1.7.2.js"></script>

<script type="text/javascript" src="jquery.SPServices-0.7.2.js"></script>

//THIS WOUlD NEED TO BE WRAPPED IN EITHER A FUNCTION OR HANDLER  

$().SPServices({

                operation: "GetListItems",

                async: false,

                listName: "[List Name]",

                webURL: "http://[site]/[subsite]",

                CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",

                completefunc: function(xData, Status) {

            //THIS IS A HANDY WAY TO CHECK THE RESPONSE AND CLARIFY FIELDS

                //alert(xData.responseText);

                      

                      $(xData.responseXML).find("z\\:row").each(function() {

                      

        var liHtml = "<li>" + $(this).attr("ows_field15") + "</li>";

        $("#taskItems").append(liHtml);

        

         });

        }

                                                

});

   

}

 

<ul id="taskItems" />

User Profile Synchronization Service - Another Way to Fix "Hung on Starting" Issue

COMMON GROUND

I know that many of us SharePoint administrations and developers have encountered the dreaded "starting" issue for the User Profile Synchronization (UPSA) service at least once in our careers. The truth is, the process became so terribly complicated with the adoption of Forefront Identity Manager(FIM) in SP2010. I've read gads of commentary empathizing the pains of setting this up, using it, and maintaining it, lending credence to the idea that it's almost not worth the struggle. However, if you CAN get it to work and stay so, it does offer some beneficial features to the user community for organizational information.  It does offer a solution for pulling attribute data straight from Active Directory and mapping it to SharePoint fields, which can be leveraged in a variety ways. I'm of the belief it was created with "good intentions" but VERY, VERY poorly constructed (My best guess is that Forefront and Microsoft were so completely thrilled when they finally got UPSA to work that they gave up attempting to make it friendly in any reguard). In my long and frustrating attempts to fix the latest problem (The service indefinately stuck on "starting"), I created the below map (Which also helps to illustrate how utterly crazy UPSA is). 

THE ISSUE

So as I promised, I did find an answer, after rougly a week of digging into how fix the "starting" trouble. I read through every forum and bit documentation I could find, trying to both understand as much as I could about this dreaded concept, and find a reason why it wouldn't work (I reviewed logs in Verbose mode from Windows, and both the SharePoint diagnostic and IIS logs via ULSViewer). According to the logs, I could see SharePoint attempting to work with FIM and would get so far as "ILM: Created Certificate". Following this, nothing would come from UPSA (Other SharePoint activities would continue on). No failures were reported. No errors were captured. It remained this way (I left it on "starting" for 24 hours) until I purposely terminated the service instance and killed the "UserProfileSyncSetupJob" in the timer queue. Each attempt would successfully create one Forefront certifcate (MMC snap in for "certificates", Computer Account) in the Trusted Root Authority folder, and everything I could find on the subject indicated there should be two within this folder when successfully completed, but noted they would be almost indistinguishable from each other, as both are named the same. I'd faithfully purged the one within the Trusted Root Authority folder each attempt. 

THE ANSWER

I'll caution you; there are some very interesting forum posts out there, offering some wild advice on how to resolve this issue, most of which suggest deleting and recreating UPSA. Please note - THIS IS LIKELY NOT NECESSARY. For my case, I finally discovered (With the help of an inspired person's comments off a related post) that certificates are created in both the Trusted Root Authority folder AND the Personal folder. The Forefront certs have to be deleted from both for the service to start, as FIM creates one cert in Trust Root Authorities AND another in Personal. 

THE END

I truly hope this helped someone, as this killed almost of a week of my time. If you should observe a collection of Forefront certs building up in the two folders, (IF UPSA is stopped) it is safe to purge these (The certs will be recreated, when successfuly restarted or "reprovisioned" as Microsoft likes to phrase it). Good hunting to you!