If you’ve ever felt like your study routine is kinda all over the place or if you’re just curious where all your “study time” actually goes then you’re not alone.

Maybe you’re prepping for exams, juggling organization work, reviewing for certifications, or just trying to build a better routine. Whatever it is, having a simple system to track your study time can help you stay accountable without needing to download another app or learn a whole new tool. (｡•̀ᴗ-)

Stick around and I’ll walk you through how to whip up your very own Smart Study Tracker using Google Sheets + a tiny bit of script magic ~
Nothing wild, promise! It’s clean, customizable, and something you actually own.

Wanted to do something like this?

• Automatically shows which task needs your attention first
• Highlights your high-priority goals
• Tracks how long you’ve actually been studying
• Changes colors depending on status and even celebrates when you’re done

If you’ve been wanting a tracker that works with you and not against you, I gotcha!

Here’s what we’re making:

What You’ll Learn

• How to make a smart study table in Google Sheets

• How to set up dropdowns and colors for priority and status

• How to auto-calculate study time



• How to add a sorting script 

Let’s start!

Now let’s make your own version from scratch.

By the end of this guide, you’ll have your very own Smart Study Tracker!

What You’ll Need:

• A Google account

• Google Sheets

• Basic copy-paste skills (you’re good!)

• The willingness to be honest with your time

Step 1: Set Up the Google Sheet

Open Google Sheets and create a new file. You can name it something like Study Tracker or whatever helps you feel productive.

Then, Click Insert > Pre-built tables > Blank Table > Insert

Step 2: Set Up Your Table

Edit your columns and set up your table like this:

Don’t forget to change the column type! 
Trust me, it makes a huge difference.

Google Sheets can be a bit wonky and the script won’t work if you skip this step, so make sure to:

• Format time columns (Start Time, End Time) as Time 

• Format the Deadline column as Date

• Format the Priority and Status as Dropdown

Here’s how to edit the column type:
Click the Column that you will edit (e.g. Start Time)
Then, Click the Dropdown, Edit Column Type > Date > Time

Step 3: Edit Dropdowns

For Priority and Status:

You’ll use Dropdowns:

Select the cells under Priority (Do this for Status too)

• Click the Dropdown, Tap the Edit Icon
• Type your options:
  • For Priority: High, Medium, Low
  • For Status: To Do, In Progress, Done
•  Add color styles to match each option!
• Click Done

Step 4: Add a Script to Make it Smart ✨

Now that the table is ready, let’s make this sheet smart.

Once you set this up, your tracker will:

• Calculate how long you studied just by entering a start and end time (no need to drag formulas)

• Automatically sort your tasks by priority (High, Medium, Low) and by deadline

• Count how many days are left before each deadline

• Highlight the entire row green when the task is marked as done

• Turn it red if it’s overdue and still not done

You don’t need to be a coder for this. Just follow these steps.

Open Extensions > Apps Script,

Delete the existing script, 

Then, paste this entire script:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var editedCell = e.range;
  var row = editedCell.getRow();
  if (row <= 1) return; // Skip header

  var today = new Date();
  today.setHours(0, 0, 0, 0);

  var priorityCol = 2;
  var deadlineCol = 3;
  var daysLeftCol = 4;
  var statusCol = 5;
  var startCol = 6;
  var endCol = 7;
  var durationCol = 8;

  var rowRange = sheet.getRange(row, 1, 1, sheet.getLastColumn());

  var deadline = sheet.getRange(row, deadlineCol).getValue();
  var status = sheet.getRange(row, statusCol).getValue();
  var start = sheet.getRange(row, startCol).getValue();
  var end = sheet.getRange(row, endCol).getValue();
  var daysLeftCell = sheet.getRange(row, daysLeftCol);

  // Duration calculation
  if (editedCell.getColumn() == startCol || editedCell.getColumn() == endCol) {
    var durationString = "Invalid Time";

    if (typeof start === 'number' && typeof end === 'number') {
      var diff = end - start;
      if (diff < 0) diff += 1;
      var diffMins = Math.round(diff * 24 * 60);
      var hours = Math.floor(diffMins / 60);
      var mins = diffMins % 60;
      durationString = hours + "h " + mins + "m";
    } else if (start instanceof Date && end instanceof Date) {
      var diffMs = end.getTime() - start.getTime();
      if (diffMs < 0) diffMs += 24 * 60 * 60 * 1000;
      var diffMins = Math.round(diffMs / (1000 * 60));
      var hours = Math.floor(diffMins / 60);
      var mins = diffMins % 60;
      durationString = hours + "h " + mins + "m";
    }

    sheet.getRange(row, durationCol).setValue(durationString);
  }

  // Days Left logic
  if (status === "Done") {
    daysLeftCell.setValue("N/A");
  } else if (deadline instanceof Date) {
    var diffDays = Math.ceil((deadline - today) / (1000 * 60 * 60 * 24));
    if (diffDays > 0) {
      daysLeftCell.setValue(diffDays + " day(s) left");
    } else if (diffDays === 0) {
      daysLeftCell.setValue("Due today");
    } else {
      daysLeftCell.setValue("Overdue by " + Math.abs(diffDays) + " day(s)");
    }
  } else {
    daysLeftCell.setValue("");
  }

  // Row color
  if (status === "Done") {
    rowRange.setBackground("#CCFFCC");
  } else if (deadline instanceof Date && deadline < today) {
    rowRange.setBackground("#FFCCCC");
  } else {
    rowRange.setBackground(null);
  }

  if ((editedCell.getColumn() == priorityCol || editedCell.getColumn() == deadlineCol) && row > 1) {
    autoSortByPriorityAndDeadline();
  }
}

function autoSortByPriorityAndDeadline() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 9);
  var data = dataRange.getValues();

  var priorityOrder = {
    "High": 1,
    "Medium": 2,
    "Low": 3
  };

  data.sort(function(a, b) {
    var priorityA = priorityOrder[a[1]] || 4;
    var priorityB = priorityOrder[b[1]] || 4;
    if (priorityA !== priorityB) return priorityA - priorityB;
    var deadlineA = a[2] ? new Date(a[2]) : new Date("9999-12-31");
    var deadlineB = b[2] ? new Date(b[2]) : new Date("9999-12-31");
    return deadlineA - deadlineB;
  });

  dataRange.setValues(data);
}

Save and close the script editor. That’s it! Your tracker is now smart and ready to work with you! 

Ready to make studying feel a little lighter?

And there you go. Sometimes all you really need is a simple Google Sheet to help you stay on top of things. It doesn’t have to be fancy or perfect, just something that works for you and keeps you honest about where your study time goes.

If you end up trying this study tracker, I’d love to know how it goes for you. Maybe you’ll come up with your own tweaks or shortcuts along the way. 

If you found this helpful, send it to a friend or anyone else who might want a calmer, clearer study routine.

You’ve got this! ദ്ദി ˉ͈̀꒳ˉ͈́ )✧