Introduction

In my I showed how you can use Flow to trigger a scheduled flow and retrieve an iCal feed over HTTP.

I also spent a bit of time explaining how to parse the text returned from the iCal feed into individual events and extract the information required to be able to import and synchronize the events in a SharePoint list. I mostly covered at a high-level and didn’t give you detailed steps — which is a good thing, because the article was already pretty long!

In today’s post, we’ll set up a SharePoint list and walk you through how to implement the flow, step-by-step.

Let’s get going!

Setting up a SharePoint list

To synchronize an iCal feed to SharePoint, we need two things:

  • A feed
  • A SharePoint list

And we’ll need a flow to combine the two.

For the feed, I’ll use one of CalendarLabs.com‘s iCal feeds. Being Canadian, I’ll use the Canadian Holidays feed, but feel free to use the US Holidays or any other that suits you. (Make sure that you get the URL from the Download button). I have to admit that I only learned about CalendarLabs.com when I was researching this post, but I’ll definitely be recommending it to everyone!

There are all sorts of useful iCal feeds out there. For example, if your company uses People HR to for your HR software, you can get an iCal feed of employee vacations directly from People HR and synchronize with a list called Staff Calendar.

Let’s assume you have found an iCal feed you want to sync and you tested it to make sure it returns something.

For the SharePoint list, we’ll create one. Since the iCal feed I’m using is a list of Statutory Holidays, I’ll call my new list Statutory Holidays (I know, right? How do I come up with this stuff?!)

To create the list, follow these steps:

  1. From a SharePoint site of your choice, go to Settings then Site contents
  2. From the Site contents page, select + New and pick App. We use App instead of List because we want to create an events list, not a regular list.
  3. From the Site contents > Your Apps page, pick Calendar
  4. From the Adding calendar dialog, in the Name field, enter Statutory Holidays (or whichever name you picked for your event list, I’m flexible!) then select Create.

Your list should automatically get created. Now we need to add one more field called UID to keep track of the unique identifiers for each event. The UIDs are provided by the iCal feed.

To add the column, follow these steps:

  1. From the list you just created, go to List settings
  2. In the Settings page, in the Columns section, select Create column.
  3. In the Settings > Create column page, type UID for the Column name.
  4. Un-check Add to default view, but leave everything else. We just want a simple text column to store the unique identifier, no need to get fancy.
  5. Select Ok to create the column.
  6. If you want, you can remove the columns that you won’t use, but for the sake of brevity, I’ll leave the existing fields as it.

To set up the Flow

Yesterday, I briefly touched how I build the flow to extract the data. Today, we’ll do it step-by-step, starting with these steps:

  1. Log-in to https://flow.microsoft.com
  2. From the left navigation, select + Create
  3. From the Start from blank section, select Scheduled flow
  4. In the Build a scheduled flow dialog, pick a Flow name — something like Sync Statutory Holidays from iCal Feed
  5. Under Run this flow, pick a Starting time that suits you, and select how often you want to repeat the flow. I picked 1 day under Repeat every.
  6. Select Create.
    Creating a Scheduled Flow

To retrieve the iCal feed

Once your flow is created, follow these steps:

  1. Select the + New step from the flow editor and type HTTP in the Choose an action window
  2. From the list of Actions, select HTTP
  3. Rename the new action you added to Get events from iCal
  4. In the HTTP action’s details, set the Method to GET, and put your iCal feed’s URL in the URI field.
  5. Leave everything else as is.
    HTTP Action

Try your flow by using the Test button in the upper right corner. Your Get events from iCal should return events. If it doesn’t, check the URL.

To get the list of events

  1. Select the + New step and type Initialize in the Choose an action window.
  2. From the list of Actions, select Initialize variable
  3. Rename your new action to Get list of events
  4. In the initalize action’s details, set the Name to Events, the Type to Array and the Value to:
    split(replace(body('Get_events_from_iCal'), '\\n', ''), 'BEGIN:VEVENT')

    Getting list of events

This will split the iCal feed into an array of events where BEGIN:VEVENT can be found.

To filter array elements that aren’t events

  1. Select the + New step and type Filter in the Choose an action window
  2. From the list of Actions, select Filter array
  3. Rename the new action to Remove rows that are not events
  4. In the From field, select the Events variable using the Dynamic content
  5. Below the From field, select Edit in advanced mode and type:
    @not(startsWith(item(), 'BEGIN'))

Not events

This will keep only array items that don’t start with BEGIN, thus removing all the iCal header information and leaving you with only events.

