How to make a timesheet from Google Calendar in Google Sheets

The short answer

Google Calendar can’t add up your hours by itself, but Google Sheets can, once the events are in a sheet. The whole job takes three steps:

1. Get the events into a sheet, one row per event with its start, end and length in hours. Use the free script below, our add-on CalendarRows, or any of the methods in our guide to exporting Google Calendar to Google Sheets.
2. Add four columns next to the events: the date, the week it belongs to, the client (or project), and the billable hours.
3. Total them with SUMIFS, QUERY or a pivot table: hours by client, by week, by month, and the amount to bill.

The one thing to get right before you start is how the calendar tells clients apart. A word in the event title (“Acme: design review”) is the most reliable; a colour or a separate calendar per client also works. The rest of this guide shows each step with formulas you can copy, plus the traps that make calendar timesheets wrong: all-day events, declined invitations, events that cross midnight and time zones.

Set up your calendar so the hours add up

A timesheet is only as good as the events behind it. If you’re starting fresh, or can change how you name events from today, a few habits make every later step simpler.

Put the client or project first in the title. “Acme: kickoff call”, “Acme: wireframes”, “Globex: monthly report”. A consistent first word lets one formula sort every event into the right client, and it still reads naturally in the calendar. If you track projects inside clients, use two levels: “Acme / Website: wireframes”. Avoid client names that are part of other words; “Art” matches “Start” and “Party”, so use a short code like “ART-” instead.

Or use one colour per client. Colours are quick to set and easy to see on the week view. They work well when you have a handful of clients and never share the calendar with someone who recolours events. The catch is that Google Calendar has only eleven event colours, and an event left on the calendar’s default colour has no colour name of its own, so keep the default for “not billable”.

Or use a separate calendar for work. Many people keep a “Work log” calendar next to their main one and only put time they intend to bill on it. That keeps lunches, school runs and personal appointments out of the timesheet entirely, which removes the most common source of wrong totals. You can still invite people to meetings from your main calendar and copy the block to the work log afterwards.

Mark what isn’t billable. Admin, internal meetings and travel often count as work hours for payroll but aren’t billed to a client. Give them a title word of their own, such as “Internal:” or “Admin:”, so they show up as their own line instead of quietly inflating someone’s invoice.

Log time as it really happened. A calendar shows what you planned. If a 60-minute call ran 80 minutes, drag the event to its real length that day; that’s the single habit that makes a calendar timesheet trustworthy. If you forget, the timesheet will faithfully total your plans, not your work.

Already have months of events with messy titles? That’s fine. Step 2 uses a small keyword table, so “acme”, “ACME call” and “Call with Acme team” can all count as the same client without renaming anything.

Step 1: get the events into a sheet

You need one row per event with at least the title, the start, the end and the length in hours. There are three practical ways, all covered in more depth in our Calendar to Sheets export guide:

• The free timesheet script in the next section. It already leaves out all-day events and invitations you declined, works out the week, and sorts each event into a client from the title, so you can skip most of Step 2.
• CalendarRows, our add-on. Pick a calendar and a date range in a sidebar and the events land in a new tab, with a Duration (hours) column ready to total. No code, and you can bring edits back to the calendar later. See Doing it with CalendarRows.
• Exporting the .ics file from Google Calendar’s settings. It’s free, but the file isn’t a spreadsheet, so it needs converting first, and recurring events come out as one rule rather than one row per meeting. For a timesheet that’s the wrong shape; use one of the other two.

Whichever you choose, export one period at a time (a week, a month, a billing cycle) into its own tab. A tab per period keeps old timesheets unchanged after you’ve sent an invoice, and lets you compare months side by side.

The free timesheet script

This Apps Script reads one calendar for the dates you choose and writes a ready-to-total timesheet tab: Date, Week starting, Client, Title, Start, End, Hours. To use it, open your spreadsheet, choose Extensions → Apps Script, replace the sample code with the code below, change the four settings at the top, click Save and then Run. The first time, Google asks you to allow the script to see your calendars and edit the spreadsheet; that permission is for your own script in your own account.

// Google Calendar -> timesheet tab: one row per timed event, with the date, the week it belongs to, the client and the hours.
// Paste into Extensions > Apps Script of the spreadsheet, set the four settings, Save, then Run buildTimesheet.
// All-day events and invitations you declined are left out. Dates and times use the spreadsheet's time zone.
const CALENDAR_ID = 'primary';   // or a Calendar ID from the calendar's Settings > Integrate calendar
const FROM = '2026-10-01';       // first day, yyyy-mm-dd
const TO = '2026-10-31';         // last day, included
const CLIENTS = [                // a word in the event title -> the client it belongs to (first match wins, any case)
  ['acme', 'Acme Ltd'],
  ['globex', 'Globex'],
  ['internal', 'Internal'],
];

function buildTimesheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tz = ss.getSpreadsheetTimeZone();
  const cal = CALENDAR_ID === 'primary' ? CalendarApp.getDefaultCalendar() : CalendarApp.getCalendarById(CALENDAR_ID);
  if (!cal) throw new Error('No calendar with the ID ' + CALENDAR_ID + ' (check the ID and that you can see it).');
  const day = (s, add) => { const p = s.split('-').map(Number); return new Date(p[0], p[1] - 1, p[2] + (add || 0)); };
  const from = day(FROM), to = day(TO, 1);
  const rows = [];
  cal.getEvents(from, to).forEach(function (e) {
    if (e.isAllDayEvent()) return;                                   // holidays, out-of-office days, reminders
    if (e.getMyStatus() === CalendarApp.GuestStatus.NO) return;      // invitations you declined
    const start = e.getStartTime(), end = e.getEndTime();
    if (start < from) return;                                        // started before FROM: it belongs to the previous period
    const date = Utilities.formatDate(start, tz, 'yyyy-MM-dd');
    const d = date.split('-').map(Number), u = new Date(Date.UTC(d[0], d[1] - 1, d[2]));
    u.setUTCDate(u.getUTCDate() - (u.getUTCDay() + 6) % 7);          // back to the Monday of that week
    const title = e.getTitle(), low = title.toLowerCase();
    const hit = CLIENTS.find(function (c) { return low.indexOf(c[0].toLowerCase()) !== -1; });
    rows.push([date, u.toISOString().slice(0, 10), hit ? hit[1] : '(none)', title,
      Utilities.formatDate(start, tz, 'yyyy-MM-dd HH:mm'), Utilities.formatDate(end, tz, 'yyyy-MM-dd HH:mm'),
      Math.round((end - start) / 36e5 * 100) / 100]);
  });
  rows.sort(function (a, b) { return a[4] < b[4] ? -1 : a[4] > b[4] ? 1 : 0; });
  rows.unshift(['Date', 'Week starting', 'Client', 'Title', 'Start', 'End', 'Hours']);
  const name = 'Timesheet ' + FROM + ' to ' + TO;
  if (ss.getSheetByName(name)) throw new Error('A tab called "' + name + '" already exists. Rename or delete it first.');
  const tab = ss.insertSheet(name);
  tab.getRange(1, 1, rows.length, 7).setValues(rows);
  tab.setFrozenRows(1);
  if (rows.length > 1) {
    tab.getRange(2, 1, rows.length - 1, 2).setNumberFormat('yyyy-mm-dd');
    tab.getRange(2, 5, rows.length - 1, 2).setNumberFormat('yyyy-mm-dd hh:mm');
  }
}

What it does, and why:

• Skips all-day events. Holidays, “Out of office” days and birthday reminders have no working hours, and counting them as 24 hours would wreck the totals.
• Skips invitations you declined. They stay on your calendar (greyed out) but you didn’t attend. Invitations you haven’t answered and “maybe” replies are kept, because many people never click Yes on recurring meetings they do attend.
• Counts each meeting of a repeating event separately, so a weekly call shows up four or five times in a month, as it should.
• Uses the spreadsheet’s time zone (File → Settings) for dates and times, and starts weeks on Monday. Set the script’s own time zone to match under Project Settings so FROM and TO mean the same days.
• Puts an event in the period it starts in. An event that started the night before FROM is left out, and a night shift that starts on the last day is kept whole.
• Never overwrites anything. It always creates a new tab named after the dates, and stops if that tab already exists.

The CLIENTS list matches words anywhere in the title, ignoring capitals, and the first match wins, so put longer, more specific words first (“acme web” before “acme”). Events that match nothing get “(none)”; filter the Client column for it after each run to catch a client you forgot to add. If you’d rather total by colour, the script can read e.getColor() instead, but titles are easier to audit later.

To run it every week without clicking, add a time-driven trigger (the clock icon in Apps Script → Add Trigger → buildTimesheet → Time-driven → Week timer), and change FROM and TO to be worked out from today’s date. For most people, running it by hand at the end of each billing period is simpler and less surprising.

If you’d prefer Google’s own version, Google publishes a calendar timesheet sample for Apps Script that you can copy. It’s a good starting point if you’re comfortable editing code; ours is shorter and does the client and week columns for you.

Step 2: add the timesheet columns

If you used the script, you already have Date, Week starting, Client and Hours; skip to Step 3 and use the column letters from that tab (Hours is G). If you imported with CalendarRows or another tool, add the columns yourself. The examples below assume a tab called Hours laid out like a CalendarRows import with the default columns:

Your import may have a few more columns on the right (CalendarRows adds its event ID and status). Add the new ones after the last column; the letters below assume they start at I. Rename the tab to Hours (double-click the tab name), or change Hours! in the formulas to your tab’s name in quotes, such as 'Events Work 2026-10-01 to 2026-10-31'!.

Date (I2, then fill down). Strips the time so you can group by day:

=INT(B2)

Format the column as a date (Format → Number → Date).

Week starting (J2). The Monday of the event’s week, which makes weekly totals one simple match:

=INT(B2)-WEEKDAY(B2, 3)

WEEKDAY(…, 3) counts Monday as 0, so subtracting it always lands on Monday. If your week starts on Sunday, use =INT(B2)-WEEKDAY(B2, 1)+1 instead. Don’t use WEEKNUM for timesheets: week numbers restart in January, so the last days of December and the first days of January can share a number but belong to different years.

Client (K2). Make a small tab called Clients with a keyword in column A, the client’s name in B and the hourly rate in C:

Then this formula finds the first keyword that appears anywhere in the title, ignoring capitals:

=IFERROR(INDEX(Clients!$B$2:$B$50, MATCH(1, ARRAYFORMULA(ISNUMBER(SEARCH(Clients!$A$2:$A$50, A2)) * (Clients!$A$2:$A$50<>"")), 0)), "(none)")

The (Clients!$A$2:$A$50<>"") part matters: without it, an empty row in the keyword table matches every title. Several keywords can point to the same client (“acme” and “roadrunner project” both to Acme Ltd), which is how you cope with old, inconsistent titles. For a project column, add a second table of project keywords and repeat the formula in another column.

If you sort clients by colour instead, import the Color column and use the colour name as the keyword, matched exactly: =IFERROR(VLOOKUP(G2, Clients!$A$2:$B$50, 2, FALSE), "(none)"), where G holds the colour.

Billable hours (L2). Zero for all-day events, and rounded up to the next quarter hour, which is the most common billing rule:

=IF(D2=TRUE, 0, CEILING(ROUND(E2*60, 0), 15)/60)

The formula turns hours into whole minutes first, so a 20-minute call is exactly 20 minutes and not 19.8 from rounding. Change 15 to 6 for tenths of an hour, 30 for half hours, or remove the CEILING for exact time. To make internal time non-billable, wrap it: =IF(K2="Internal", 0, …).

Rate and Amount (M2 and N2). Look up the client’s rate and multiply:

=IFERROR(INDEX(Clients!$C$2:$C$50, MATCH(K2, Clients!$B$2:$B$50, 0)), 0)
=L2*M2

Rather than filling each formula down, you can put one formula per column in row 2 that fills itself as the import grows, for example =ARRAYFORMULA(IF(B2:B="", , INT(B2:B)-WEEKDAY(B2:B, 3))) for the week. The fill-down versions are easier to read and check, which is worth more on a timesheet than saving a few seconds.

Step 3: totals by client, project and week

Put the totals on their own tab, say Summary, so they never get mixed up with the event rows. All the formulas below read whole columns, so they keep working when you add rows.

A list of clients. In A2 of the Summary tab:

=SORT(UNIQUE(FILTER(Hours!K2:K, Hours!K2:K<>"")))

Hours per client next to each name (B2, fill down), leaving out all-day events:

=SUMIFS(Hours!E:E, Hours!K:K, A2, Hours!D:D, FALSE)

Use Hours!L:L instead of Hours!E:E for rounded billable hours, and Hours!N:N for the amount.

Without a client column at all, a wildcard on the title does the same job for one client:

=SUMIFS(Hours!E:E, Hours!A:A, "*acme*", Hours!D:D, FALSE)

The asterisks mean “anything before or after”, and the match ignores capitals. It’s the quickest way to answer “how many hours did I spend on Acme last month?” when you have no time to set anything up.

Hours in one week. With the week’s Monday in a cell, for example D1:

=SUMIFS(Hours!E:E, Hours!J:J, D1, Hours!D:D, FALSE)

Hours for one client in one month. With any date of that month in D1:

=SUMIFS(Hours!E:E, Hours!K:K, A2, Hours!D:D, FALSE, Hours!B:B, ">="&EOMONTH(D1, -1)+1, Hours!B:B, "<"&EOMONTH(D1, 0)+1)

Everything at once with QUERY. One formula that builds the whole client-by-week table, one row per week and client:

=QUERY(Hours!A1:N, "select J, K, sum(E), sum(L), sum(N) where D = false and E is not null group by J, K order by J, K label J 'Week starting', K 'Client', sum(E) 'Hours', sum(L) 'Billable', sum(N) 'Amount'", 1)

Format the first column of the result as a date. For a grid with weeks across the top and clients down the side, replace the select part with select K, sum(E) where D = false and E is not null group by K pivot J.

Or a pivot table, no formulas. Select the Hours tab, choose Insert → Pivot table, and set Rows to Client, Columns to Week starting, Values to SUM of Duration (hours), and add a Filter on All day showing only FALSE. Pivot tables are the friendliest option if you share the timesheet with someone who doesn’t like formulas, and you can drag Client and Week around to look at the data both ways.

Whichever you use, check the grand total once against the plain sum of hours for timed events, =SUMIFS(Hours!E:E, Hours!D:D, FALSE). If they differ, an event has a client the summary doesn’t list, usually “(none)”.

Billable hours, rounding and invoices

Turning hours into an invoice adds a few decisions. Make them once, write them down next to the Clients table, and the timesheet does the rest.

Round each event or the total? Rounding every event up to 15 minutes, as the Billable formula above does, is common for client work and easy to explain: a 5-minute call is billed as 15. It also adds up quickly if you have many short calls, so some contracts round only the daily or monthly total. To round a total instead, keep exact hours in the rows and round at the end: =CEILING(ROUND(SUMIFS(…)*60, 0), 15)/60. Whatever you choose, use the same rule every month and say so on the invoice.

Minimum charges. If a call-out or meeting has a one-hour minimum, use =MAX(1, CEILING(ROUND(E2*60, 0), 15)/60) for that client’s rows, or add a Minimum column to the Clients table and use MAX with a lookup.

Different rates for different work. When the same client pays one rate for design and another for support, put the rate on the project instead of the client: a Projects table with keyword, project, client and rate, and the same lookup formula against it.

Show the detail. Clients trust an invoice they can check. A copy of the client’s rows (date, title, billable hours) attached to the invoice answers most questions before they’re asked. This makes a ready list for one client and one month, with the client in A2 and the month’s first day in D1:

=FILTER({Hours!I2:I, Hours!A2:A, Hours!L2:L}, Hours!K2:K=A2, Hours!D2:D=FALSE, Hours!B2:B>=D1, Hours!B2:B<EOMONTH(D1, 0)+1)

Review event titles before you share the list; a calendar title written for yourself (“Acme: argue about scope again”) isn’t always one you want on an invoice.

Freeze the numbers once billed. After you send an invoice, copy the month’s summary and use Paste special → Values only on a separate tab. If someone later moves an event in the calendar and you re-import, the old invoice still matches what you billed.

Hours for payroll and overtime

Calendar timesheets are also used for paying people: part-time staff, shift workers, tutors and contractors who put their shifts in a shared calendar. The same sheet works, with a few extra checks.

One person per calendar, or a name in the title. If each person has their own calendar, export each one into its own tab. If everyone shares one schedule calendar, put the person’s name in the title (“Maria: front desk”) and use it the same way as the client keyword. If you create the shifts from a spreadsheet in the first place, our guide to sending a shift schedule from Google Sheets to Google Calendar shows how to keep the names consistent.

Weekly overtime. With the person’s weekly total in B2, hours above 40 are:

=MAX(0, B2-40)

and regular hours are =MIN(B2, 40). In the United States, the federal rule for most hourly (non-exempt) employees is time and a half for hours over 40 in a workweek, and the workweek is a fixed, repeating seven-day period that you choose, which is exactly what the Week starting column gives you. Some states add daily overtime, and other countries have different rules, so check what applies to you; this guide is about the spreadsheet, not legal or payroll advice.

Unpaid breaks. A calendar block for a whole shift includes the lunch break. Either put breaks in the calendar as their own events with a word such as “Break” and leave them out with "<>*break*" in SUMIFS, or subtract a fixed break per shift over a certain length: =IF(E2>6, E2-0.5, E2) for a half-hour break on shifts over six hours.

Night shifts. A shift from 22:00 to 06:00 is one event of 8 hours, and both the script and CalendarRows count it in the day and week it starts. That’s the usual payroll convention, but if your rules split hours at midnight, you’ll need a split column for those shifts.

Approval. Calendars are easy to edit after the fact. For payroll, export once at the end of the period, share the Summary with the person to confirm, and keep the tab as the record you paid from.

Doing it with CalendarRows

CalendarRows is our Google Sheets add-on for moving events between a sheet and Google Calendar in both directions. For timesheets, its import does Step 1 without any code:

1. In your spreadsheet, choose Extensions → CalendarRows → Import events from Calendar.
2. Pick the calendar, then a date range, or one of the quick ranges such as This month or Last 30 days.
3. Optionally type a word in Only events containing, for example a client’s name, to import only matching events. This uses Google Calendar’s own search, so it can also match that word in an event’s description or location.
4. Tick the columns you want. Title, Start, End, All day, Duration (hours) and Location are on by default; Description, Guests, Color, Event status and a Calendar link are optional. Add Color if you sort clients by colour.
5. Click Import events. The events go into a new tab, so nothing you already have is overwritten.

Then add the columns from Step 2 and the totals from Step 3. A few things to know so the totals come out right:

• Duration (hours) is the length of each timed event in hours, to two decimals. For an all-day event it shows 24 hours per day, so always leave out rows where All day is TRUE, as every formula in this guide does.
• Each meeting of a repeating event is its own row, and cancelled events are left out.
• CalendarRows doesn’t read your reply to invitations, so declined invitations are imported like any other event. Delete those rows, or use the free script above, which skips them.
• Free imports up to 100 events at a time, and Pro up to 5,000. Imports don’t count toward the daily calendar changes. If a month has more than 100 events on Free, the import stops at 100 and tells you; import the month in two halves and stack them.
• Because each row keeps its event ID, you can fix an event in the sheet (a wrong end time, say) and send it back with Send rows to Calendar to update the event instead of creating a copy.

When is the add-on worth it over the script? If you’d rather not paste code, if you’re setting this up for someone else, or if you also send rows to Google Calendar (shifts, bookings, deadlines), it saves time. If you just need a monthly timesheet from one calendar and you’re happy with Apps Script, the free script does that job well.

Other ways to get hours from Google Calendar

Time insights in Google Calendar. On a computer, some work and school accounts have a Time insights panel next to the calendar that shows how much time you spent in meetings over a week or month. It’s a quick overview of your own time, but it doesn’t total by client, round billable time or export a timesheet, and it isn’t available on personal Gmail accounts.

Dedicated calendar timesheet tools. Several apps and add-ons connect to Google Calendar and produce hours reports directly, often with rates, rounding rules, monthly statements and PDF exports built in. They’re worth a look if you bill many clients every week and want a finished report without any formulas. Check what each one keeps on its own servers, how many events its free plan covers, and whether the report can be exported back into a spreadsheet you own.

A time tracker instead of a calendar. If most of your work isn’t scheduled in advance, a start-stop timer app will be more accurate than calendar blocks, because it records what you did rather than what you planned. Many people use both: the calendar for meetings and appointments, a timer for desk work, and one sheet that adds the two.

Typing it by hand. For a handful of events a month, a manual sheet with Date, Client, Start and End and =(D2-C2)*24 for the hours is perfectly fine. The point of using the calendar is to avoid that typing once the list gets long.

Common problems

The totals are far too high. All-day events are being counted. Check that every SUMIFS includes Hours!D:D, FALSE, or delete the all-day rows. Next most likely: declined invitations, or the same meeting on two calendars that you exported into one tab.

SUMIFS returns 0. The Start column is text, not a date and time, so date comparisons fail. Click a cell: if it’s left-aligned, it’s text. Check the spreadsheet’s locale under File → Settings, or convert it in a spare column with =DATEVALUE(LEFT(B2, 10))+TIMEVALUE(MID(B2, 12, 5)) for text like 2026-10-05 09:00. Also check that the client name in the summary matches the Clients table exactly, including spaces.

Hours show as a time like 1:30 instead of 1.5. Somebody subtracted End from Start without multiplying by 24, which gives a fraction of a day. Use =(C2-B2)*24 for decimal hours, or format the cell as Duration (which shows totals over 24 hours correctly as, say, 42:30:00).

Events are an hour off. The spreadsheet’s time zone differs from the calendar’s. Set it under File → Settings → Time zone and import again. Daylight saving changes are handled correctly as long as both use the same named zone.

A client is missing from the summary. Filter the Client column for “(none)”. The title probably uses a spelling the Clients table doesn’t have yet; add it as another keyword for the same client.

The same week appears twice in the summary. The Week starting column has times in it. Make sure it uses INT(B2), not B2, so every event in a week gets the same plain date.

Events from a shared calendar show only “busy”. You can see free/busy information but not event details for that calendar. Ask the owner to share it with “See all event details”, or have them export it themselves.

Frequently asked questions

Related

• How to export Google Calendar to Google Sheets
• How to put a shift schedule from Google Sheets into Google Calendar
• How to create Google Calendar events from Google Sheets
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. The free script and every formula here work without it, and we say when another tool is the better fit.

Google Sheets, Google Calendar and Google Workspace are trademarks of Google LLC. CalendarRows and Cellbrook are not affiliated with or endorsed by Google.