Tag: sharepoint

Did you know … you can add manager approvals in Microsoft Flow?

In my previous post, I demonstrated how the Office 365 User search could be used to record a Form responder’s email address instead of their logon ID. But other information about the user is available too – including their manager. This means you can use Flow to build workflows that require manager approval where “manager” is dynamically determined. How?

Select the “Get manager (V2)” action from the “Office 365 User” connector.

In this example, I am extending the sample workflow that creates a SPO list item from a Forms submission. I have inserted a step after getting the responder’s profile information. Click in the “User (UPN)” and select the input that contains the responder’s account (which, again, Forms calls an e-mail address)

Insert a new action

Search for and select “Approvals” and then select the “Start and wait for an approval (V2)” action.

Click the drop-down for “Approval type” – since I am only adding a single approver, the first two options are essentially the same. I can, however, use “Everyone must approve” to have more than one level of manager required for approval, or a manager plus static application owner.

To assign the approval, find the dynamic content from the manager lookup and select “Mail” from that section. Add details that will appear in the approval notification – something to clearly explain what the person is approving.

Save your workflow. Now when a Form response is submitted, the responder’s manager will receive an e-mail which allows them to approve or reject the request.

If they use the Flow site, they will see the approval in their Approvals section.

 

Did you know … you can store Microsoft Forms responses in a SharePoint Online list with Flow?

Microsoft Forms is a great way to compose simple forms and gather responses but analyzing the data in Excel … well I generally prefer processes to be automated. To use Forms data in automated processes, I store the form response in a SharePoint Online (SPO) list. How do you get the form response into a SPO list? Microsoft Flow.

Note – Microsoft Flow cycles are not free. We receive a number of free cycles each month for each Office 365 license purchased. The company-wide number of free flow cycles is quite large, but not infinite. Be cognizant of what constitutes a cycle. In this case, a cycle is each new form submission … if I expect 20,000 people to submit the form this month, I am using 20,000 flow cycles. Not all trigger cycles are as straightforward as this. As an example, Flow could send me an alert when I receive an e-mail from my manager. A cycle is not triggered on “e-mail from my manager received in my mailbox” but rather “new e-mail message received in my mailbox”. For a mailbox dedicated to a specific purpose, the number of messages received per month may be exactly what you want to process. In my mailbox, system alerts, ITSM notifications, script results and such would quickly use up a significant portion of Windstream’s free cycle allotment.

First you need a Form. When editing your form, grab the URL and find the “FormID” section. Save that string – we’ll need that in the workflow.

Before you start – if you plan to convert your personal form into a group form, do that before creating the Flow workflow. The form ID changes when a form is moved between group and personal forms, and you will need to adjust the workflow accordingly.

To store information in a SharePoint Online list, you’ll also need a SPO list. You can create one in a dedicated SPO site or the SPO site behind a Teams space.

Record the SPO site address and list name – we’ll need that in the workflow.

Now that we’ve got all of the components ready, we can build a workflow to connect them. Log into https://flow.microsoft.com

On the left-hand navigation bar, click “My Flows”.

Click the drop-down next to “New” and select “Create from blank”

Click “Create from blank” to confirm your choice.

Before you do anything else, give your Flow a descriptive name. Click on “Untitled”, type something else, and hit enter. If you don’t name your flow, it will get a default name based on the actions taken within the workflow … which makes it really hard to differentiate between five different workflows that take Forms responses and store them in SPO lists.

The trigger for the workflow will be that a new Forms response is submitted.

Select the “When a new response is submitted” trigger.

Select “Enter custom value” and paste in the form ID we recorded earlier.

Click “+ New step” to add a new step.

To use the data from the Form submission, we need to get the response data. Enter “Forms” again and select “Microsoft Forms”

Select the “Get response details” action.

In “Form Id”, enter a custom value and paste in the Form ID. Click in the “Response Id” field. In the right-hand navigation menu, click “See more”.

Select “List of response notifications Response Id”

