Exporting SharePoint Group Members to Excel Without PowerShell

I was at a client recently and was not allowed to run any powershell commands but needed a list of accounts that existed in a SharePoint Group in a table format.  I also was not allowed site collection administration permission.  I tried a few different options in which trying to manipulate the list view of the group and using Excel data connections to get back to SharePoint but no option worked very cleanly.

What I ended up using was a REST call to get the users and then downloading the XML response and opening it with Excel.  Use this link to learn about the available REST api’s for users & groups

Here are the steps….

1.  Get a client that you can use to test REST calls

2.  Construct the REST call to get a list of users by group

The structure looks like this:  https://siteurl/_api/web/sitegroups/getbyid(groupid)/users

  • To get the group ID simply navigate to the members page of your SharePoint Group and look at the number at the end of the URL  Here is the URL of my “Product Members” group:  https://concurrencyinc.sharepoint.com/sites/products/_layouts/15/people.aspx?MembershipGroupId=9

Here is my call:  https://concurrencyinc.sharepoint.com/sites/products/_api/Web/SiteGroups/GetById(9)/Users

  • We will see the users returned in the entry area of the response.

ExportSPGroup1

3.  Download the XML response

  • In the Advanced REST Client click Save as file and then Download in the response section.

ExportSPGroup2

4.  Change the file type

  • The file will download as a .text-plain file type.  Edit the filename and change it to a .xml file type.

ExportSPGroup3

5.  Open with Excel!

  • In Excel browse and pick out the new .xml file you created and select open this file as an XML table.

ExportSPGroup8

  • Take a second and look at your pretty data.

ExportSPGroup4

6.  Remove duplicates

  • The data comes across in a way that there are 2 rows for each user.  We can clean that up by removing the duplicates based on the login name.  First click anywhere in the imported table and under the Data tab click Remove Duplicates.

ExportSPGroup5

  • Click the Unselect All button then scroll down and check ns4:LoginName

ExportSPGroup6

  • That will remove your duplicate logins and you will have emails and logins that you can use whatever way you need.

ExportSPGroup7

 

Handy appendix?

I know this post is titled how to get a list without powershell but I wanted to just include this down here as this is an easier approach if you have the ability.  Here are the commands to get a list of users in a SharePoint Group via powershell.

  • Get-SPSite http://server/sites/yoursite | Select -ExpandProperty RootWeb | Select -ExpandProperty Groups | Where {$_.Name -EQ “group name”} | Select -ExpandProperty Users | Select Name, Email| Export-Csv c:\scripts\users.txt

Here is the command to do it with SharePoint Online

  • Get-SPOUser -Site https://contoso.sharepoint.com/sites/finance -Group “group name”

Handy links!

InfoPath 2013 for Office 365 ProPlus

InfoPath 2013 is sticking true to the continuing support statements that were made about the software by releasing a standalone version.  For anyone who was running the Office 365 ProPlus edition of Microsoft Office, it was not possible to also run InfoPath.  Thankfully this has now changed and the standalone version can run side-by-side with Office and is now available to download from Microsoft.

Click the image below to download

infopath_logo

I ran the install and everything worked great!

2015-09-08 09_01_20-Microsoft InfoPath 2013

Should I still be using InfoPath in my SharePoint or O365 projects?

To continue with a favorite consultant response unfortunately is “it depends…”  InfoPath is supported in Office 365 and is supported in SharePoint Server 2016 so it is not going anywhere soon.  This is a question that I hear a lot and I wanted to put down my high level recommendations.

  • Using InfoPath to edit list forms is just fine
  • Using InfoPath for short term form solutions is also ok
  • Using InfoPath for enterprise business process solutions or long term projects should be used as an exception

There are plenty other 3rd party or development solutions that would ensure better longevity.  If you want to discuss any unique scenarios, please reach out to us and we can work on a strategy that best fits your business needs.

Issues Moving SharePoint Access Apps Between Farms

