Update a hyperlink or picture column in SharePoint using Microsoft Power Automate (Flow)

Setting a hyperlink column’s value using Power Automate is a bit different than setting other column types’ values. In SharePoint, a hyperlink column has two components – address and description. If you update this column type using Power Automate’s “Update item” action, your address and description are both set to the same value.

In SharePoint Designer workflows, we could do this easily with the usual “Set field” action:

But in Power Automate, we only get one field which maps to the URL/address part of the hyperlink field, and is duplicated as the description as well in SharePoint.

And if you’re thinking it could work with a comma, as some other field types might, it won’t. You’ll get “Enter a valid uri.”

To get this to work so we can set both address AND description as separate values, we have to use an HTTP request action instead of (or in addition to) the Update item action. This isn’t as complicated as it may sound. Here are the steps:

1. Add the Send an HTTP request to SharePoint action

2. Choose or enter your site for Site Address

3. Set Method to POST

4. Set the Uri to the following, changing List Name to your list’s name, and replacing [ID] with the ID field from dynamic content

_api/web/lists/GetByTitle('List Name')/items([ID])

5. The easiest way to set your headers is to copy and paste this text into the “Text mode” option as demonstrated in the GIF that follows.

{
   "Content-Type": "application/json;odata=verbose",
   "X-HTTP-Method": "MERGE",
   "IF-MATCH": "*"
 }
Click to enlarge

6. Lastly, in Body, you can copy and paste this as well, but:

  • replace List_x0020_Name with your own list’s name. _x0020_ should replace any spaces in your list name.
  • replace Hyperlink with the name of the hyperlink column’s name
  • replace Google with the label/description/clickable text you want for the hyperlink
  • replace http://www.google.com with the URL/address part of the hyperlink
{'__metadata': {'type':'SP.Data.List_x0020_NameListItem'},'Hyperlink':
 {'Description': 'Google',
 'Url': 'http://www.google.com'}
 }

Your final result should resemble this:

Click to enlarge

Test that out. When it’s working and you’re ready to take it to the next level, you can replace the static url (like Google’s in my example) with dynamic content. For example, you could create convenient one-click links to an item’s version history:

Click to enlarge
Click to enlarge

Workaround: Export to Excel not working in Safari, Chrome, or other non-IE browsers for SharePoint server list exports

The problem

If you’re using SharePoint online (O365) you won’t run into this issue. But for those of you, like me, who are still on SharePoint 2013 or 2016 you may have tried to use “Export to Excel” on a SharePoint list or library and received the following error message:

To export a list, you must have a Microsoft SharePoint Foundation-compatible application.

Clicking “OK” only redirects you to page that is most definitely not an Excel export:

The workaround

Using Internet Explorer to open and export your SharePoint list might be the simplest way to export your list. However, some are unable to use IE.

If you can’t download or use Internet Explorer (IE) you can still get your lists exported to Excel by working in reverse. Rather than export from SharePoint we are going to open Excel and import from SharePoint.

1. Open a blank workbook in Excel

2. Navigate to the Data ribbon –> Get Data > From Other Sources > From SharePoint List

Note: There are two SharePoint list options under Get Data. Do not select From Online Services > From SharePoint Online list (this option would be for O365 only and O365 users will not need this post’s workaround).

3. Paste the URL to your SharePoint site. You can paste the URL to the list you’re wanting to import, but delete everything after the site’s name in the URL.

4. Click OK.

5. Select Windows and leave credentials as the default “Use my current credentials” unless you have reason to access the list as another account (perhaps a service account which may be able to retrieve all items even with restricted item-level permissions).

6. Click Connect.

7. Find the name of the list you want to import, select it, and click Load.

Click to enlarge

8. Depending on the size of your list, this may take a while. Once imported, you’ll find all of your data as well as some SharePoint metadata associated with your list items.

Click to enlarge

While not ideal, it doesn’t take long to do and you do get the result you’re looking for (with a bit of cleanup, deleting unnecessary columns and such).

Some things to keep in mind:

  • Exporting from SharePoint using IE allows you to export a view.
  • Importing from SharePoint using Excel imports every single list item and column, regardless of view.
  • You’re only able to import items to which you at least have view permissions.

Solution: “Feature requires a browser that supports ActiveX controls” when importing spreadsheet to SharePoint

If you’re trying to import a spreadsheet as a new list in SharePoint, you’ll need to use a browser that supports ActiveX controls or you’ll get the error:

“This feature requires a browser that supports ActiveX controls.”