You’ll see that the response ID is added to “Select an output from the previous step”. Click “Add an action”

Type “SharePoint” and select “SharePoint”

Select “Create item” from the “Actions” tab

Select “Enter custom value” for the Site Address and enter the site address we recorded earlier. Then click the drop-down for “List Name” – select the list into which you want to insert Forms responses.

Below “List Name”, additional fields will appear – one for each column of your list. Click into a column. In the right-hand bar, you will see the Form attributes that can be placed into the List column. For each List column, select the Form component you want stored in that field.

Flow is aware of column types – if you click into a date column, it will try to present Forms information that holds dates. If you find an option missing, click “See more” to see an unfiltered list of Forms attributes.

When you are selecting values to use in an action, the possible selections are grouped by the action. For a simple form/list combination like this, using the default step names is not too confusing, but if you build more complex workflows with more data elements … figuring out which “Get response details” is which becomes difficult.

It can be clearer if you provide a unique name for each trigger and action – the values from the LJRDemo form are clearly part of the “Get LJRDemo Form response details” section.

To rename a workflow element, click the ellipses and select “Rename”

Type a new name and hit enter

Click “Save” to save and activate your workflow.

Submit answers using your form, then check your SPO list.

Notice that what Forms calls my e-mail address is actually my logon ID. This is very useful for IT folks who can easily correlate logon IDs to actual individuals. Maybe not so useful for you. Flow provides directory lookup capabilities if you want to turn the logon ID field into something … more readable. Edit your Flow. After the get response details step, click the little plus to insert a new step.

Select “Add an action”

Search for “Office 365 Users”, select it, and select “Get user profile (V2)”.

Click in the “User (UPN)” box and select the Form respondent’s “e-mail”

Edit the action which creates the SPO list item. You will now have dynamic content both from the Form response and the Office 365 user lookup. In this case, I will use “Mail” in the Submitter column.

Save you changes. Since we’ve already submitted a Form response, we can test the Flow without submitting a new response. Click “Test” in the upper right-hand corner of your screen.

Select one of the previous runs (there may only be one!) and click “Save & Test” to re-run the Form response through your workflow.

Now the submitter is recorded as an e-mail address.

SPO Guest Access Stops Working

I ran across an interesting issue today — Windstream’s got a really awesome SPO site for SD Project Management – tracking orders, equipment orders, 3rd party cabling installations, etc. The cool part about the site being hosted in SharePoint Online is that a customer can get set up as a federated partner and be granted access to see equipment readiness and installation scheduling within our system.

Guest access is an interesting concept – while I have an account in our tenant that is linked to my Active Directory account in our domain, you can also create links to accounts in other company’s directories. The guest account can then be set up to access our Azure resources – added to Azure groups, added to SharePoint Online groups, invited to join Teams.

A guest user had her computer replaced and could no longer access the site – SPO insisted that she was not a valid user. Looking in Azure AD, the account existed; the audit log even showed successful authentication events. I’m not sure if the computer replacement was a coincidence, the new computer had a different configuration, or if your browser stashes some information that allowed her to avoid authentication failures, but her guest account in our tenant was no longer working.

For companies that don’t have Azure AD, when an individual accepts guest account access … the guest account link in our tenant lists “Microsoft Account” as the source.

But when the company sets up Azure, the auth framework seems to get confused by the Azure AD account. Easy enough solution – we’ve got to delete the guest account that’s linked to their MS Account from Azure AD. Bonus step specific to SPO, a site administrator needs to use <site>/_layouts/15/people.aspx?MembershipGroupId=0 to delete the guest account from the SPO site.

 

Once the “Microsoft Account” guest account has been removed, the guest can be re-invited. They’ll step through the registration process again but the guest account will be linked up to their Azure AD account.

 Re-add the new guest account to whatever they were using & their access will be restored.

 

Did you know … You can paste records into a SharePoint list?

