Google Calendar: Not able to set Row ID in action

AppSheet AppSheet Q&A
1

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.

Fabian_Weller_0-1672655109086.png
Fabian_Weller_0-1672655109086.png835×517 36.7 KB

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:

Fabian_Weller_1-1672656160389.png
Fabian_Weller_1-1672656160389.png435×203 7.69 KB

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?

4 Likes
2

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.

2 Likes
3

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.

3 Likes
4

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.

3 Likes
5

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.

3 Likes
6

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.

2 Likes
7

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.

1 Like
8

@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!!

1 Like
9

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

1 Like
10

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

2 Likes
11

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

2 Likes
12

Do you have the case ID?

2 Likes
13

Thank you @AleksiAlkio but this was back in 02.01.2023 and I cannot find any mail. I think it was with the old support system. Could you please forward it to the engineering team?

3 Likes
14

Hi,

Hope this case will be solved asap

Thanks

1 Like
15

@Fabian_Weller You should receive an update from support today if you haven’t already.

1 Like
16

Thank you very much @AleksiAlkio I received an email.

1 Like
17

Wow! It took me such a long time to find (by analysing working and non-working id’s, using grok) that the letters wxyz don’t work in calendar data:

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

I wanted to post this somewhere, but I found this post. Damn this took a rediculous amount of time.. What a weird choice to exclude those letters.

1 Like
18

Hi,

Is there any update about this issue ?

Thanks

19

No. These where the last 2 responses from support:

Mar 12, 2023, 2:33 PM
Thank you for your patience. Our AppSheet product team has been notified of your issue and currently is investigating it. Unfortunately, due to the complexity of the issue, we are not able to provide a precise date for you. However, we’ll follow up as soon as we receive any update. Kindly refer to the escalation Id [b/264895156] for your future reference.

Feb 24, 2025, 1:38 PM
We have got an update from our specialists and we thank you for reaching out and bringing this issue to our attention. We truly appreciate your patience and understanding. At the moment, due to resource limitations, we’re unable to provide a resolution for this particular issue and there is no ETA to roll out a fix. However, the issues have been logged in our system. We will keep you posted if we make any changes in the future.

I will ping them again.