Chances are you’re running a browser other than IE, or you’re running IE version 11. ActiveX controls are not supported in IE11, or most browsers. We can work around this error message by having IE pretend it’s version 10 momentarily.

  1. Open SharePoint in Internet Explorer (IE)
    • Quick access: Hit Windows key, type IE, hit enter
  2. Press F12 to open Developer Tools and select the Emulation Tab
  3. Change Document mode from 11 to 10 (supports ActiveX). Page will reload so you can try again in version 10.

Now try to import the spreadsheet again and it will work fine, opening your spreadsheet and prompting you to select the table or relevant data for import.

Updated share link options for SharePoint files

You may have already seen some new options when sharing links to files in SharePoint (and OneDrive). Here’s what’s available in my tenant today:

If you use the “Anyone with the link” option (for anonymous access) you can choose a date on which the link will expire and access will no longer be granted via that link.

You can block people from downloading only if you uncheck “Allow editing” for the link types that support it.

Your basic options are to allow anonymous users (with or without an expiration date) to:

  • Edit & Download
  • View & Download
  • View Only

You can also easily share with people only in your tenant, even if they don’t have prior access, and choose whether they can edit, view and download, or just view.

People with existing access is useful just to Skype/Teams someone a quick link to get to the file. Their pre-existing permissions apply.

Finally, “Specific People” can allow you to share with external users but they must use the address you share with, as they’ll be sent a verification code to validate their identity. This adds a layer of security to otherwise anonymous share links.

Note: You may not be prompted to enter your email address if opened directly. But if the email is forwarded, user will be asked to verify email before they’re able to send a code.

“Notify your team” option when you upload files to SharePoint

A newer feature in SharePoint allows you the option to “Notify your team” after a new file is uploaded. Your upload process is the same, but then your “upload complete” dialog now has an additional option:

When you select “Notify your team” you’re presented with options like sharing with SharePoint groups, or just individuals manually entered.

When finished, click “Notify” and the intended recipients receive a link that only works for them when logged in.

If you attempt to share with someone not in your tenant, you will receive an error as you can only notify people with existing access.

For these external users, you can instead separately share via the usual “Share” dialog when a file is selected. Here you’ll also find a newer feature that allows for blocking downloads if the “Allow editing” box is unchecked. This would prevent people making edits offline and creating multiple versions in silos.

Speaking at SharePoint Saturday Cincinnati this Saturday | Oct 27, 2018

chrome_2018-09-09_11-33-13.png

I’ll be speaking this Saturday at SPS Cinci (#SPSCinci2018). I’ll be presenting my brand new, magical session on content management, archiving and retention for the third time this year. Don’t miss it!

SharePoint wizardry for content management, archiving & retention

Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”? Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!

Hope to see you there!

Speaking at SharePoint Saturday Baltimore this Saturday | Oct 20, 2018

chrome_2018-09-24_21-45-49.png

I’ll be speaking this Saturday at SPS Baltimore (#SPSBMORE). I’ll be presenting my brand new, magical session on content management, archiving and retention for the second time this year. Don’t miss it!

SharePoint wizardry for content management, archiving & retention

Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”? Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!

Hope to see you there!

Speaking at SharePoint Saturday Kansas City this Saturday | Oct 13, 2018

chrome_2018-09-09_11-24-06.png

My local SharePoint Saturday, SPS Kansas City, is coming up THIS WEEKEND (#SPSKC). I’ll be speaking about one of my favorite topics – building a modern intranet:

Building the Intranet of the Future: Using SharePoint to Empower Collaboration

Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint. We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets. We’ll also cover rethinking forms and minimizing busy work by utilizing automation capabilities via workflow and designing intuitive navigation.

Hope to see you there!

Speaking at SharePoint Saturday Denver this Saturday | Oct 6, 2018

chrome_2018-09-09_11-12-00

I’ll be speaking this Saturday at SPS Denver (aka SPS Colorado) (#SPSColo). I’ll be presenting a brand new, magical session on content management, archiving and retention:

Content Wizardry 2018

SharePoint wizardry for content management, archiving & retention

Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”? Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!

Hope to see you there!

Speaking at SharePoint Saturday Pittsburgh this Saturday | Sep 15, 2018

chrome_2018-09-09_10-57-58.png

The 3rd annual SharePoint Saturday Pittsburgh (#SPSBurgh) is coming up this Saturday. I’ll be speaking about one of my favorite topics – building a modern intranet:

Building the Intranet of the Future: Using SharePoint to Empower Collaboration

Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint. We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets. We’ll also cover rethinking forms and minimizing busy work by utilizing automation capabilities via workflow and designing intuitive navigation.

Hope to see you there!