I hit an issue recently that was a little tricky in regards to Access Services and SharePoint 2013.  (Not Access Services 2010)  The issue arose when saving an Access app that was created in a development farm and trying to publish it to production.  Here are the steps to create an Access app package for an on-premises Access app.  What was unique about this situation that moving apps in any other direction worked.  You were able to go Prod->Prod, Prod->Dev, Dev->Dev but Dev->Prod failed.  You were also able to create new Access apps in both environments.

When deploying the app to a site in the production environment these were the errors that were received…

————–

DacManagerPeer.Install: Exception thrown installing DAC. Exception {Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. —> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Internal Error.  The internal target platform type Sql110DatabaseSchemaProvider does not support schema file version ‘2.5’.     at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateExtensionManagerFromModel(SqlPackage package)

————–

DacManager.ExecuteDacOperation: DAC operation failed with exception {Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. —> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Internal Error.  The internal target platform type Sql110DatabaseSchemaProvider does not support schema file version ‘2.5’.     at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateExtensionManagerFromModel(SqlPackage package) 

————–

I had never heard of DAC as it relates to SharePoint (it’s not Dedicated Administrator Connection) before so I had to do some digging.  From the sound of the error it sounded like software was mismatched between environments.  I first double-checked all of the install tasks to ensure they were completed – Here is a great white paper on setting up Access Services.  While going through this I saw that one of the prerequisites referenced DAC, the Data-Tier Application Framework.

image

So I went and looked at both environments and sure enough there were multiple versions of the Data-Tier Application Framework across all servers (APP & SQL) in both environments.  There were versions 10 up to 12 with no standardization.  I then went ahead and installed the latest version of the DACFramework.msi file that I could find in the download center which was from June 22nd 2015 and version 13+.  I received the same error after this install so I started to dig deeper by going into the Access app package.  I first changed the extension of the app package to .zip.

image

And I found these files:

image

Great start, time to compare between apps build in Prod vs Dev!  At first glance nothing stood out between the manifest and .xml files, but then I started looking into the .dacpac file as I had never heard of this before.  Here is what I found:

  • A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.

It was now making more sense.  Access Services in 2013 uses an entire SQL DB per app you create.  So SharePoint is utilizing the logic behind the DAC framework to package and move databases between environments.  (This took software on the SQL server out of the picture).  But I must go deeper so lets open up this file…. was able to use the same logic as before and convert the extension to .zip to open it up.

image

Yay! More files to open and compare. I first looked at Origin.xml and nearly found what I was looking for.

Dev

image
Prod

image

Dev has a newer DAC version than Prod.  Ok is there anything else?  What is this schema error?  Opening up the model.xml file I found the answer.

image

Inside of the model.xml file existed the corresponding Schema for the DB provider that was throwing the error.  And sure enough apps created in Prod had Schema Version 2.4.  This made sense but I had installed the latest DAC version on both environments?  What I found out is that installing the newest DAC framework version 13.0.2993.2 does not upgrade the previous version.  I then remove everything I can find for older DAC versions and give it a shot.

image

Another error? Come on!  At least I got a correlation ID.

————–

DacManager.ExecuteDacOperation: DAC operation failed with exception {Microsoft.Office.Access.Services.Design.TemplateException: Sorry, an app package could not be created.  

————–

Well that didn’t appear too helpful.  The next shot I took was a guess that the version I installed was too new.  So I installed the Data-Tier Application Framework August 2013 version 11.1.2902.0 and completely uninstalled version 13.0.2993.2.

image

SUCCESS!  I was then able to successfully add an app that was created in the Dev environment to Prod.  Mission completed, let’s load this app and move on…..(queue up loading music)……Error…..dang……

image

————–

We weren’t able to load the app’s home page. Please let your administrator know about this.

————–

Not helpful, onto ULS logs and actually some helpful info.

————–

The application’s system schema version is newer than what the current version of Access Services understands. AppVersion: 15-0-61-0. ServerVersion 15-0-59-0.

An error occurred reading application properties while opening a command scope. Microsoft.Office.Access.Services.VersioningException: Sorry, the server doesn’t recognize the version of the app you are trying to open.

————–

Alright so more discrepancies between farms.  What I found on dev was a hotfix applied KB2956180.  I knew this had been there as this was the first place I looked when we had server mismatches at the beginning.  I didn’t believe that this was vital as the overall version of Access Services was identical between farms and the KB was a lower build number.   Reviewing the KB though had a lot of direct ties into Access Services.  This was also a large hotfix (154MB) and required PSConfig to be ran.

image

I went ahead an installed this hotfix on all SP servers and ran PSconfig on all servers.  I then opened the app that had threw an error earlier and the sky’s opened up with a wonderful ray of light and I saw my Access app.

image

I certainly hope this post will save someone some time as it was quite an adventure.  The final breakdown and advice of this post:

  1. Ensure the same version of DACFramework.msi is installed across your farms
  2. Ensure you only have 1 version across your farms
  3. Do not use version 13.0.2993.2 of DACFramework.msi
  4. Ensure hotfixes (even if lower build numbers) are matched between farms

Happy SharePoint troubleshooting!

SharePoint 2013 PowerPivot Install – Error Deploying Farm Solutions

I ran into an issue recently that I thought would be good to pass along. I was installing the BI features for SharePoint 2013 on a new farm and ran into an issue when configuring PowerPivot. This environment is a 3 tier environment as depicted in the image below.

2015-07-15 13_24_45-Document1 - Word

These are the BI steps that I had already completed. (along with some helpful links)

Here is the download link for SQL Server 2014. The installer does not deploy or configure Power Pivot features in SharePoint. The following components install by default:

  • Power Pivot for SharePoint 2013. This component includes:
    1. PowerShell scripts (.ps1 files)
    2. SharePoint solution packages (.wsp)
    3. Power Pivot for SharePoint 2013 configuration tool to deploy Power Pivot in a SharePoint 2013 farm
  • Microsoft OLE DB Provider for Analysis Services (MSOLAP).
  • ADOMD.NET data provider.
  • SQL Server Analysis Management Objects.

A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the Power Pivot for SharePoint 2013 configuration tool. When you install spPowerPivot.msi you can customize the installation by excluding individual components.

The next step is to configure the PowerPivot for SharePoint Add-in

This is the area in which I ran into an issue.  Here is the technet article about the configuration.

  1. I launched the configuration toolimage<
  2. I set the default account information, DB Server and PowerPivot infoimage
  3. I set the parameters for the task “Create PowerPivot Service Application” to use my naming format.  (Remove the GUID from the DB)image
  4. I set the web application deployment location for the task “Deploy Web Application Solution”image
  5. I set the unattended account info for the task “Create Unattended Account for Data Refresh”image
  6. I then clicked Validate and Run

The configuration failed on the task “Deploy Farm Solution” with the error:

Running PS Script failed. Reason: Solution failed to deployed, reason: SPAPP01 :
Error: Cannot add the specified assembly to the global assembly cache: Microsoft.AnalysisServices.SPAddin.dll.

The solution DID deploy to SPWEB01 and SPAPP02.

These were the items I tried to debug first all of which failed:

  1. IIS Reset on all servers
  2. Reset Timer Service on all servers
  3. Ran Configuration tool as Administrator
  4. Set default account to different account names with different permissions (farm/install/service/etc…)

I then stumbled across this blog – Troubleshooting PowerPivot for SharePoint Solution Deployments – which lead me into the correct direction based on this statement:

“…the Timer service depends on the Administration service to carry out the deployment work because the Timer service usually runs under a low-privileged service account that might not have the required permissions to deploy assemblies in the Global Assembly Cache and so forth, while the Administration service uses the high-privileged Local System account with full access to all local resources.”

So I head out to SPAPP01 and check out what account is running the Administration service and sure enough it was set to the fully qualified farm domain account!

I reset the log on to be Local System and reran the configuration.

image

Success!

image

Hopefully this helps someone else moving forward!