I’ve written about using the SharePoint REST API to maintain data in a SharePoint list, but tweaking a program to maintain your list data takes some programming knowledge. If you just want to get data into the list, it can even seem quicker to just retype the information. But you can paste data into the list too.

From your list, switch to “Quick Edit” mode

Look at the columns – the data you are pasting in will need to line up with the quick edit view.

Format your data to match the quick edit view – I often use Excel to store my data because it’s quick and convenient.

Highlight the records and click copy (or use ctrl-c)

Return to the SharePoint list. Scroll to the bottom of the page and click in the bottom left-hand corner to highlight the blank row.

Use CTRL+v to paste your new records into the list.

That’s it! You can click “Exit quick edit” to return to your normal view or close the browser tab if you’re done.

 

Did you know … you can Quick Edit SharePoint lists by default?

Web forms are fine for one-off data entry, but the default SharePoint list view is inefficient for bulk item updates. Since you can create your own list views, and you can set your view as the list default … you can have lists open in the Quick Edit view.

Click the not-quite-a-hamburger menu between the list name and “List”, then select “Settings

Scroll down to the “Views” section and click “Create view”

Select “Datasheet View” as the basis for your new view

Give the view a descriptive name. You might not want to set the view as the default just yet – create it, verify it looks good, then set it as the default. Or, like me, you might be a combination of impatient and confident 😊 Either way, select to create a public view.

Check off the columns you want to include, uncheck any that you don’t need.

Select how you want your list sorted – I am sorting by the record creation time, but you can sort and sub-sort by whatever columns make sense for your data.

You can also filter items displayed in your view – my example is order entry, so maybe a ship date column is superfluous for data entry and programmatically populated by some order fulfillment process. Or you can leave the data unfiltered. Scroll down and click “OK” to save your view.

If you didn’t make your view the default, click the drop-down by the view name and select your view to test it. Return to the list settings and click on your view name to edit it and set it as the default view.

Now that my datasheet style view is the default, my list opens in the “Quick edit”, and I can update lots of records.

Asdf

 

Did you know … you can link SharePoint Lists?

There is a lot of nicely self-contained data – I track usage and membership stats to track adoption and predict future capacity needs, and a single list contains everything I need to know. But sometimes it’s not possible to keep complete records in a single table – two different processes update the information, and there is a need to isolate access to the more confidential information. As an example, I have a process that updates employee e-mail addresses in PeopleSoft. I don’t have any reason to read employee evaluations or payroll information. Even where it is possible, it can be inefficient. Data duplication can be eliminated by keeping the information in separate tables.

In a relational database, this is called normalization. Instead of maintaining a single table with orders where the customer’s name, address, contact person, phone number, etc are stored on every single order … you create a table with order-specific data (what did they order, how many, how much did it cost) and include a single column for customer number. Another table has the customer number and their contact information. If the customer moves their office, you don’t have to go through and update hundreds of orders – just go into the customer table and update their record. All orders will use the new address. To retrieve an amalgamated record, use JOIN in the query (or create a view using a JOIN query).

You can do something similar with SharePoint lists. As an example, I will use lists with customer contact information and a list with orders. First, we need a list with customer information. Add an index for the column that will be used to find the customer record – in this case, it is CustomerID. Click on “Indexed columns”.

Click “Create a new index”

Select the column to index and click “Create”

You now have an index – something to speed up searching your list for CustomerID.

We’ll also need a list with customer orders. Create all of the columns that aren’t populated by lookup *or* the field on which the lookup occurs. Then Add lookup column using “Add Column” and selecting “More”

Name it & select “lookup” as the data type.

Some of the column settings will depend on your particular use case. Requiring data: are there going to be orders where the site isn’t known yet? Should the values be unique (in this case, no, since I hope my customers return and place a second order!).

From the “Get information from” drop-down, select the table that stores the associated information. The page will reload; from the “In this column” drop-down select the value you want to store in this lookup column – what information is being cross-referenced? Here, it’s the customer ID number. Then check all of the columns you want to populate based on the lookup field. Here, I’m pulling in the customer’s name and mailing address details.

I specifically configure this table’s records not to be deleted if the record gets pulled from the lookup table. Again, this will depend on your use case. Hit OK to create the column.

When creating a new record, you won’t even see the columns we’ve checked above – there will be a drop-down where you can select the data on which the lists are correlated.

When you save the record, the remaining columns display information from the customer information table.

What if you don’t want a drop-down with ten thousand customer numbers? (A perfectly reasonable request – I’ve loaded a few thousand customers in my list and there’s no way I want to scroll through all of those numbers and hope to get the right one).

You can turn the drop-down list into a searchable drop-down by using PowerApps to customize the input form. Click the not-quite-a-hamburger menu between the list name and “List” and select “Settings”

On the settings page, select “Form settings”

Click the “Customize in PowerApps” hyperlink.

Wait a minute as the new app is built.

In the right-hand tool bar, find the “Fields” section and click “Edit”.

On the menu that flies out to the left, click on “Add fields”

Select the fields for the input form and click “Add”

The drop-down box in PowerApps is searchable

In the ribbon bar, select “File”

Click “Save” to save the app

When the app has saved, click “Publish to SharePoint”

You will see a warning that publishing the app to SharePoint makes it visible to anyone who uses your list. Click to continue publishing the app to SharePoint.

Verify that the app is published successfully.

Click “Back to SharePoint” to return to your list.

In the list settings, verify that the custom form is selected for your list.

On the data entry card, you will see that the drop-down is now searchable.

When you type some text into the drop-down, you will see a filtered list of options.

** This works provided the list against which the lookup is performed (in this example, my customer list) contains 5,000 or fewer records. If you see an error like this, then you’ve exceeded 5,000 records in the lookup table.

There are a few options – the generally recommendation from Microsoft is to break the list into multiple lists. This may mean breaking the data into different, unrelated lists. Or it may mean establishing a parent/child list relationship (e.g. a customer list that has multiple entries per customer reflecting their various offices could be a parent list of customers and a list for each customer reflecting their sites). You may be able to use a view, filtered to a subset of records, for data entry. Writing a custom web input form and using the SharePoint REST API to read and write data would allow you to populate the searchable drop-down with data retrieved from the lookup target (in this case my customer table) – as the column is indexed, the threshold should not be exceeded as you search.

 

Did you know … you can perform CRUD operations on SharePoint Lists?

Not crud like “ugg, that’s a bunch of crud … let’s load it up in a SharePoint list to store it forever!” – that wouldn’t make sense at all. In programming-speak, CRUD is an abbreviation for Create, Update, Read, Delete – the basic types of operations for data storage. And you can create, update, read, and delete SharePoint list items through the REST API.

First, you’ll need a list. Here, I am using a sample list that has columns for SiteID, MailingAddress, City, State, and ZipCode – the usual information if you’re going to use a LOOKUP column to correlate a location in a record with address details for the location (i.e. there’s no reason to type 1925 Enterprise Parkway and such in every order you want to ship to the Twinsburg office).

And you need some sort of code that communicates with the REST API – something that sends HTTPS calls. In the example code, I am using Python. Functions, along with example code to use those functions can be found at https://github.com/ljr55555/spoRestAPICRUD. Clone the repository locally.

You will find a config.sample – I use this as a template for storing user-specific configuration parameters. Copy config.sample to config.py and edit config.py. The actual config.py is included in the .gitignore file, so retrieving updates from the repository won’t wipe our your settings.

There are a handful of values you will need to set. Most of the values you can get from your list’s web address. Open your list in the web browser of your choice and find the information in the address line:

There are three values we need to extract from this URL – the SharePoint tenant address, your SharePoint site name, and the list name

Edit config.py and modify the following variables with your list-specific information

strConnectURL = “tenant.sharepoint.com”

strContextURI = “https://tenant.sharepoint.com/sites/SiteName/_api/contextinfo”

