I’ve imported my Google Calendar history.

I want to see who, via email address in the attendees column, I met with the most.

What is the formula to count the number of times I’ve had an email address in my list of attendees?

Hi @Christien_Louviere and Welcome to the Community !

I have 2 similar formulas that seems to do what you’re asking when used in a canvas formula :

I mean, from my tests, they both seem to answer to this:

``````Events.Attendees.Email.ListCombine().Filter(
CurrentValue.Contains("Your_own_email_here").Not()
AND
CurrentValue.IsNotBlank()
).WithName(All,
All.Unique().WithName(Unique,
Unique.ForEach(
CurrentValue.WithName(U,
ListCombine(
All.Filter(CurrentValue.ContainsText(U,true,true)).Count(),
U
)
)
).MaxBy(CurrentValue.Nth(1))
)
).Nth(2)
``````

Or this one :

``````Events.Attendees.Email.ListCombine()
.Filter(
CurrentValue.Contains("Your_own_email_here").Not()
AND
CurrentValue.IsNotBlank()
).WithName(All,
All.Unique().WithName(Unique,
Unique.ForEach(
CurrentValue.WithName(U,
ListCombine(
All.Filter(CurrentValue.ContainsText(U,true,true)).Count(),
U
)
)
).Sort(false).First()
)
).Nth(2)
``````

I’ll sadly have to come back later to add explanations (Sorry )

@Pch Thanks for getting this started. So I want the formula to return something like…
You’ve met with these people the most
X email address = 30 times
Y email address = 21 times
Z email address = 10 times
etc

No problem @Christien_Louviere Sorry for the lack of explanations for the 2 previous formulas But this one follow a similar principle and renders the output as you wished .

``````Events.Attendees.ListCombine().Filter(
CurrentValue.Self.Not() AND CurrentValue.IsNotBlank()
).Email.WithName(All_Attendees,
All_Attendees.Unique().WithName(Unique_Attendees,
Unique_Attendees.ForEach(
CurrentValue.WithName(Attendee,
List(
All_Attendees.Filter(CurrentValue.ContainsText(Attendee,true,true)).Count(),
Attendee
)
)
).Sort(false)
)
).ForEach(
Format(
"{1} = {2} times",
CurrentValue.Nth(2),
CurrentValue.Nth(1)
)
).Join(LineBreak())
``````

So I actually discovered that `Attendees` from the GCal Pack have a `Self` property which I used here in the first step of the formula :

``````Events.Attendees.ListCombine().Filter(
CurrentValue.Self.Not() AND CurrentValue.IsNotBlank()
).Email.WithName(All_Attendees,
[...]
``````

Which creates a flat list (using `ListCombine()`) of all the `Attendees` from the `Events` table which is then filtered by `CurrentValue.Self.Not()` (in other words, where the property `Self` is not checked/is false, so my email would be left out) and `CurrentValue.IsNotBlank()` (because I had events where there where no attendees, so those empty values would be left out too).

From the resulting list of `Attendees` we can then access their email (which is also an `Attendees` property from the GCal Pack) and store the whole result (the whole list of emails) within `All_Attendees` using WithName().

Now that we have the complete list of email of all the attendees across all events in the table, we can take this list and only keep the unique values in the list…

``````[...]
All_Attendees.Unique().WithName(Unique_Attendees,
[...]

``````

… this new list of emails is then stored within `Unique_Attendees`

Then, the `CurrentValue` of each specific attendee (each specific email) in the list of all `Unique_Attendees` is stored within `Attendee` using ForEach() (so it can be used later) .

``````[...]
Unique_Attendees.ForEach(
CurrentValue.WithName(Attendee,
[...]
``````

And, still for each `Attendee` (each email) in the list, we can create a new list where the first item is the amount of how many times a specific attendee (email) appears in the list of all attendees (all emails), and the second item is the `Attendee` (the email)

``````[...]
List(
All_Attendees.Filter(CurrentValue.ContainsText(Attendee,true,true)).Count(),
Attendee
)
[...]
``````

So it returns something like :

For `Attendee`

• → A → [Y, Email A]
• → B → [Z, Email B]
• → C → [X, Email C]
• Etc…

And because `ForEach()` returns a list, so far we have a list of unsorted lists/pairs of `[Number, email]` .

As the number of times an email appears in the list of all emails is the first item in each pair of `[Number, email]`, we can then `Sort()` them by descending order (from the greatest to the least)

``````[...]
)
).Sort(false)
)
[...]
``````

And all that’s left is to Format() the result for each pair of `[Number, email]`:

``````.ForEach(
Format(
"{1} = {2} times",
CurrentValue.Nth(2),
CurrentValue.Nth(1)
)
).Join(LineBreak())
``````

So, for each pair of `[Number, email]` in the list, the placeholder `{1}` is replaced by `CurrentValue.Nth(2)` which is the `email` in the pair and the placeholder `{2}` is replaced `CurrentValue.Nth(1)` which is tis time the `Number` in the pair .

The values in the final resulting list are then joined by a `LineBreak()` Sorry for the length … But I hope this helps .

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.