It dumps the eventID from the Google Calendar into a Google Calendar column (named “Event”) in the target table and the remaining columns use formulae to pull in other attributes like the start, end and description of the event. Good so far – when the data from my primary Google Calendar is put into the example table, everything works as expected.
But if I change the value of the calendar (bolded in the example formula above) from my primary calendar to a secondary calendar, the Google Calendar column in the table recoils in horror, per the screenshot below:
To be clear, the issue appears to be when the data is being pulled into the table. I suppose I’m not formatting the secondary calendar’s identifier (URL, ID or name, according to the Coda Help function document) in a way that works with the Google Calendar column type.
Working with the calendar API can get hairy in cases like this- the ID and URL schemes are a bit of a mess. I think what’s going on here is that the ID we get back from the calendar event is only enough to specify the event GIVEN the calendar. That’s fine for events on your primary calendar, since we assume primary calendar by default. But for secondary calendars, if you pass in just the ID without the calendar as well, we won’t be able to fetch it. That’s why the column is failing to fetch. Ideally you could provide the calendar in the configuration options for the Event column, but unfortunately that’s not yet supported.
For now, I think you can work around this by using the URL instead of the ID to populate the event column, since the URL does contain both the calendar and the event info. I’d probably still match based on ID, so that would look something like this:
Note that I got rid of the first FormulaMap (which wasn’t really necessary in the template either) so that I could use both the URL and the ID of CurrentValue in AddOrModifyRows. Does this work for you?
@gilgoldshlager41 Thanks for your explanation and suggestion. The new formula isn’t able to match the ID, however. Coda tells me
Unknown Reference - Coda cannot find reference Id in CurrentValue…"
It also can’t find the reference URL in CurrentValue.
As is probably obvious, I’m not a coder… I’ve never fully comprehended how CurrentValue works (I know it is supposed the represent the current value in the list element) and in the above case, I just pulled the formula from the template. I assume that the FormulaMap function creates a list of all events in the specified calendar with a min date of Today() and a max date of Today()+rangeofdays. So when you get to the AddOrModifyRows function, the formula modifies rows in the specified table ([All My Pulled Events] when the table’s Id column matches the ‘Id’ value from the current event returned from the generated list (or adds a new row if there are no matches). But it sounds like there’s no “Id” attribute available from the current event in the generated list, right?
Yeah, you have that mostly right. The GoogleCalendar::Events formula returns a list of events- you can see that if you try that formula directly in the canvas. Then the FormulaMap takes in the list of events, and lets you do something for each individual event. CurrentValue is the way of referencing the individual event being processed. So in this case the AddOrModifiedRows action will be run once for each event, and each time it’s run, CurrentValue will be equal to a different one of the events. Does that help clarify a little?
Anyway, dug more into why that formula isn’t working. It seems we have a bug in our formula engine that’s preventing currentValue.Id from working within an AddOrModifyRows formula. Hope we can fix that soon. For now I think you can match on URL instead of ID since that should be a unique identifier as well.
I got something working to pull events into my google calendar like this:
The formula is FormulaMap(GoogleCalendar::Events([gil@coda.io], calendar: 'Coda Calendar', minDate: Today(), maxDate: Today() + 2), CurrentValue.Url).FormulaMap(AddOrModifyRows([Calendar events], Url=CurrentValue, Url, CurrentValue))
Two changes here. First of all the move from ID -> URL for both insertion and matching. Secondly I’m actually dropping the URL into its own column called URL. I then have an Event column with a formula of ‘=[Url]’ to pull in that URL and load the full event. That makes sure that I can safely match on the URL column to avoid duplicate rows.
Hope that helps. Happy to share that doc with you as well if you send me your email
That does the trick! Thanks so much for your help here. I understand FormulaMap much better as a result, and I got this experiment working. This is going to solve a big issue for me, so I am really excited.
If you will indulge me, I have one more question. If I want to pull events from two different calendars from the same Google Calendar account (e.g. ‘Work Calendar’ and ‘Personal Calendar’) is that supported? That is, if FormulaMap is essentially creating a list of events with which to populate the table, I’m guessing it might be possible to merge two or more similar lists. For example:
On pulling in two calendars, I believe that should work as well. An alternative would be to make one button for each calendar, and then either press both of them or create another button or an automation that presses both of them using the Push Buttons action.
On that note, I’m not sure if you’ve already discovered this, but a good way to set this up either way would be to use an automation to kick the sync every day (or hour, or week, depending on how often you need it). That gives a couple advantages. First of all it makes it happen periodically without you having to press the button. Additionally, running actions through automation means that you shouldn’t experience any lag in your doc while the data is being updated.
I thought that I was pulling in the Google Calendar data correctly, but it looks like I may have misunderstood something.
Shouldn’t the “AddorModifyRows” function modify existing rows in the table? So for example, if the title of the Google Calendar Event changes, and I trigger the formula (using a button for now, automated in the future), shouldn’t the change be reflected in the table?
I noticed this because two events that I changed on Google Calendar – one, the title and one, the time of day – are not changing on my Coda table.
Totally reasonable question. If you think about the action that’s being run, it’s really just saying ‘make sure there’s a row for each event in my column’. It’s not actually telling the column to fetch the latest data for each event. The button push will thus repopulate the set of rows in the table, but not necessarily refresh all the individual item data. However, there are a few ways you can get the items to refresh.
For one, you can use the refresh action on the column:
Apologies for the hoops you have to jump through to get this working- there are a lot of things we’d like to do to make the ‘data sync’ scenario easier that got postponed in the first round of development on Packs. Hope you can get things working to your satisfaction nonetheless!
Hi folks!
I’ve been trying to do something similar to Jack (importing various secondary/sub calendars from a single Google account). I re-read this thread a TON and made some progress. I now have the original button from this doc which pulls from my default calendar and a button which pulls from a sub calendar. However the button pulling from a sub calendar (which I created based on these instructions) won’t fill out the original table’s Event, Start, End, or Duration columns.
I do have a new Event title column, which I believe is functional because the URL column which I updated is working. The other columns though - as duration is based on start and end, and those are trying to pull from an event object - I’m guessing they aren’t working because the event object is not being populated. Or something. I’m not a programmer myself!
Not sure if I need to change the button or the table to get the duration of each event to populate in the table (which will make the rest of the doc be able to calculate where I spend my time).
First button’s formula is:
FormulaMap(GoogleCalendar::Events([myemail@gmail.com], "myemail@gmail.com", Today(), Today() + rangeofdays), CurrentValue.Id).FormulaMap(AddOrModifyRows([All My Pulled Events], CurrentValue = Id, Event, CurrentValue))
Second button’s formula is:
FormulaMap(GoogleCalendar::Events([myemail@gmail.com], “Creative”, Today(), Today() + rangeofdays), CurrentValue.Url).FormulaMap(AddOrModifyRows([All My Pulled Events], Url = CurrentValue, Url, CurrentValue))
Can you try using the Calendar ID instead of the calendar name?
And pulling in a non-default calendar means you’re referencing the URL instead of the ID, so if you set the Google Calendar Event column with a formula referencing the URL, it should pull in all data.
This is the setup I tried…
Non-Default Calendar
Code with Sample Data FormulaMap(GoogleCalendar::Events([me@example.com],"xxxxxxxxxxx@group.calendar.google.com", Today()-Days(15), Today()),CurrentValue.Url).FormulaMap(AddOrModifyRows([Calendar Events], CurrentValue=Url, Url, CurrentValue))
Same Code but with Labels Instead of Sample Data FormulaMap(GoogleCalendar::Events([YOUR CONNECTED EMAIL ACCOUNT],"CALENDAR ID", MIN DATE, MAX DATE),CurrentValue.Url).FormulaMap(AddOrModifyRows([TABLE NAME], CurrentValue=Url, Url, CurrentValue))
CALENDAR ID is found in your Google Calendar account under Calendar Settings and Integrate Calendar. It looks like a long generated email address.
Table Column Setup
Url - Text: The Url to the calendar event Event - Google Calendar - Event: with formula thisRow.Url
Everything else is just Event.Whatever for any other information you want to display.
So the Event column isn’t really the core reference, the Url column is and the event just reads from it.
@John_Beaudoin_Jack can you please update the template that is in template library ? It only works if one makes the changes outlined here to download, otherwise throws empty rows into the table.
This post was from before we launched Packs with Sync Tables. The Google Calendar Pack will now let you add multiple calendars to pull in data from and it will sync. I would give things a try with the new pack first and let us know if you get stuck anywhere.
Sorry I wasn’t clear. I only want to sync one calendar. That calendar is a secondary Gmail calendar. Right now I am only able to sync with the default calendar on my Gmail. Does that make sense?
