query Sharepoint O365 (online) User Profile Data

I'm looking for a way to query Sharepoint O365 (Online) User Profile data from within an Access 2013 application.

I have created  a Web App on our O365 Sharepoint (Online) that includes a Sharepoint  Azure Sql Server  backend (as opposed to lists). I have also created an Access desktop application, for data maintenance and reporting,  that links to the same Azure backend . The web app uses the Sharepoint user's login  Email and other info located in the Sharepoint User Profile to authenticate permissions, and limit data. Before a new hire can use the web app, their Sharepoint login email and other User Profile data must be added to the app's Azure backend User table.

It has been suggested that I download User Profiles to a csv file via Powershell and CSOM. I have no experience with Powershell or CSOM, but from my research, I'm pretty sure I can call Powershell and run a CSOM script via vba. The various script examples that I have found seem to require Global Admin privileges to succeed. The users that maintain the data will not have admin privileges, so this seems to be a roadblock to that method.

My expertise is in Access (mostly vba) with Sql Server backend, and I am just getting started with Sharepoint. I'm hoping to find a way to link to the Sharepoint User Profile table to the Access Desktop app in the same way that I link to the apps backend Azure tables, so I can then write vba code that will allow the users to add new hires to the app's Azure backend User table.

Is there a way to accomplish this?

Or, if not via the desktop app, is there a way to link to or otherwise get the User Profile data via the existing Web App, or a new Web App?

Thanks!


September 7th, 2015 2:58pm

Hi,

According to your description, my understanding is that you want to query Office 365 user profile data in access desktop app or access web app.

Per my knowledge, we can get the Office 365 user profile data using PowerShell CSOM like the article below:

Office 365 - Retrieve User Profile Properties using CSOM with PowerShell

Then in access, you can use shell function to call the PowerShell script, you can refer the similiar thread below:

Calling a Powershell Script from VBA in Access

Thanks

Best Regards

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 9:14am

Hi Jerry,

I really appreciate your help!

After installing the latest version of Powershell, I am almost able to accomplish this task. I have a few questions that I haven't figured out yet, and could use some more help:

1) The lines in the CSOM code that retrieves the password and adds it to the credentials requires user input in Powershell.

$Password = Read-Host -Prompt "Please enter your password" -AsSecureString
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

I am calling Powershell from Access VBA and the user does not see the Powershell window. so I need to hard code the password.

I've tried: $Password = "mypassword"  -AsSecureString

That returns an Unexpected Token '-AsSecureString' error.

So I tried: $Password = "mypassword"

That returns a Cannot convert argument for SharepointOnlineCredentials to type System.Security.SecureString error.

How do I hard code the password?

Or, if the user is already logged into Sharepoint with admin privileges, is there a way to use their current login within this code?

2) The sample code that you pointed me to, and similar code that I have found show the user field example as: $User = "admin@tenant.onmicrosoft.com"

The credentials that I am using are: $User = "me@tenant.com"

The Sharepoint admin has issued me admin rights, and I am able to retrieve some user profile data. However, when I try to retrieve some custom fields that I am told exist, I see no data. Would my using a "tenant.com" as opposed to a  "onmicrosoft.com" login limit the fields that would be returned?

Thank you so much for your help!

September 11th, 2015 10:54am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics