Creating calendar invites using ICS attachments

[TyAlevizos]

Attached herein is a public example app showing how to use an ICS template to send a calendar event email to recipients. A few comments in no particular order:

0. online help for this feature: https://help.appsheet.com/en/articles/3568441-adding-a-calendar-ics-attachment-to-an-email

1. This feature has just dropped, so apologies in advance for any errors or omissions. And, feedback most welcome here!

2. The example app attached here has a parent-child relationship: the parent is the calendar entry, and the children are the email recipients. Here is the app link:

https://www.appsheet.com/samples/Sample-App-that-generates-a-calendar-invite-whenever-you-add-or-upd...

3. In the workflow, we will generate an email for any ADDS or UPDATES to a calendar event. The “TO” line is set to:

[Invitees][InviteeEmail]

(i.e. the children of the parent calendar invite item)

4. The subject line is <<[Subject]>>. The start and end times are the start and end fields. I have not yet tackled timezones so the defaut behavior is “current timezone” of the client device.

5. The ICS template is a plain text file that ends in .ics - google sheets (and probably one drive) is somewhat picky about this. As the help article states, you will want to download, edit, and then re-upload the ICS content file with great care. AppSheet will informatively throw an error if you make a mistake during this part of the process.

6. Lastly, ICS files have a UID field, this is documented somewhat vaguely in the original ietf RFP:

https://tools.ietf.org/html/rfc5545#section-3.8.4.7

which was written in 1998 and last updated in 2009. Lolz… We believe that this UID field does need to support a pretty broad definition of “uniqueness” - in the example we have created a virtual calculation field whose definition is:

concatenate(UniqueID(),"-",UniqueID(),"-",UniqueID(),"-",UniqueID())

(somewhat of a hack, but it’s getting the job done)

7. Here are the full contents of the ICS file used in this example:

   BEGIN:VCALENDAR
   VERSION:2.0
   PRODID:-//AppSheet Inc//AppSheet
   CALSCALE:GREGORIAN
   METHOD:PUBLISH
   X-COMMENT: See https://icalendar.org/validator.html
   BEGIN:VEVENT
   SUMMARY:Meeting about <<[Subject]>>
   UID:<<[ICS_UID]>>
   DTSTART:<<[TimestampStart]>>
   DTEND:<<[TimestampEnd]>>
   LOCATION:<<Here()>>
   DESCRIPTION:This is an invitation to a meeting about <<[Subject]>>
   DTSTAMP:<<Now()>>
   ORGANIZER;CN=<<USERNAME()>>:MAILTO:<<USEREMAIL()>>
   END:VEVENT
   END:VCALENDAR

Let us know here if you have any questions or feedback. Thank you!

[sarah_t]

This is a great feature! I’ve tested using a simple start/ end expression in my description field.

“DESCRIPTION:<<Start:[Related Customer_Reservations]>> Company: <<[Company Name]>> <>”

It returns the following in the Description body of the email invite

“ompany: ASDFG Manufacturingompany: DISTRIBUTOREND:VEVENT”

I’ve used a different value for the company but it should have returned the two companies, ASDFG Manufacturing and DISTRIBUTOR. It seems to leave out the C in Company and include the END:VEVENT as if it is part of the ics email description. I’m not sure if this is a bug or start/ end expressions aren’t fully supported in the description field but I would greatly appreciate some guidance on this.

Thanks in advance!

Sarah

[sarah_t]

I’m not sure why it left it off but after Company: <<[Company Name]>> there is an End statement within double arrows <<>>

[TyAlevizos]

This:
“DISTRIBUTOREND:VEVENT”

Is a pretty good hint that there’s a syntax problem in your expression. Can you paste the entire content here using the preformatted text option:

Thanks!

[Koichi_Tsuji]

This is wonderful!

One thing I m missing from Calender related function.

On any of calendar and scheduling app, then we selected a day from the calendar and then then try to add event, then form pops up with pre-filled date as selected. However, On the appsheet, adding new event to calendar will open the form with date of TODAY, ignore the selection of the date before opening the form.

Hope you guys will do something to make it far better!

[sarah_t]

Thanks!

I’ve also noticed that if you enter to a second line for the description, the calendar invite seems to ignore it and it doesn’t show in the body of the invite. I’ve tested using a \n when I want to start a new line but that gives an error that my Start doesn’t have a corresponding End.

