I am creating a spreadsheet in Google Sheets for a client who wants the sheet to pull data from their Google Calendar to calculate how many days each employee works. They use Calendar for their scheduling.

So I know that I need the formula to look through the calendar for the specific employee name, and then count how many days they work in a 3-month period. Hours worked aren't counted, just days.

I need to have Sheets pull info from Calendar:

To fill in these fields:
Spreadsheet The client would like the spreadsheet to just stay up to date, but if that's not possible, then I would put a link or button in B1.

I'm thinking that I'll need formulae in A, B, C and D to the effect of =FIND("drname" =FIND("officeabbreviation", yadda yadda yadda. But I don't know the specifics of the yadda yadda yadda. I do have a table that I can MATCH INDEX for the Dr's names and Office abbreviations.

Edit 2 I do need to know how to import the Calendar to Sheets, as well as the formulas.

migrated from superuser.com Mar 6 at 18:52

This question came from our site for computer enthusiasts and power users.

  • it is possible but it really depends on how data looks like to choose the best approach. can you share a copy/dummy of your sheet? – user0 Mar 6 at 18:59
  • I'd use QUERY instead of MATCH INDEX. I had an Excel moment there. – Keiva Mar 27 at 17:59

For example, if you export all names under 1 column you can use simple QUERY to get what you want:

=QUERY({A1:A}, "select Col1, count(Col1) 
                where Col1 is not null 
                group by Col1
                label count(Col1)'Total days'", 1)


  • @user0, Thank you, but I'm not understanding how this connects to Calendar. How does it know which Calendar to pull from? For instance, in my Google acct., I have several Calendars. – Keiva Mar 17 at 20:10
  • @user0, That's the part I need to know. I'll edit the question to provide more info. Thank you! :) – Keiva Mar 27 at 17:37
  • @Keiva well, then pls do edit your question again and remove any traces of google sheets formulas, as this is question of how to export google calendar into google sheets not how to deal with formulas – user0 Mar 27 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.