Build a Time Tracker in Google Sheets with Apps Script

A Google Sheet makes a surprisingly good time tracker — especially when backed by Apps Script for clock-in/out buttons, automatic duration calculations, and weekly summaries.

Sheet structure

Create a sheet named Time Log with these columns:

Clock in and clock out

function clockIn() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Time Log');
  const now = new Date();

  // Check if already clocked in (last row has Clock In but no Clock Out)
  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    const clockOut = sheet.getRange(lastRow, 3).getValue();
    if (!clockOut) {
      SpreadsheetApp.getUi().alert('You are already clocked in. Clock out first.');
      return;
    }
  }

  sheet.appendRow([now.toDateString(), now, '', '', '']);
  SpreadsheetApp.getUi().alert(`Clocked in at ${now.toLocaleTimeString()}`);
}

function clockOut() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Time Log');
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('No active clock-in found.');
    return;
  }

  const clockIn  = sheet.getRange(lastRow, 2).getValue();
  const clockOut = sheet.getRange(lastRow, 3).getValue();

  if (clockOut) {
    SpreadsheetApp.getUi().alert('Already clocked out. Use Clock In to start a new session.');
    return;
  }

  const now = new Date();
  const durationHrs = (now - new Date(clockIn)) / (1000 * 60 * 60);

  sheet.getRange(lastRow, 3).setValue(now);
  sheet.getRange(lastRow, 4).setValue(parseFloat(durationHrs.toFixed(2)));

  SpreadsheetApp.getUi().alert(
    `Clocked out at ${now.toLocaleTimeString()}\nDuration: ${durationHrs.toFixed(2)} hrs`
  );
}

Add a custom menu

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('⏱ Time Tracker')
    .addItem('Clock In',  'clockIn')
    .addItem('Clock Out', 'clockOut')
    .addSeparator()
    .addItem('Weekly Summary', 'showWeeklySummary')
    .addToUi();
}

Calculate weekly totals

function getWeeklyTotals() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Time Log');
  const data = sheet.getDataRange().getValues().slice(1); // Skip header

  const totals = {};

  data.forEach(([date, , , duration]) => {
    if (!date || !duration) return;

    const d = new Date(date);
    const year = d.getFullYear();
    const week = getWeekNumber(d);
    const key = `${year}-W${week}`;

    totals[key] = (totals[key] || 0) + duration;
  });

  return totals;
}

function getWeekNumber(date) {
  const d = new Date(date);
  d.setHours(0, 0, 0, 0);
  d.setDate(d.getDate() + 3 - (d.getDay() + 6) % 7);
  const week1 = new Date(d.getFullYear(), 0, 4);
  return 1 + Math.round(((d - week1) / 86400000 - 3 + (week1.getDay() + 6) % 7) / 7);
}

function showWeeklySummary() {
  const totals = getWeeklyTotals();
  const lines = Object.entries(totals)
    .sort()
    .map(([week, hrs]) => `${week}: ${hrs.toFixed(2)} hrs`)
    .join('\n');

  SpreadsheetApp.getUi().alert('Weekly Summary\n\n' + lines);
}

Write weekly totals to a summary sheet

function writeWeeklySummary() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let summarySheet = ss.getSheetByName('Weekly Summary');

  if (!summarySheet) {
    summarySheet = ss.insertSheet('Weekly Summary');
    summarySheet.appendRow(['Week', 'Total Hours']);
  } else {
    summarySheet.clearContents();
    summarySheet.appendRow(['Week', 'Total Hours']);
  }

  const totals = getWeeklyTotals();
  Object.entries(totals).sort().forEach(([week, hrs]) => {
    summarySheet.appendRow([week, parseFloat(hrs.toFixed(2))]);
  });

  Logger.log('Weekly summary written.');
}

Tips

Format columns B and C with Time format (Format > Number > Time) for clean display.
Format column D with Number (2 decimal places) for hours.
Add conditional formatting to highlight rows where duration exceeds 8 hours.
For team tracking, add a Name column and filter summaries per person.
You can also trigger clockIn / clockOut via a button: Insert > Drawing, draw a button shape, then assign the script function.