Add, update, delete Google calendar events from another table (Strategy)

[shimodabt]

This article assumes a successful connection has been established with Google Calendar. 

The obvious limitation of the Google Calendar table is the inability to add more columns to store information or create Refs with other tables. 

So, another table is required to do so. The challenge is creating a link between this table (Let's call this table Activity.) and Google's one. 

The overall strategy is to create a Google Calendar event, retrieve its ID, and save it in another table for further action. 

ADD

1. Create an action type "Data: add a new row to another table..." for the "Google Calendar" table and set the values from the Activity values. Let's call this action "Google Calendar Add event"
2. Create a Bot that monitors when an Activity is added. 
3. The Bot creates a new record "Google Calendar" by calling "Google Calendar Add event" AND INSERT the "ID Activity" in this record (in my case I used the "Description" field and inserted the ID at the very end).
4. Show More

   Description = IF(...,".......AS:"&[ID Activity],"")So the end of the Description looks like... AS:0b52ef67
5. The next step of the Bot is to Run a data action type "Set row values" to set the "Google Calendar ID" (Row ID), and save it in a column Activity. Let's call it "ID Google Calendar".
6.  "Google Calendar ID" is found back by retrieving and matching the last 8 characters of the Description field of the "Google Calendar" table with the "ID Activity".
   Show More

   ANY(SELECT(Google Calendar[Row ID],RIGHT([Description],8)=[ID Activity]))

DELETE

1. On Activity,  create an action type "Data: execute an action on a set of rows" which calls Delete on the Google Calendar table. The reference row is found using: 
   Show More

   LIST([ID Google Calendar])
2. On Activity, create a "Grouped" type action. This one will call an action to first delete the Google Calendar record and another one to delete the Activity record. 

UPDATE

1. On "Google Calendar" table, create an action (let's call it "Google Calendar update") that sets the values of columns from Inputs defined in the Advanced section of the action. Like for the Title: 
   Show More

   Title = [_INPUT].[IN Title]
   ...
2. Create a Bot that monitors when an Activity is modified. His process step "Run a data action" defines an action type "Run actions on row" which calls "Google Calendar update" on "Google Calendar" table. The Referenced rows are 
   Show More

   SELECT(Google Calendar[Row ID],RIGHT([Description],8)=[ID Activity])
   The "With these inputs" are set in my case with updated values from Activity. 

Overall when adding an Activity, a Google Calendar event is added.  When the Activity is modified or deleted, the Google Calendar event changes accordingly. 

Limitation. When the Google Calendar is directly deleted or modified inside or outside of Appsheet (from a Calendar view for example), the related Activity is not updated. In my case, I could live with these limitations.