Creating iCal (.ics) calendar item links with a workflow (Power Automate or SharePoint Designer)

When working with calendars, a big request I hear is to make it more like Outlook or to make it easy to add an event to your calendar, at least. In SharePoint online, this is easy! The Events web part on modern pages includes an Add to my calendar button on events by default.

Click to enlarge

However, when working with classic pages or SharePoint Server/on-prem, it’s not so easy. There are two ways we might utilize Power Automate or SharePoint Designer to help us out:

  • We could create a hyperlink column if we want something on-page/in-item, then populate it using a workflow.
    • Usage idea: A landing page for upcoming training opportunities displayed in list (not calendar) format with a column designated for “Add to my calendar” links
  • If it doesn’t need to be clickable on the item or page within SharePoint, we could just build the URL within the workflow and include it in an email message.
    • Usage idea: A Flow that runs weekly to “Get items” coming up that week and sends a list out with clickable links for adding items of interest to recipients’ calendars

URL structure

No matter the tool, Power Automate or SharePoint Designer, the most important part to know is how to build the URL. That won’t change from one tool to the other.

1. Go the the list settings for the calendar hosting the events

Click to enlarge

2. Copy the list GUID from the URL in the browser. This includes everything in the address bar after “List=”. This should begin with %7B and end with %7D. This is your calendar’s GUID in hyperlink-friendly formatting.

ical4
Click to enlarge

3. Update the following URL template with your site’s path, and paste in the list/calendar GUID you copied from step 2 where GUID is. Leave the [ID] as it is for now.

https://Site or Subsite path/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=GUID&CacheControl=1&ID=[ID]&Using=event.ics 

Your almost-finished result should resemble this:

https://natechamberlain.sharepoint.com/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=%7B1EC8795A-3B1D-43D7-A49E-B1CCD4BFF950%7D&CacheControl=1&ID=[ID]&Using=event.ics 

Now we have everything we need to finish the process by using whichever workflow platform you prefer or have access to.

Create iCal (.ics) links using Power Automate

No matter where you’re using the URL in your flow, we’ll create it as an expression so we can use the concat() function.

If you just want to update a field, or create a variable, with the URL, it’s as simple as:

1. Paste the URL part you have from the previous section of this post

2. Delete [ID]

3. With your cursor still where [ID] was, use the dynamic content panel to search for and insert ID in its place.

If you want to populate an actual hyperlink format column with a label/description that’s prettier than the full URL, however, you’ll need to do a bit more work so that you can have both URL and description. Follow the steps in this post on updating hyperlink or picture format columns using Power Automate.

If not updating a field or creating a variable that pieces the URL together, you can create expressions (via Dynamic content panel) to concatenate the different parts of the URL. For example, if I’m creating an HTML table, for Value I’d use the dynamic content panel > Expression and enter a formula like:

concat('<a href="https://natechamberlain.sharepoint.com/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=%7B1EA8795A%2D3B0D%2D43D7%2DA48E%2DB3CCD4BFE950%7D&CacheControl=1&ID=',item()?['ID'],'&Using=event.ics">Add to my calendar</a>')

In this specific example, I’m creating the table after a “Get items” step. The formula above is what I’m using for the value of the “Save” (Add to Calendar) column.

Check out this post for full instructions on sending weekly emails of upcoming events with easy “Add to calendar” links using Flow.

Create iCal (.ics) links using SharePoint Designer

In SharePoint Designer, we can set a hyperlink field to our iCal link to make it easy to add an event to your calendar. This could be placed as a main column in a list view, or just on item display forms like this:

Let’s set the hyperlink field via workflow:

1. Create a variable (set workflow variable) and use the string builder

2. Paste your almost-finished URL from earlier in this post. Replace [ID] with a lookup to the current item’s ID

3. If setting a field (skip if just using the link elsewhere) add , Add to Calendar (or whatever link text you want) to the end of the string. It just has to be a comma, a space, and the text.

Click to enlarge

4. Set the hyperlink field (iCal in my example above) to your new variable.

If you’re not setting a field in your list, maybe you’re emailing new events to people and want an easy link in the email body itself. In that case, just skip step three above and your variable will just be the URL, ready to be used in email actions.

Click to enlarge

Why can’t we just use calculated columns?

Once upon a time, I blogged about creating Automatic iCal hyperlinks using a calculated column. This almost works. It creates the hyperlinks for all existing items at the time of the calculated column’s creation. But then if you add a new item or modify an existing item, the [ID] field drops out the hyperlink which, of course, breaks the link.

