At Henricks Media, we’re passionate about equipping businesses with practical tools to simplify their workflows. One handy solution we’ve put together is a bug tracking system built in Google Sheets—a budget-friendly alternative to tools like Jira. Perfect for web developers, project managers, and small business owners, this DIY setup is quick to implement and easy to use. Let’s dive into how to get it up and running, and see why it’s a great choice for your team!

Why Choose a Google Sheets Bug Tracker?

For web development teams, keeping track of bugs efficiently is key. While traditional tools can be complex and costly, often requiring subscriptions and a steep learning curve, a Google Sheets-based bug tracker offers:

• Cost-Effectiveness: Free with your Google account.
• Customization: Easily tailored to your needs.
• Collaboration: Seamless sharing through Google’s ecosystem.

This solution uses Google Sheets and Apps Script to deliver an automated experience, making bug tracking straightforward and accessible.

System Overview

This bug tracker auto-generates 6-digit Bug IDs (starting at BUG-000100), applies dropdown menus for key fields, and timestamps entries—all within Google Sheets. Here’s what you’ll get:

• Spreadsheet Name: Bug Tracker
• Sheet Name: Bugs
• Columns:
  • A: Bug ID (auto-generated, e.g., BUG-000100, BUG-000101)
  • B: Summary
  • C: Status (dropdown: To Do, In Progress, Done)
  • D: Priority (dropdown: Low, Medium, High)
  • E: Assignee (dropdown: John Smith, Jane Doe, Mike Johnson, Sarah Brown, Tom Wilson)
  • F: Reporter (dropdown: John Smith, Jane Doe, Mike Johnson, Sarah Brown, Tom Wilson)
  • G: Date Reported (auto-set to current date and time)
  • H: Date Resolved

The system uses Google Apps Script to automate these features, triggered when you add a new bug summary, making bug tracking both efficient and user-friendly.

Step-by-Step Guide to Set Up Your Bug Tracker

Follow this guide to get your bug tracker up and running:

1. Open Your Spreadsheet

• Launch Google Sheets and create a new spreadsheet named “Bug Tracker.”
• Rename the default tab to “Bugs” (right-click the tab at the bottom and select “Rename”).

2. Set Up the Column Headers

• Enter these headers in row 1:
  • A1: Bug ID
  • B1: Summary
  • C1: Status
  • D1: Priority
  • E1: Assignee
  • F1: Reporter
  • G1: Date Reported
  • H1: Date Resolved

3. Implement the Script

1. Access Script Editor:
   • Click Extensions > Apps Script in the Google Sheets menu.
2. Add the Script:
   • Replace the contents of Code.gs with this code:

function onEdit(e) {
  if (!e) return;
  var sheet = e.source.getActiveSheet();
  Logger.log("Active sheet: " + sheet.getName());
  if (sheet.getName() !== "Bugs") {
    Logger.log("Wrong sheet: " + sheet.getName());
    return;
  }
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  Logger.log("Editing row: " + row + ", column: " + col);
  if (col >= 2 && row > 1) {
    var bugIdCol = 1;
    var bugIdCell = sheet.getRange(row, bugIdCol);
    Logger.log("Bug ID cell (A" + row + ") value: " + bugIdCell.getValue());
    if (!bugIdCell.getValue()) {
      var previousId = row > 2 ? sheet.getRange(row - 1, bugIdCol).getValue() : "BUG-000099";
      var num = previousId ? parseInt(previousId.split("-")[1]) + 1 : 100;
      var newId = "BUG-" + ("000000" + num).slice(-6);
      Logger.log("Setting new Bug ID: " + newId);
      bugIdCell.setValue(newId);
      var statusCell = sheet.getRange(row, 3);
      var statusRule = SpreadsheetApp.newDataValidation()
        .requireValueInList(["To Do", "In Progress", "Done"], true)
        .setAllowInvalid(false)
        .build();
      statusCell.setDataValidation(statusRule);
      var priorityCell = sheet.getRange(row, 4);
      var priorityRule = SpreadsheetApp.newDataValidation()
        .requireValueInList(["Low", "Medium", "High"], true)
        .setAllowInvalid(false)
        .build();
      priorityCell.setDataValidation(priorityRule);
      var assigneeCell = sheet.getRange(row, 5);
      var assigneeRule = SpreadsheetApp.newDataValidation()
        .requireValueInList(["John Smith", "Jane Doe", "Mike Johnson", "Sarah Brown", "Tom Wilson"], true)
        .setAllowInvalid(false)
        .build();
      assigneeCell.setDataValidation(assigneeRule);
      var reporterCell = sheet.getRange(row, 6);
      var reporterRule = SpreadsheetApp.newDataValidation()
        .requireValueInList(["John Smith", "Jane Doe", "Mike Johnson", "Sarah Brown", "Tom Wilson"], true)
        .setAllowInvalid(false)
        .build();
      reporterCell.setDataValidation(reporterRule);
      var dateReportedCell = sheet.getRange(row, 7);
      dateReportedCell.setValue(new Date());
      dateReportedCell.setNumberFormat("MM/dd/yyyy HH:mm:ss");
    } else {
      Logger.log("Bug ID already exists in A" + row);
    }
  } else {
    Logger.log("Edit outside target range (col < 2 or row <= 1)");
  }
}