strListInfoURI = “https://tenant.sharepoint.com/sites/SiteName/_api/web/lists/GetByTitle(‘ListName‘)”

Then you need some credentials – this config file will need to be updated when the account password changes, so you may wish to use a non-user account with a very long password that changes less often.

Obviously, typing a username and password in clear text is a bad idea. I’m using simplecrypt to keep an encrypted password in the config file which is decrypted using a key in the script file. Anyone who obtains both files can decrypt the password – in my production code, the key comes from another location to reduce the probability of someone accessing the key file.

Use stashStringForConfig.py to generate the string to use for the username and password values – change strKey to match whatever you are using for your key, and change strString to your user id. Run the script and copy the output into your config.py file. Change strString to your password and repeat the process.

C:\ljr\git\spoRestAPICRUD>python stashStringForConfig.py

b’c2MAAnHWW1nqXuc4bO+pt8q1FjTG6Q5CYNz1O5ORHnJxl8vBOpGKj0HxVSYdGa1o+Ij/VicrQLTWTyU7P0StspMEJ7zBe/qtFWuHGrfEvnLO5dU=’

That’s it for configuration – at this point, if you have a list with the same columns I do, you can run the script.

Voila, records!

What’s the script doing? Well – CRUD, of course!

Connecting To SharePoint Online – I am using a modified version of sharepy which can be found in the develop branch of my fork of the repository. This is a requests wrapper that handles authentication to SharePoint Online. The connection, in my script, is named spoConnection. The arguments supplied are sourced from config.py

spoConnection = sharepy.connect(strConnectURI,strUID,strPass)

Creation – You need a dictionary with your data. The required metadata type value is retrieved from your list. The remaining key:value pairs in the dictionary are the column names and record values, respectively.

{“__metadata”: { “type”: strListItemEntityTypeFullName}, “Title”: “Bedford Office”, “SiteID”: ‘123456’, “MailingAddress”: “17500 Rockside Road”, “City”: “Bedford”, “State”: “OH”, “ZipCode”: “44146”}

The writeNewRecord function will insert the record into your list. The dictionary containing my record is called strBody (because it ends up being the HTTP POST body).

iNewRecordResult = writeNewRecord(spoConnection, strContextURI, strListDataURI, strBody)

Read – Now that we have records, we can retrieve the full list or filter to find specific records. To find all records, run findSPRecord – the arguments are the SharePoint connection and the URI for the list.

jsonResult = findSPRecord(spoConnection, strListDataURI)

If you want to return a filtered subset of data, add the column on which to filter, the filter operator, and the value. You can construct more complex ODATA filters – see the ODATA query operations supported in the SharePoint REST API for more information.

jsonResult = findSPRecord(spoConnection, strListDataURI, “SiteID”, “eq”, “234567”)

Update – I intentionally included incorrect data in one of my create lines – the Twinsburg office isn’t in Rochester NY! To update a record, you need it’s internal ID number. The findSPRecordID function has the same parameters as findSPRecord, but instead of returning the full record, it returns the integer record ID.

iRecordToUpdate = findSPRecordID(spoConnection, strListDataURI, “SiteID”, “eq”, “345678”)

Now that we have a record number, we also need a dictionary with the new values. Values that are not changing do not need to be included – just anything value you want to update. As with the record creation, the metadata type is determined programmatically.

dictRecordPatch = {“__metadata”: { “type”: strListItemEntityTypeFullName}, ‘Title’: “Rochester Office”}

And then updateRecord is called to write the new information into the selected record.

iRecordPatchResult = updateRecord(spoConnection, strContextURI, strListDataURI, dictRecordPatch)

Delete – Delete operations are similar to update operations – you need to find the internal record ID number to delete it. There’s no validation – nothing checks that the City for item #x is Rochester.

iDeletionResult = deleteRecord(spoConnection, strContextURI, strListDataURI, iRecordToDelete)