The appeal of calculated columns is that it won’t create another version of an item when the link is generated and it doesn’t require Power Automate or SharePoint Designer to work. Unfortunately, if the link doesn’t work after item edit or creation, then the point is lost anyway. So let’s pretend that method doesn’t exist except for one-time uses or lists that will never change again.

Demystifying Microsoft 365 admin roles in Azure AD and the M365 admin center

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. 

Available roles

Full access to enterprise applications, application registrations, and application proxy settings.

> Read more about this role on docs.microsoft.com

Create application registrations and consent to app access on their own behalf.

> Read more about this role on docs.microsoft.com

Can require users to re-register authentication for non-password credentials, like MFA.

> Read more about this role on docs.microsoft.com

Can manage Azure DevOps organization policy and settings.

> Read more about this role on docs.microsoft.com

Manages labels for the Azure Information Protection policy, manages protection templates, and activates protection.

> Read more about this role on docs.microsoft.com

Can create and manage all aspects of user flows.

> Read more about this role on docs.microsoft.com

Can create and manage the attribute schema available to all user flows.

> Read more about this role on docs.microsoft.com

Can manage secrets for federation and encryption in the Identity Experience Framework.

> Read more about this role on docs.microsoft.com

Can create and manage trust framework policies in the Identity Experience Framework.

> Read more about this role on docs.microsoft.com

Makes purchases, manages subscriptions, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to enterprise applications and application registrations. No application proxy.

> Read more about this role on docs.microsoft.com

Manages regulatory requirements and eDiscovery cases, maintains data governance for locations, identities, and apps.

> Read more about this role on docs.microsoft.com

Manages Azure Active Directory conditional access settings, but not Exchange ActiveSync conditional access policy.

> Read more about this role on docs.microsoft.com

Manages Customer Lockbox requests, can turn Customer Lockbox on or off.

> Read more about this role on docs.microsoft.com

Can access and manage Desktop management tools and services.

> Read more about this role on docs.microsoft.com

Can read basic directory information. Commonly used to grant directory read access to applications and guests.

> Read more about this role on docs.microsoft.com

Do not use. This role is automatically assigned to the Azure AD Connect service, and is not intended or supported for any other use.

> Read more about this role on docs.microsoft.com

This is a legacy role that is to be assigned to applications that do not support the Consent Framework. It should not be assigned to any users.

> Read more about this role on docs.microsoft.com

Full access to Microsoft Dynamics 365 Online, manages service requests, monitors service health.

> Read more about this role on docs.microsoft.com

Full access to Exchange Online, creates and manages groups, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Configure identity providers for use in direct federation.

> Read more about this role on docs.microsoft.com

Has unlimited access to all management features and most data in all admin centers.

> Read more about this role on docs.microsoft.com

Has read-only access to all management features and most data in all admin centers.

> Read more about this role on docs.microsoft.com

Creates groups and manages all groups settings across admin centers.

> Read more about this role on docs.microsoft.com

Manages Azure Active Directory B2B guest user invitations.

> Read more about this role on docs.microsoft.com

Resets passwords and re-authenticates for all non-admins and some admin roles, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to Intune, manages users and devices to associate policies, creates and manages groups.

> Read more about this role on docs.microsoft.com

Full access to all Kaizala management features and data, manages service requests.

> Read more about this role on docs.microsoft.com

Assigns and removes licenses from users and edits their usage location.

> Read more about this role on docs.microsoft.com

Access to data privacy messages in Message center, gets email notifications.

> Read more about this role on docs.microsoft.com

Reads and shares regular messages in Message center, gets weekly email digests, has read-only access to users, groups, domains, and subscriptions.

> Read more about this role on docs.microsoft.com

Manages cloud-based policies for Office and the What’s New content that users see in their Office apps.

> Read more about this role on docs.microsoft.com

Can reset passwords for non-administrators and Password administrators.

> Read more about this role on docs.microsoft.com

Full access to Power BI management tasks, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to Microsoft Dynamics 365, PowerApps, data loss prevention policies, and Microsoft Flow.

> Read more about this role on docs.microsoft.com

Allowed to view, set and reset authentication method information for any user (admin or non-admin).

> Read more about this role on docs.microsoft.com

Manages role assignments and all access control features of Privileged Identity Management.

> Read more about this role on docs.microsoft.com

Reads usage reporting data from the reports dashboard, PowerBI adoption content pack, sign-in reports, and Microsoft Graph reporting API.

> Read more about this role on docs.microsoft.com

Full access to Microsoft Search, assigns the Search admin and Search editor roles, manages editorial content, monitors service health, and creates service requests.

> Read more about this role on docs.microsoft.com

