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
The following column validation formula worked fine in SharePoint on-prem (2016 specifically, in my case), but returned an error when used in the exact same context in SharePoint Online’s modern UI:
The expected behavior, in SharePoint Server/on-prem OR SharePoint Online/O365, is that if someone enters a date beyond the current date, they’ll get an error message and cannot submit the form until it’s corrected and the validation formula resolves to TRUE.
Troubleshooting in SharePoint Online
I used this formula in SharePoint Server/on-prem, and it worked fine. Then I tried using the modern UI in SharePoint Online by using the column’s menu > Column settings > Edit.
But when you try to save the exact same formula (specifically from the modern experience side panel) you get the error “A formula has a syntax error.”
Then I decided to try the classic view of settings to compare on-prem and online as closely as possible. I went to Settings > List settings and selected my column.
And, as you already know, it WORKED when entered on this classic column settings page (in SharePoint Online still) instead of the modern column settings side panel accessed directly from the list view.
When I go back through the modern UI now that my formula saved successfully, I see what caused the problem. The modern UI requires that you begin the formula with an equals sign (=). When I created the formula through the classic column settings method, it automatically added the equals sign for me in the background.
So if you’re creating column validation formulas in the modern experience (or even in classic), just remember to add an equals sign (=) to the start of your formula.
The difference is simply which formats are accepted.
Classic: Start formulas with or without equals sign
As a rule of thumb (not to mention for improving your Secure Score), you should limit the number of people who have the “global admin” role in your organization. Microsoft recommends fewer than 5 global admins. That makes it important to get to know the other roles available and assign the least permissive role (a phrase you’ll see frequently if seeking certifications) rather than blanket roles that often include more permissions than what are necessary (or secure).
Global admins can assign other admin roles, purchase additional products and subscriptions, reset all (including each others’) passwords, and manage absolutely everything in your tenant. So of course you can see why we’d want to restrict how many are working with these capabilities simultaneously.
You may end up assigning five different, non-global admin roles to a user instead of the single global admin role, but your security will be improved significantly.
There are a couple places to assign admin roles: the Azure AD portal, and the M365 admin center. My goal with this post is to consolidate and simplify information on the roles, including which are only available in Azure. I’ve combined information from:
Those marked with * are only available to assign from Azure AD. All others are in both the M365 admin center AND the Azure portal.
Note: Most role descriptions are copied directly from the resources listed above as of date of publish and are subject to change. Always check Microsoft documentation prior to making significant decisions.
Shifts, formerly StaffHub, is an app available for use within Microsoft Teams to share schedules and allow employees to submit requests. There is also a mobile app that can be used for many of the same desktop features, and adds a clock in/out functionality for members.
In this post, I’ll go over some Shifts basics to give you an idea of what’s possible. Please note you must be a Team owner to set up Shifts for your team.
Set up Shifts for the first time
To get started using Shifts, open Teams and select the ellipses from the left menu. Here you can select the Shifts app.
Next, you’ll choose the Team for which you’re configuring Shifts. Click Create next to the correct Team.
Next, confirm the time zone you want to use for this team. Once correct, click Confirm.
Add groups/types of shifts
Next, you’ll want to do some planning. What types of Shifts are you going to be scheduling? For each “type” of shift, you’ll create a group.
Click Add group and enter a name for each group/type of shifts or roles.
Once you’ve added all the possible types of shifts, you may wish to reorder the shift groups. Just click the “move” button on any of the groups, then drag the groups into the order you wish and save.
Add members to groups/roles
Choose who will have shifts in each group by selecting the “add people to group” icon.
Add members you wish, then close the dialog.
A person can be a member of multiple roles. For example, I may have shifts in concessions AND the admin office. You must add these members manually to each group they’ll appear in – they are not automatically added to all groups.
Add shifts for members
You can double-click in any square on the schedule to add a shift.
If you add a shift to “Open shifts” you can assign it later.
Share/publish the schedule
As you add Shifts, changes will be marked with an asterisk meaning only you as an owner can see them. You must Share with team to publish changes and let others see the schedule.
After clicking Share with team, you choose which dates to “publish” and whether to notify the entire team or just those affected by the changes/additions.
Remember to “share” every time you make changes so members are aware of your edits or new schedules.
Adding time off for members
To add time off for a team member, just right-click a square in their row and choose Add time off. Time off will appear in ALL groups for that team member – you can’t have a sick or vacation day in one shift group, and work in another.
You can classify these time off shifts in different ways, and save when satisfied.
If someone requests time off after they’ve already been scheduled you can move their affected assigned shifts to open shifts to re-assign later or have them make a request for someone to cover them.
Your team members, not just owners, can make their own requests for time off, swaps, or offers (giving a shift up without taking another in return). This takes some of the administrative burden off owners and makes employees accountable for their own changes and communication.
Time off requests
Employees can request time off, which sends a request for approval to the manager/owner.
When time off has been requested, a pulsing orange dot will appear with the request on the schedule itself, not just in the Requests tab.
Members can offer to exchange shifts with another person. The other employee must accept the proposal for it to take affect. When making the request, your employees will be able to tell which “group” the shift is in easily.
Sometimes employees just want to give a shift away without taking another in return. They can “Offer” shifts to other employees which sends the other employee a request for approval (similar to swaps).
Not all members may have easy access to Teams at all times, so you can easily print the schedule and post anywhere.
Using the “scale to one page” setting to make sure it will fit on your desired paper size.
The next logical piece, after scheduling, is the actual shift performance itself. Shifts even includes a Time Clock feature which allows mobile clock-in and clock-out with optional location detection for reporting.
If you choose to turn on location tracking, you must enter the coordinates from which you expect employees to be clocking in and out.
From the Teams mobile app, team members can then clock in and clock out. They can edit clockings before confirming (in case they forgot to clock in/out) but the export/report will note the entry was edited. When clocking in/out, members will be notified if they’re noticed as off-location but can still clock in/out with that note added to the record.
The export (Shifts > Settings > Time Clock > Export) will highlight those clock-ins and clock-outs that were off-location:
Shifts comes at no additional cost with your O365 subscription. It could easily replace your Excel sheet schedules or expensive clock in/out software. While it may not be the best solution for all scenarios, I’d argue it could be a game-changer and cost-saver for many organizations and committees or event staff.
O365 groups make up the sun in the O365 solar system. And every Microsoft Teams team (planet?) must have an O365 group to exist. You’ll also use O365 groups for group email and calendars, defining permissions for private Stream channels, collaborating with group members in Planner, and much more.
When you create a new team from scratch, it automatically creates an O365 group (and a SharePoint site collection). Whomever you invite to that team is added to the O365 group which is also used to define permissions in other apps and services throughout O365.
But let’s say you were using O365 groups before Teams was cool. You don’t want to create duplicate teams or have to manage twice as many, right? Luckily, you can use existing O365 groups to create new teams.
Create a team using an existing O365 group
In Teams, beneath your list of Teams, click Join or create a team.
Click Create team.
Choose Create from…
Next, click Office 365 group.
If you choose Team, it will copy that Team’s apps, settings, and channels to the new Team (without changing the original Team). This might be useful for classes with similar formats, or creating committees or teams based on a well-received template.
But to create a team from our existing O365 group, we’re going to select Office 365 group.
Now select the existing O365 group for which you’re establishing a new Team, then click Create.
After a moment, your new Team will be created and you’ll be taken to its landing page.
You can verify membership is correct by opening the Team’s menu (ellipses) and selecting Manage team. This
Here you can view owners, members, and guests from the O365 group. You can now modify the O365 group’s membership here if you wish, keeping in mind it affects permissions outside of Teams as well (as is the nature of O365 groups).
Microsoft recommends modifying O365 group permissions via Teams because doing so elsewhere (such as the M365 admin center) will cause a delay in taking effect and removed team members may retain Teams chat access for a couple hours.
The Tasks app (or Task list) in SharePoint has many features to help your team communicate and stay on track. You can group tasks into buckets by designating some tasks as subtasks to others. You can also specify any order of tasks, and groups of tasks, that you wish. The built-in timeline web part polishes off the task list, providing a visual representation of the tasks and phases that matter most.
Note: See bottom of post for a video demo.
Create a new subtask
To create a subtask of an existing task (thereby creating a “group”) just select the ellipses next to the parent task to open the task menu and choose “Create subtask.”
You can reorder tasks, and groups of tasks, by using the “Move up” and “Move down” buttons on the Tasks ribbon. First, select the task (or parent task of a group). Then from the Tasks menu, choose Move up or Move down.
Change an existing task to a subtask of another
If you’ve already created your tasks and need to rearrange and organize them later, you can do so.
Select the task(s) you want to change into subtasks.
Use Tasks > Move Up and Tasks > Move Down until the task is positioned beneath the intended “parent” task.
Use “Indent” to make it a subtask of its predecessor.
To “promote” a task, you’d do the opposite, using the “Outdent” option to move a task back up a level in the hierarchy.
Create multiple layers of task groups
Using the “indent” and “outdent” options seen in the previous section, you can create many layers of tasks that can expand and collapse for a clean and organized user experience.
Add and remove tasks from the timeline
SharePoint won’t automatically add tasks to the timeline for you. This could get cluttered fast. Instead, be selective in what you choose to show on your timeline. Perhaps those that are part of the current phase or upcoming date ranges. Avoid getting too granular in what shows in this “overall” visual representation.
To add a task to the timeline, open that task’s menu and choose “Add to Timeline.”
To remove a task, simply select it on the timeline, then “Remove from Timeline“
Change date range “bars” to “callouts” on the timeline
To create a cleaner appearance, you could explore changing the “bars” on your timelines to callouts. Callouts appear as brackets encompassing the date range they’re associated with and may be easier to read for some.
Select the bar
Click “Display as Callout” from Timeline menu
The following video demonstrates a few of these tips when working with SharePoint tasks and timelines.
Most of us have Office apps installed on our machine. But not everybody has, or can afford, Photoshop. Luckily, Office apps (PowerPoint, Word, Excel, and Outlook) have built-in image editing features that can do much of your basic image editing needs, including removing backgrounds from images.
I prefer to use PowerPoint, myself, but you can use whichever app you’re comfortable with or already creating within. When finished editing, you can right click the modified image to save it as an independent image file to be used elsewhere.
Note: Video demonstration at bottom of post.
Remove the background
Here are the few simple steps to start removing backgrounds from images:
Select your image
Picture Tools > Format will become an option once the image is selected. Click it.
Select Remove Background
PowerPoint (or whichever app you’re using for this) will guess what you want to remove by highlighting it in pink.
Make corrections using the “Mark Areas to Keep” and “Mark Areas to Remove” buttons in the ribbon.
In the following animation, I’m choosing to “Mark Areas to Keep.” Once you’ve selected an option, click and drag your cursor over the pink-highlighted parts of the image you want to keep. Then select “Keep Changes” to save, removing the pink areas, and view the result.
If you’ve discovered you’ve made a mistake or want to keep or remove additional areas in the image, you can select the image again, choose “Remove Background” and continue the steps above until the result is to your liking. If you decide you want to restore the background and undo your changes, you can choose “Discard All Changes.”
Save the image(s)
When finished, you can save the modified image (which will preserve the transparent background) by right-clicking your result and choosing “Save as Picture.”
If you want to combine multiple edited images into a single image file, as in the following example, group the images first.
Select an image
Select all (Ctrl+A) to select all images
Right click a selected image > Group > Group
Right click the new group > Save as Picture
Here’s a video showing this process on multiple image types, layering those images into a final result.
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:
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.
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.
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.
On-premises data gateways allow users to connect online services like Power BI service, Power Automate (formerly Microsoft Flow), and Power Apps to their on-premises data sources such as SharePoint Server, SQL databases, and network file shares.
The gateway has allowed me to use modern services like Power Automate to bring advanced functionality to my SharePoint 2013 and 2016 lists and libraries, such as copying items across site collections (not possible in SharePoint Designer). I’ve also been able to share Power BI data visualizations and reports of limited on-prem data sources such as SQL databases with off-prem consumers who wouldn’t otherwise have access to that data.
One data gateway will cover all three apps – you don’t need separate gateways for each app/service. You can, however, have two gateways per machine but a max of one per mode:
Regular mode (share access to data)
All services (BI, Automate, Apps)
Supports Power BI scheduled refresh and live query
Personal mode (others don’t need access to data)
Only works with Power BI
Only supports Power BI scheduled refresh
Install a gateway
The following steps are adapted from documentation available on Microsoft Docs. Please read all available information (see “More info” section at bottom of post) prior to installing a gateway to make sure you’re configuring it correctly for your organization’s needs.
Install on a personal/work computer if it will only be used by one person or machine manually (perhaps for those building Power BI reports without automatic refreshes). This is not ideal for scheduled flows or data refreshes as the machine may be powered down.
To manage gateways from your apps, follow these paths;
Power Automate –> Settings wheel –> Gateways
Power Apps –> Data –> Gateways
Power BI –> Settings wheel –> Manage gateways
In Power Automate and Power Apps, your options are identical from here. You can view additional information on existing gateways, download another gateway, and share or delete existing gateways.
If you share gateways with others, you get a dialog with the option to include everyone in the organization. This way anyone could have access to the gateway to use in their own Flows, apps, and reports. And unless a user is an admin, they can’t reconfigure the gateway :
When you share a gateway with others, you can provide these levels of access within Power Automate and Power Apps (not Power BI):
Can use (for those just using apps)
Can use + share (for those sharing apps, which will automatically share the gateway)
When assigning permissions, you can specifically allow these permissions:
In all three apps, you can make someone else (or a security group) an admin. If you make another user an admin of a gateway, they have the same rights as you to manage the gateway and its configuration in its entirety.