To create a loop to process all events

  1. Select the + New step button and type apply to each in the Choose an action window.
  2. From the list of Actions, select Apply to each
  3. Rename your action Loop through every event
  4. In the Select an output from previous steps field, select the Body of Remove rows that are not events in the Dynamic content picker.

This will create a loop for every event in the array.

Loop

To split every event into lines

  1. Within the Loop through every event loop, select Add an action
  2. In the Choose an action window, type compose
  3. From the list of Actions, select Compose
  4. Rename your new action Get all lines in event
  5. In the Input field, you’ll want to use:
    split(item(), json('{"NL":"\n"}')?['NL'])

Splitting into lines

Now, all we need to do is extract the data from every relevant line

To get the Start Date

  1. Still within the loop, select Add an action and type Filter in the Choose an action window
  2. From the list of Actions, select Filter
  3. Rename the new action Find DTSTART
  4. In the Inputs field’s Expression tab, type:
    @startsWith(item(), 'DTSTART')
  5. Add another action using Add an action and type Compose
  6. From the list of actions, select Compose
  7. Rename the action to Get DTSTART
  8. In the Inputs field, type the following expression:
    replace(first(body('Find_DTSTART')), 'DTSTART;VALUE=DATE:', '')

Getting Start Date

To get the End Date

Repeat the same steps as above except that you should call the Filter action Find DTEND and use the following expression:

@startsWith(item(), 'DTEND;VALUE=')

And in the Compose action, call it Get DTEND and use the following expression

replace(first(body('Find_DTEND')), 'DTEND;VALUE=DATE:', '')

Getting End Date

To get the Summary

You guessed it, repeat same as above, but name the Filter action Find SUMMARY and use the following expression:

@startsWith(item(), 'SUMMARY:')

And set your Compose action to Get SUMMARY and use this expression:

replace(first(body('Find_SUMMARY')), 'SUMMARY:', '')

Getting Summary

To get the Unique Identifier

One last time! Repeat same as above, but name the Filter action Find UID and use the following expression:

@startsWith(item(), 'UID:')

And set your Compose action to Get UID and use this expression:

replace(first(body('Find_UID')), 'UID:', '')

Getting UID

Now you have all the properties we need. You should test your flow to make sure everything works.

Everything we need

Note that if your iCal feed has different fields that you need, you may need to adjust your actions above. For example, some feeds will return DATE-TIME and DATE events, so you may want to add a little condition up there to deal with such events. To keep things simple, we won’t do that here.

To verify if the event is already in SharePoint

  1. Still within the loop, select Add an action and type SharePoint in the Choose an action window
  2. From the list of Actions, select Get items
  3. In the Site Address field, type your site’s URL
  4. From the List Name pick Statutory Holidays.
  5. Under Filter Query type UID eq ' then select the Output from Get UID in the Dynamic content window.
  6. Add ' to close the filter query
  7. Under Top Count, enter 1. We only care if there is already an event with a matching UID, so returning only 1 will do for us.

Getting SharePoint items

If you test your flow now, every Get Items action should return the following:

{
[]
}

Because there are no events to retrieve. Let’s fix that.

To create a list item if there are no matches found

  1. Still within the loop, select Add an action and type Condition in the Choose an action window

  2. From the list of Actions, select Condition

  3. Rename the condition Any existing events found

  4. In the expression below type:

    length(body('Get_items')?['value'])
  5. Select by is greater than in the next field

  6. In the next field, type 0

  7. This will cause the condition to go to If yes when there is an existing event, and If no if there isn’t an existing event. We’ll only worry about If no for now, but you could always update the existing item in the If yes side if you wanted to. Just no today, ok?

  8. In the If no site, select Add an action

  9. In the Choose an action field, type Create item

  10. In the Actions list, select Create item from SharePoint

  11. In the Create item dialog, type your Site Address and pick Statutory Holidays from the List Name

  12. In the Title field, bind to the Output from Get SUMMARY

  13. We’ll skip the Start Time and End Time for now. In the UID field, set it to the Output of the Get UID action.

  14. For the Start Time, we’ll need to do some surgery because SharePoint expects the value to be formatted as yyyy-MM-ddThh:mm:ss. To do this, we’ll use substring() to extract the year, month, and day from the event’s date but using the following expression:

    concat(substring(outputs('Get_DTSTART'),0,4),'-',substring(outputs('Get_DTSTART'),4,2),'-',substring(body('Get_DTSTART'),6,2),'T00:00:00-00:00')

    Which essentially combines the first 4 characters of the Start Date with a -, followed by the next 2 characters of Start Date, followed by another -, and the last two characters of Start Date, followed by T00:00:00 to set the time to midnight. If your iCal returns DATE-TIME values, you’ll also want to parse the time element instead of setting it to 00:00:00.
    The last part -00:00 is for the timezone. If you find that your events are coming in at the wrong time, you can adjust the time zone accordingly (e.g.: +01:00 or -01:00).

  15. Repeat the same formula for End Time, except that you should use GET_DTEND instead of GET_DTSTART.