Can only create, edit, and delete content for Microsoft Search, like bookmarks, Q&A, and locations.

> Read more about this role on docs.microsoft.com

Can read security information and reports, and manage configuration in Azure AD and Office 365.

> Read more about this role on docs.microsoft.com

Can read security information and reports in Azure AD and Office 365.

> Read more about this role on docs.microsoft.com

Creates service requests for Azure, Microsoft 365, and Office 365 services, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to SharePoint Online, manages Office 365 groups, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to all Teams and Skype features, Skype user attributes, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

 

Full access to Teams & Skype admin center, manages Office 365 groups and service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Can manage calling and meetings features within the Microsoft Teams service. Assigns telephone numbers, creates and manages voice and meeting policies, and reads call analytics.

> Read more about this role on docs.microsoft.com

Reads call record details for all call participants to troubleshoot communication issues.

> Read more about this role on docs.microsoft.com

Reads user call details only for a specific user to troubleshoot communication issues.

> Read more about this role on docs.microsoft.com

The default role assigned to all users. No admin center access.

Resets user passwords, creates and manages users and groups, including filters, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Not finding a perfect fit? You can create CUSTOM admin roles in Azure AD if you have Azure AD Premium Plan 1.

Assign admin roles (single or bulk) in M365 admin center

To assign admin roles to a user or multiple users via the M365 admin center:

  1. Go to the M365 admin center
  2. Select Active users from under Users
  3. Select the user(s) to whom you’re assigning an admin role and select “Manage roles” from the menu



  4. Select the role(s) to assign selected user(s) and click Save

Assign admin roles in bulk in Azure AD

To assign the same role(s) to multiple users:

  1.  Sign in to Azure AD
  2. Select Roles and administrators from the left

  3.  Select the role you want to assign



  4. Click Add assignments. Search for or find those you want to add and select each. When finished, click Add.

View/edit assigned roles in Azure AD for an individual

To review a single user’s current roles, or assign more, follow these steps:

  1. Sign in to Azure AD
  2. Find and select the user for whom you want to review admin role(s)
  3. Select “Assigned roles”

  4.  Here you’ll see current assignments and can Add or remove assignments

On-premises data gateway for Power BI, Power Automate (Flow), and Power Apps

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)
    • Multiple users
    • All services (BI, Automate, Apps)
    • Supports Power BI scheduled refresh and live query
  • Personal mode (others don’t need access to data)
    • One user
    • 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.

  1. Download and install the gateway
    • Install on a server/computer that is always on to make sure Flows and refreshes aren’t disrupted due to the gateway being unavailable on a powered down machine. You might consider installing multiple gateways and creating a gateway cluster for high availability or load balancing in case a server goes down.
    • 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.
    • Note: You can restrict who is allowed to install on-premises data gateways for your organization.
  2. Configure the gateway.
  3. Add gateway admins
    • By default, the individual/account that installs a gateway is a gateway admin. You can manage admins, adding other security groups or individuals as needed.
  4. Use the gateway to refresh an on-premises data source such as a SQL database (specific to Power BI).
  5. Troubleshoot gateways

Share a data gateway

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)

More info on these levels available here.

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.

More information from Microsoft Docs

MS-101 Exam Study Guide

Below you’ll find all exam areas and objectives with related documentation to help you prepare for the exam. Most of the references below are summarized in my exam prep book.

Implement modern device services (30-35%)

Implement Microsoft 365 security and threat management (30-35%)

Manage Microsoft 365 governance and compliance (35-40%)

Make room on your shelf

Check out these two MS-101 exam prep books. The first is written by Nate Chamberlain.

Make training fun and increase learning retention with puzzles and games

My number one compliment at trainings comes from my live, interactive elements like my Mentimeter quizzes. But I’m branching into a new type of interactivity that might begin in the training classroom but carries on with attendees after they leave.

I’m talking about handouts. Attendees are more likely to remember sessions, topics, and facts if they had a little fun along the way. Not every handout has to be a glossary. Bingo, for example, challenges end users to explore various capabilities in SharePoint they otherwise may not have considered.

SharePoint Bingo and O365 Crossword: These two downloadables encourage attendees to listen up and have certain prompts in mind throughout your talk such as “how could I add a new list?” or “we can live stream events?? with what?” They can work on these throughout the session as they learn, or take it back to their desks. A great way to encourage participation is to offer an incentive such as “add your name and turn it in when you’re done for a chance to win a Surface Go.”

SharePoint Sudoku: This one is just for fun and is a great “added bonus” handout for your session. It’s also great for those attendees that show up 15 minutes early and might appreciate something to do in the meantime.

Sale!

