Google Calendar: Count number of times an email address appears

[Christien_Louviere]

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?

[Pch]

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:

@Christien_Louviere

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

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 )

[Christien_Louviere]

@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

[Pch]

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 .