function testBugId() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = sheets.map(sheet => sheet.getName());
  Logger.log("Available sheets: " + sheetNames);
  var sheet = spreadsheet.getSheetByName("Bugs");
  if (!sheet) {
    Logger.log("Sheet 'Bugs' not found!");
    return;
  }
  var bugIdCell = sheet.getRange(2, 1);
  if (!bugIdCell.getValue()) {
    bugIdCell.setValue("BUG-000100");
    var statusCell = sheet.getRange(2, 3);
    var statusRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["To Do", "In Progress", "Done"], true)
      .setAllowInvalid(false)
      .build();
    statusCell.setDataValidation(statusRule);
    var priorityCell = sheet.getRange(2, 4);
    var priorityRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["Low", "Medium", "High"], true)
      .setAllowInvalid(false)
      .build();
    priorityCell.setDataValidation(priorityRule);
    var assigneeCell = sheet.getRange(2, 5);
    var assigneeRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["John Smith", "Jane Doe", "Mike Johnson", "Sarah Brown", "Tom Wilson"], true)
      .setAllowInvalid(false)
      .build();
    assigneeCell.setDataValidation(assigneeRule);
    var reporterCell = sheet.getRange(2, 6);
    var reporterRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["John Smith", "Jane Doe", "Mike Johnson", "Sarah Brown", "Tom Wilson"], true)
      .setAllowInvalid(false)
      .build();
    reporterCell.setDataValidation(reporterRule);
    var dateReportedCell = sheet.getRange(2, 7);
    dateReportedCell.setValue(new Date());
    dateReportedCell.setNumberFormat("MM/dd/yyyy HH:mm:ss");
  }
}

1. Save the Script:
   • Click the floppy disk icon and name the project (e.g., “AutoBugID”).
2. Authorize the Script:
   • Run the testBugId function (optional) by selecting it from the dropdown next to “Run” and clicking “Run” to authorize the script when prompted.
3. Set Up Trigger:
   • Click the clock icon (Triggers) in the script editor.
   • Click + Add Trigger.
   • Set: onEdit, Head, From spreadsheet, On edit.
   • Save and authorize if prompted.

4. Start Tracking Bugs

• Open the “Bugs” tab and enter a bug summary in column B (e.g., “Login fails” in B2).
• The script will auto-generate a Bug ID in A2 (BUG-000100), apply dropdowns for C, D, E, and F, and set the current date/time in G2.
• Select values from the dropdowns (e.g., To Do for Status, Low for Priority, John Smith for Assignee, Jane Doe for Reporter).
• Add more bugs in subsequent rows (e.g., B3) to see BUG-000101 and so on.

Note on Customization: The Assignee and Reporter dropdowns come with generic names (John Smith, Jane Doe, Mike Johnson, Sarah Brown, Tom Wilson) as examples. To tailor this tool to your team, edit the script in the Apps Script editor. Locate the onEdit and testBugId functions and replace these names with the actual names of your team members in the requireValueInList arrays for columns E and F. Save the script after making changes to apply the updates.

Why Henricks Media Excels in Tech Solutions

At Henricks Media, we’re committed to delivering tools that fit your workflow. This bug tracker highlights our ability to turn Google Sheets into a powerful asset through custom scripting—a skill we apply across our media and tech projects. Whether you need automation for content management or team collaboration, our expertise ensures practical, impactful results.

Best Practices for Effective Bug Tracking

Logging

• Be Descriptive: Use clear summaries like “Form submission fails on Chrome v120.”
• Add Context: Note the environment or steps to reproduce.
• Assign Roles: Use the Assignee and Reporter dropdowns to track ownership.
• Trust the Timestamp: The Date Reported is auto-set for accuracy.

Review

• Stay Consistent: Check the sheet weekly to prioritize bugs.
• Collaborate: Discuss updates in team meetings and adjust Status or Priority.
• Verify Fixes: Test resolutions before marking as “Done.”

Closure

• Mark Complete: Set Status to “Done” only after verification.
• Log Resolution: Add the Date Resolved in column H.
• Archive: Move resolved bugs to a new sheet to keep the active list lean.

Troubleshooting Tips

If something doesn’t work as expected:

• Check View > Logs in the script editor for clues (e.g., “Wrong sheet” or “Edit outside target range”).
• Reauthorize by running testBugId (optional) if needed.
• Ensure the trigger is set (see Step 3.5).
• Contact Henricks Media for expert support.

Enhance Your Bug Tracker

• Auto-Resolved Dates: Add =IF(C2=”Done”, TODAY(), “”) in column H.
• Visual Cues: Use conditional formatting (e.g., green for “Done,” red for “High”).
• Customization: Let Henricks Media tailor this further for your team!

Conclusion

This Google Sheets bug tracker proves that powerful tools can be simple and cost-effective. At Henricks Media, we’re dedicated to empowering businesses with solutions like this. Visit henricksmedia.com to learn how we can elevate your projects with custom tech solutions. Share your feedback in the comments—we’d love to hear how this works for you!