SharePoint create items

Test your workflow, and you should have a whole bunch of new events! Then try again, and you should not get more events until the iCal feed adds a new event.

Conclusion

(Sigh of relief!) That was a long post!

This post showed you how to get an iCal feed in Flow and import events from that feed into a SharePoint list.

You can use a similar approach for other types of feeds.

There are a few more opportunities to improve the resiliency of this flow, and to deal with all-day events that span over two days when they should really last one day… but that’ll have to be for another post.

I hope this helped?

Photo credit

Image by Free-Photos from Pixabay

Author

Microsoft MVP and PnP Team Member. Independent consultant. Certified SCRUM Master. SharePoint, Office 365 and Dynamics 365 are his favourite toys.

9 Comments

  1. aewickham

    Hi Hugo! I figured out how to fix my flow! I ended up using a non ical method, but I couldn’t have gotten my flow to work without all that I learned from setting up using your ical method. I also tried the ical flow one more time after a previous comment using the exact same ical feed you used instead of the one I was trying to use to eliminate the possibility that differences between our calendar feeds was the reason for my error, but I still got the same result.

    Based on my Googling, the error I was getting is a known bug in Microsoft Power Automate that says that if the action returns null, it gives an error. People had created a workaround that filters out any null values before that action to keep it from breaking, but I wasn’t able to make those fixes work with my nonexistent programming knowledge.

    Instead, I made a flow connecting my project management software, Jira, directly to a Sharepoint calendar and bypassing the ical functionality I was planning to use to connect these. Probably should have tried that route earlier, but again, I think it was only because of all I learned from mimicking your flows that I was able to see other solutions.

    Thanks again for putting this together! Coffee on me 🙂

    • Hugo Bernier

      Thank you for sharing your results and especially a great thank you for the coffee!!! I really appreciate it!

  2. aewickham

    I did check for this and I believe it is indeed set up as a formula. The “fx” is there and it is in the pinkish color. I also double checked the syntax/spelling to make sure I didn’t copy it wrong. If you think of anything else, please let me know but don’t worry– I will continue to study this and when I find the solution, I will let you know! Thank you again for your help!

  3. aewickham

    Hi Hugo, thank you so much for the helpful post! I have set this up exactly as you described and everything is working until I get to the “create item” at the very last step. Then I continue to get an error in the flow. The error says, “Unable to process template language expressions in action “Create_item” inputs at line ‘1’ and column ‘2790’: ‘The template language function ‘substring’ expects its first parameter ‘string’ to be a string. The provided value is of type ‘Null’.

    Any idea what I could be doing wrong?

    Thank you again for taking the time to spell this out! If I can get past my error, this would solve a huge challenge for me 🙂

    • Hugo Bernier

      Hi, thank you for your kind comments. It looks like one (or more) of your events are missing a field — probably one of the date fields. Feel free to send me a link to your feed if you want and I can take a look at it.

      • Hugo Bernier

        In the stpe where you “Get all lines in events”, make sure that you use the expression editor to paste the expression.

        If it displays as text in the “Inputs”, you probably pasted it directly in the field. It should display as a pink-ish `fx` icon that says `split(…)` once you’re done.

        Essentially, the error you’re getting is that it reads the formula `split(item(), json(‘{“NL”:”\n”}’)?[‘NL’])` as the input, which isn’t an array. You want to use the expression editor to force Power Automate to treat the text as a formula which will produce an array, not as a single line of text.

        I hope it makes sense, it’s hard to describe without screen shots 🙂

  4. Jason Katz

    Very useful and thanks for the hard work. For anybody who comes across this going forward, when you make the UID column, make sure you filter for existing events by looking at what SharePoint literally calls the column, which in my case was “UID0.” Found this by going to List Settings, selecting UID, and seeing what it said after Field= in the url for this column.

  5. Pingback:

How can I help?

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: