Pulling in data from Google Calendar

Hi all, I’m trying to modify the Google Calendar template and I haven’t been able to figure out how to use a secondary calendar with the table.

In the example document, the button to pull events into a table (called “All My Pulled Events”) has a formula that looks like this:

FormulaMap(GoogleCalendar::Events([al@codadoc.io], “al@codadoc.io”, Today(), Today() + rangeofdays), CurrentValue.Id).FormulaMap(AddOrModifyRows([All My Pulled Events], CurrentValue = Id, Event, CurrentValue))

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.

Any suggestions? Thanks in advance…
JB

2 Likes

Hey @John_Beaudoin_Jack,

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:

FormulaMap(GoogleCalendar::Events([al@codadoc.io], "al@codadoc.io", Today(), Today() + rangeofdays), AddOrModifyRows([All My Pulled Events], CurrentValue.Id = Id, Event, CurrentValue.Url))

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?

Best,
Gil

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

Any ideas how to proceed?
JB

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 :slight_smile:

3 Likes

Gil,

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:

FormulaMap(ListCombine((Google Calendar::Events <>),(Google Calendar::Events<>), Expression)

Where <> and <> designate the actual Google Calendar::Events function.

Do you see any issue with that approach?

Thanks again,

JB

2 Likes

Awesome, glad that worked!

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.

2 Likes

Gil,

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.

Thanks,

JB

Hey Jack,

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:


If you want that to happen automatically, you could use a ‘Push Buttons’ to do both the sync and the refresh at the same time.

You can also set up a periodic refresh through the packs panel for all calendar data, or manually refresh the column from the column menu:

Does that help?

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!

Gil

1 Like

Nailed it! Working beautifully now.

Thanks so much for all your help!

Jack

1 Like

Superb questions/answers to a question I have been pondering too. Thank you both :+1:

2 Likes

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.

2 Likes

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

@Ferhan_Elvanoglu I am not the owner of the template. I was just a user…

Hi @Ferhan_Elvanoglu,

Welcome to the community :handshake:

Please write to support@coda.io and include the link to the template to ask for support.

Kind regards,
//JP

1 Like