Google Calendar: Not able to set Row ID in action

[Fabian_Weller]

When we use Google Calendar as a data source, we can add new entries with AppSheet via the following actions:

• Add
• LINKTOFORM()
• Add a new row to another table using values from this row

But there seems to be a bug in the "Add a new row to another table using values from this row" action.
We cannot select the [Row ID] column to set it's value.

This may be intended, because the Google Calendar Event ID has some requirements as stated here.

Show More

Opaque identifier of the event. When creating new single or recurring events, you can specify their IDs. Provided IDs must follow these rules:

• characters allowed in the ID are those used in base32hex encoding, i.e. lowercase letters a-v and digits 0-9, see section 3.1.2 in RFC2938
• the length of the ID must be between 5 and 1024 characters
• the ID must be unique per calendar

Due to the globally distributed nature of the system, we cannot guarantee that ID collisions will be detected at event creation time. To minimize the risk of collisions we recommend using an established UUID algorithm such as one described in RFC4122.

If you do not specify an ID, it will be automatically generated by the server.

Note that the icalUID and the id are not identical and only one of them should be supplied at event creation time. One difference in their semantics is that in recurring events, all occurrences of one event have different ids while they all share the same icalUIDs.

We can rename the column and then we can select it in the "Add a new row to another table using values from this row" action. But the value we set there will not be respected. Instead, Google Calendar will create it's own ID with 26 alpha-numeric characters. So this is not a solution.

But we can set the Row ID via the LINKTOFORM() action. For example:

LINKTOFORM(
"Calendar_Form",
"Row ID",CONCATENATE("test1234",UNIQUEID()),
"Title","Test",
"Start",NOW()
)

 This is working as we can see in the data source:

To conclude:
Why can we set the Row ID in a LINKTOFORM() action but not in a "Add a new row to another table using values from this row" action?

[Suvrutt_Gurjar]

Hi @Fabian_Weller ,

You are correct in your assessment. However just for our understanding, may we know if you are trying to set the [Row ID[ for any specific reason.

This is so because even without setting the [Row ID] through action, I tested that we are able to execute the action to add a meeting event to the calendar. However for such meeting events added through the AppSheet app, the [Row ID] is set to AppSheet UNIQUE() ID that is et as an initial value for the [Row ID] column. But events are available in the calendar.

[dbaum]

If the issue is that you need to know the new row's key for subsequent actions, consider exploring the technique of Use 'Call a process' and 'Return values' steps in automations.

[Fabian_Weller]

Thank you @Suvrutt_Gurjar  and @dbaum for taking the time.

It's true, that we don't need to set the Calendar Event ID manually. Initially it is set to UNIQUEID() which will generate an ID with 8 alpha-numeric characters. But even if we would leaf this blank, Google Calendar will generate the ID as it's stated in the documentation:
"If you do not specify an ID, it will be automatically generated by the server."
And this ID would have 26 alpha-numeric characters.

Why do I need to set the ID manually?
In our App we manage customers. Customers have heating systems that need maintenance. In the heating system's detail view I've created an action that will open a helper table. In this table I can prepare the calendar event. Why don't I open the Google_Calendar_Form view directly to add an event? Because in my helper table I can add additional columns to construct the calendar event more user friendly.
In the helper table, I can also store the heating system's ID.
By saving the helper_table_form, a Bot will add a row to the Google Calendar via the action "Add a new row to another table using values from this row".

The goal is to include the heating system's ID into the Google Calendar ID with the expression [_THISROW].[Heating_System_ID]&UNIQUEID()
Then I add a VC to the Google Calendar with the Expression LEFT([ID],8).
This will generate a Ref to the heating system.
In the heating system's detail view I can now see all it's events as an inline view.

I was able to do that now with an initial value in the Google Calendar ID that does a LOOKUP() to the helper table to get the heating system's ID. This is working fine.
(in the helper table I have a DateTime column. I use this in the LOOKUP() expression to get the most recent row)
But if we could set the ID directly in the "Add a new row to another table using values from this row" action, I would not need this LOOKUP() expression.

[Suvrutt_Gurjar]

---

---

Hi @Fabian_Weller ,

Thank you very much for the detailed explanation of your use case. I now understood your use case and how the ability to add key of the calendar table in the action itself will save the LOOKUP() based workaround for you.

[Alan_Thorp]

I have come to almost the exact same issue. I'm planning on using a Bookings table to link my Jobs table to the Google Calendar table.

The workflow would be:

• Create a Booking via a form, linking the Job as a reference.
• Add new row to Calendar copying times and BookingID. BookingID would go into Row ID in the Calendar table.
• The Calendar would has a virtual column that looks up the BookingID using the Row ID and returns the associated JobID.

Of course the can't happen because of the problem identified above. Now I think that I'm going to add a Calendar entry, run an action on the form close that copies the details to a new row in the Bookings table, and then opens a form for that row so that the Job can be selected.

Calendar integration needs to be sorted out. It is a fundamental part of so many apps. It's frankly ridiculous that we have to go about things with all these convoluted methods when all that is needed is a reference to a supporting table or similar.

I'd really like to know if this is an issue that is going to be fixed soon so that I can make some sort of plans for the future. It seems like fixing this is a higher priority than a new desktop interface.

[Fabian_Weller]

Hi @Alan_Thorp thank you for your post. I'm in contact with AppSheet Support. But you can also send a note to them, so they can see the priority.

As I mentioned above, I was able to set the Row ID via initial value that does a LOOKUP(). I use a bot, but I'm sure this is also possible with a form saved event action.

[WillowMobileSys]

@Alan_Thorp 

The direction AppSheet should be moving is to use the Extended Properties of the Google Calendar API.  Not sure how but it would allow us to specify our own Name/Value pairs:

https://developers.google.com/calendar/api/guides/extended-properties

In the meantime, there does need to be a way to add meta-data.  The key ID as suggested by @Fabian_Weller is good approach for key values specifically.

An approach I have used with Google Folders can also apply here.  I set some accessible field with the needed data and then use a Virtual Column to parse it out and perform the lookup - no bot required.  For files, I inserted the data I needed into the file names which was perfect since that was unlikely to ever change.

In Google Calendar, we only have user accessible fields which could be changed on google Calendar by anyone who access to the event there.  Less than ideal!!

[eric_niedergang]

Hi everybody,

I agree with everything. Google Calendar integrations useless for me.
I need to keep a link between my Projet ID and Google Calendar. Maybe I can store the Google calendar ID into a column of my Projects database but it is more complicated 

[Fabian_Weller]

@lizlynch This is still an open point as written in the OP. Can you please check the actual state?