[TyAlevizos]

I think you’re onto something (bug related) here. I changed the original ICS template from my original example so that the description line reads:

DESCRIPTION:<<Start: [Invitees]>><<[InviteeEmail]>><<End>>

And when I tested I received no ICS attachment in my inbox.

I think we’ll need to carefully experiment on an ongoing basis.

Unlike some of the other attachment types, ICS is a pretty nit-picky standard and format - it could very well be that AppSheet dev team has encoded safety checks and conservative handling on this file type. I’ll check with them as soon as possible.

[sarah_t]

I thought it might have something to do with the ics attachment as well because the expression works with any other attachment template. Also, \n seems to work well for separating the description in multiple lines but only if you don’t need a start/ end expression in it.

I would eventually like to use another embedded start/ end expression to get grandchild records (guests for each customer all on one reservation) as well as child records. I know this also works with my other attachment templates so I’m sure it’s contingent on our initial problem.

Thanks for your help with this! I’ll keep trying different things until there’s an update.

[TyAlevizos]

I see. That’s helpful, thanks.

Just to clarify, there really isn’t a good business reason to use a parent-child Start Expression inside of the ICS file itself. That’s better kept in an email body, PDF attachment, etc.

The ICS file is meant to be delivered to one person and is meant to have calendar info on it.

That’s why the original example we shipped for this post included an email-to which found the “children” of the calendar event “parent”, the children in this case were email recipients:

So, even were we to “fix” the ICS start expression item, it’s not very worthwhile from a business point of view.

Hopefully this helps explain it further, and/or, I still may have missed the overall goal/purpose here!

[sarah_t]

I completely understand that. In our case, the invite gets sent to a group and the reason why it’s important for the users of the app is because they would like the invitation recipients to be able to open their calendar on Outlook, open the event/ meeting and see specific parent, child, and grandchild information in the description. Some of the invite recipients do not have the app so they simply receive a workflow email with the information in the email body. But all of the users would rather open up their calendar and immediately find the event with the important details in the description instead of having to search through all of their emails for the reservation. Hope that gives some explanation of why I am trying to get this working for my users!

If could add a pdf attachment to the invite itself, not the email the invite is delivered on, then that would be great but I don’t know if that would be possible.

[TyAlevizos]

that’s a pretty decent business use case! Thank you.

I can confirm that the logs throw an undesirable error:
"Errors": "Error: Workflow rule 'Send Calendar Invite' action 'Action 1' Attachment template. Contains end token '>>' at offset 14 without matching start token '<<'",

when I insert the following “child” example in the description line:

<<Start: [Related calendar_lineitems]>> <<[LineItemName]>> <<End>>

I’ll log a bug and link back to this thread.

[sarah_t]

Any update on the bug?

[Fabian_Weller]

Thank you for this function.

I’m also looking forward for this

Microsoft Office Calendar integration Feature Requests

Would love to have a Microsoft Calendar integration like we have the Google calendar integration. We want to book rooms and plan the vacations of our employees with an App. The App should send it to the Microsoft Calendar.

We now can add Calender entries via the ICS file.
But with an integration we could also read and edit the calendar.

[Phil]

Hi Sarah,

As Ty said, you uncovered a bug. I made an attempt to fix the bug when you first reported it, but was unsuccessful.

I made another attempt to fix the problem yesterday. In an abundance of caution, I am only releasing the change gradually. If you provide me your account id, I can add you to the list of users getting the fix. That will allow you to see if it fixes the problem you reported.

[stephen_silk]

Hi Phil, is there working sample app that creates an .ics file suitable to create a google calendar entry ? Thankyou,

stephen

[Lucinda_Mason]

@TyAlevizos

Have you been able to tackle time zones? I want to add sending an event invite just to the current user but our users will be in many different time zones so I need to event record to indicate the time zone and have the ICS handle adjusting it to the user running the action time zone.

[TyAlevizos]

Hi Lucinda,
I haven’t had a chance to revisit this topic lately. Sorry about that. I am assuming that TIMEZONE is a standard element in the ICS format. Plus, our UTCNOW() function may be of some use here. But that’s just me typing, until I get some time to test this out again.