3 Puzzle Pack (SP Bingo, SP Sudoku, & O365 Apps Crossword)

$10.85 $7.99

Get three puzzles in a bundle (saving over 25%) to improve attendee engagement and training reinforcement.

  • SharePoint Sudoku #1
  • SharePoint End-User Bingo
  • Apps in O365 Crossword (Editable)
Category: Tags: ,

Description

Get three puzzles in a bundle (saving over 25%) to improve attendee engagement and training reinforcement.

  • SharePoint Sudoku #1 (great for occupying early arrivers)
  • SharePoint End-User Bingo (hands-on practice during or after training)
  • Apps in O365 Crossword – Editable version (have “ah-ha!” moments during, and reinforce learning after)

Reviews

There are no reviews yet.

Only logged in customers who have purchased this product may leave a review.

Get a customized puzzle from me

I’m happy to create a custom crossword or bingo downloadable for your needs at the same cost of these examples (no setup fee). DM me on Twitter or send me a message via he form below with your scenario and let’s work together to make training fun!








Microsoft Surface Pro 4 “plugged in, not charging”

Alas, I reached a day where I thought I was done with my Surface. It powered on when plugged in just fine but was at 0% battery and would, of course, instantly shut off when disconnected from AC power. When I hovered over the battery indicator in the task bar, it told me it was “plugged in, not charging.” The nerve!

After some troubleshooting and trying:

  • Making sure devices, particularly under “Batteries”, were all updated
  • Uninstalling and reinstalling power related devices via Device manager
  • Running anti-virus checks
  • Installing latest Windows updates

I found out that if I plugged my surface dock into the surface “upside down” it worked fine and charged as usual. So, long story short, if your Surface isn’t charging just try connecting the power source in the opposite direction.

Also, once it “remembers how to charge itself,” you can reverse this and put the charger in the normal direction (just double-check that it still says charging). Think of it as a quick “refresh” of the surface power connector.

How to give SharePoint link lists a professional look out-of-the-box using the icon library

In the modern SharePoint experience, you can clean up ordinary link lists with very little effort, thanks to SharePoint’s icon library.

chrome_2018-10-23_15-30-53

Whether you need a Teams icon (teams), dog (fangbody), tooth (teeth) or football (football), you can search for icons and use those for adding visual cues and iconography to your sites. Here’s how you can implement this on your page(s):

Continue reading “How to give SharePoint link lists a professional look out-of-the-box using the icon library”

Set multiple choices as default values for checkbox fields in SharePoint forms

default checks

I recently worked on a project for a client that needed 2/3 checkbox options checked by default on a new form. While not as straight-forward as other field types, it’s still certainly possible.

  1. Go to List –> List Settings (server/on-prem) or settings wheel –> List Settings (online/O365)
    list settings
    list settings o365
  2. Click the name of your checkbox/choice column to edit its settings or create a new checkbox column
    iexplore_2018-07-26_07-53-51
  3. Change “Default value:” from “Choice” to “Calculated Value”
    iexplore_2018-07-26_07-52-49
  4. Enter a formula like: =”;#Choice;#Choice;#Choice;#”
    Example:
    =”;#Printed Statement;#E-Statement;#Pick Up in Office;#”
  5. Click OK

That’s it! Now on new forms/items, everything you entered in the formula as a default choice will be pre-checked.
pre-checked

Automatically open SharePoint 2013 workflow tasks in Edit mode for easy one-click approvals

one-click approval

On one of my recent projects, a client asked if it would be possible for the link to a task within a workflow notification email to open the task in “edit” mode instead of “display”. If you’re unfamiliar with SharePoint 2013 task processes built in SharePoint Designer, here’s what their process looked like prior to our change:

  1. Someone submits form
  2. Approval request sent to manager
  3. Manager clicks link in email to open task
  4. Manager clicks “Edit”
  5. Manager clicks “Approve”

They wanted to eliminate step 4 to make the process as easy as possible (one-click after opening link in email). Here’s what we ended up doing:

Continue reading “Automatically open SharePoint 2013 workflow tasks in Edit mode for easy one-click approvals”

Rearrange OneNote 2016 so pages pane and sections are visible on left

ONENOTE_2018-07-06_12-04-26

This post illustrates how you can accomplish adding sections to the left pane, and moving the pages pane from the right-hand side to the left as seen above.

Move Sections from Top to Left

In a recent training session, I was asked if it were possible to move sections from the top to the left in OneNote. The answer is “sort of”. We can get sections on the left also, but we can’t get rid of them from the top. There are two ways to accomplish this:

Continue reading “Rearrange OneNote 2016 so pages pane and sections are visible on left”