Hello all. Often I come here to ask questions, but today I thought I'd share something I came up with that may benefit others. As many of you may know, Appsheet has a Google Calendar "integration". I needed to create an app that could manage Google Calendar events for my users, and do everything they need to do without having to switch to do some stuff from Google Calendar and other things from Appsheet. It all needed to happen in one place. What I needed to accomplish:
- Add various event types to Google Calendar and be able to update and delete them from Appsheet using our custom data and templates.
- Add events that repeat on the calendar automatically.
- Add participants from our company directory automatically.
Those are just the major key points. Here's how I did it:
Created a table of scheduled events in Google Sheets and used it as a data source.
Created a new Google Calendar and added it as a data source.
Made it so that the table of events had refs and enumlist refs into tables of our company data such as locations, people's contact info/email, etc. This way, a user can add a new event, and they just pick known good data (no worrying about if they got the address, location name, invitee names or emails correct). It also automatically adds the user as an invitee (so that it shows up on their own calendar as well as anyone they invite, but I only have to connect Appsheet to one "dummy" Google Calendar that controls it all).
Specified that the records in the Google Calendar data source are *children* of the events table by specifying that the "Title" field is a ref to the table of events and that it "is part of" that data. This means the Title of each Google Calendar event is a concatenation of data from the events table with a unique ID appended to the end so it can stay "connected" properly and it serve as a primary key.
Created a bot that looks for adds on the event table and automatically creates new records on the calendar table by transposing the necessary data into the correct calendar data fields. It also sends an email notice to each attendee similar to Google Calendar's automatic invitations.
Created another bot that looks for changes to records on the event table. It deletes the existing calendar entry and creates a new one based on the changes, effectively replacing it. The user only sees it as a "change".
Deletes of events in the event table automatically cause the associated calendar data to be deleted seamlessly because they are "child records".
Created a series of hidden actions that "add a new row to another table", but really they just add it back to the events table at certain intervals.
I then created a multi-step action that calls the various interval addition actions in sequence so that an event can be repeated every 4, 8 or 16 weeks on a cycle with a single click.
Finally, I display the event table information in the app *as a calendar* view, so that the user can't even tell they're not actually working with a calendar. The adds/changes/removals from the Google Calendar are all seamless and appear to relate directly to what they do in the app to the events table.
LinkedIn
Twitter