By combining CRUD operations, you can use a SharePoint list as a user-created and user-administered database. SharePoint still stores its information in a Microsoft SQL database, and going through the REST API to interact with your data adds overhead … so this isn’t a good approach for someone with an enormous data set where views would speed up data access or complex join operations are warranted. But for someone with fairly straight-forward database requirements, you may be able to do-it-yourself using SharePoint lists.

 

SharePoint Column Naming Inconsistency

I have been interfacing with SharePoint list data via the REST API and assumed the name I typed onto the column was, well, the column’s name. It’s not!

In the ‘normal’ view, click ‘Add column’ and add a column.

Switch to “Quick edit” view.

Add a column using the “+” symbol.

What do we have? The columns look like they have the names I’ve supplied through either method. But … hover your mouse over the column and check out the URL for the column.

While the ones added using “Add Column” are named exactly what I typed, the ones added in quick edit have four-character pseudo-random strings instead of the name I typed.

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%2MZQ472C3%2F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=AddedViaAddColumn

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%9MZQ472C3%9F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=dwev

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%9MZQ472C3%9F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=fqkm

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%9MZQ472C3%9F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=NumberViaAddColumn

For GUI access to your data, this is immaterial – the friendly name displays on forms; but, when you are accessing data via the REST API, you must use the internal field name and not the display name you think is assigned to the column. It took me a long time to figure out why my REST call kept saying there was no field “uid” when I could clearly see a column with that name in my list.

 

SharePoint Rest API Does Not Allow Unindexed Queries

I’ve been developing code templates for CRUD operations (that’s a real acronym — Create, Read, Update, Delete) against SharePoint — we need to use SharePoint lists to replace database tables. Retrieving information worked fine until I tried to filter the data through the REST call. SharePoint throws a generic error about exceeding some admin-set limit. (1) I know the limit, I can see the limit. The limit is 5,000, and I know my filtered result set is 121 records. WAY lower than 5,000. Oh, and (2) I can run the query without the filter — I’m paging it! — and read all 29,887 records so what does the limit have to do with anything? Reasoning with an HTTP response … well, doesn’t work. No matter how unassailable my argument is, the API call still returned:

{"error":
    {"code":"-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
     "message":{"lang":"en-US",
       "value":"The attempted operation is prohibited because it exceeds the list 
                view threshold enforced by the administrator."}}}

It is, it turns out, a poorly worded error. I started thinking about the query limits on my LDAP servers — we have hard limits to operations and also require most people perform queries against indexed attributes. It’s computationally expensive to search through unindexed attributes (and the Right Thing To Do, generally speaking, is add an index for something that is a frequent query target). I wondered if there was an analogous “no unindexed queries” setting in SharePoint. Quick enough to test — add an index on the column(s) you use in the filter. In the site content listing, click the sideways hamburger menu by the list name. Select “Settings”

Scroll down to Index Columns and click the hyperlink.

Click ‘Create a new index’

Wait for the index process to complete, then try the filtered request again … I’ve got data! Evidently SharePoint ODATA filter queries to the REST API need to be performed against indexed columns. I’m sure Microsoft has that documented somewhere but quite a bit of Googling didn’t get me anywhere … so I’m posting this in case anyone else encounters the same error.

On Snowden and Sharepoint

I’ve seen a number of articles focus on how the NSA failed to properly secure data within SharePoint, thus allowing Snowden to take off with a huge amount of sensitive data. What I haven’t seen anyone discuss is some type of AI that would analyze the SharePoint audit records against organisational information and what others in the same position access. Maybe the access would have gotten flagged to management and someone would have said “Oh, he’s doing this data migration to the Hawaiian cluster so I guess it’s reasonable he’d be accessing the data”. Maybe. Or they would have dug deeper and seen that something malicious was happening. Or, hell, maybe just talking to the guy about his suspicious access would have scared him enough that he’d have stopped. Who knows. But asking humans to read through the audit logs on a SharePoint server (the remediation suggestions that I’ve seen) is ‘find this needle in a stack of needles’ silly. Algorithms, and especially learning algorithms, are much better suited for that type of analysis.