I’ve got this cool idea for Google Sheets but I’m not sure how to make it work. Here’s what I want to do:
I want cells to change color based on when they were last edited. This would make it super easy to spot recent changes when looking at a spreadsheet.
My ideal setup would be:
- Light green for edits in the last 2 hours
- Light yellow for edits in the last 12 hours
- Light red for edits in the last 48 hours
- Back to normal after 48 hours
This would be really helpful for tracking changes in shared spreadsheets. Has anyone done something like this before? Any tips on how to set it up?
I’m pretty new to advanced Google Sheets stuff, so step-by-step instructions would be awesome if you’ve got them. Thanks in advance for any help!
I’ve implemented something similar in my work spreadsheets, Noah_Fire. It’s definitely doable but requires a bit of setup. Here’s a rough outline:
- Create a hidden column for timestamps.
- Use Apps Script with onEdit() to update timestamps when cells are edited.
- Set up conditional formatting rules based on the timestamp column.
The trickiest part is getting the script to update timestamps correctly. You’ll need to compare the old and new values of edited cells to avoid unnecessary updates. Also, be aware that this setup can slow down your spreadsheet if you have a lot of data or frequent edits.
One tip: consider using a slightly longer timeframe for your color coding. In my experience, 6 hours / 24 hours / 72 hours worked better for tracking changes in a busy shared spreadsheet.
If you need more detailed guidance, I’d be happy to share some code snippets. Good luck with your project!
This is an interesting challenge, Noah_Fire. Conditional formatting alone cannot access edit times, so you will need to use Apps Script alongside a hidden timestamp column. One approach is to update a hidden column with the current timestamp whenever a cell is edited, and then apply conditional formatting based on these timestamps. You may also consider using a time-driven trigger to check and refresh old timestamps periodically. I have experimented with similar setups in Google Sheets and found that careful use of the SpreadsheetApp class and time comparisons can yield the desired results.
hey Noah_Fire, that’s a neat idea! i’ve messed around with conditional formatting before, but not with edit times. you might need to use Apps Script for this. try looking into the onEdit() trigger and getRange().setBackground() method. good luck with your project, let us know if u figure it out!