Automate Google Sheets with a small script
Highlight overdue rows by itself. The AI writes the script. You run it once.
The idea
Google Sheets has a free built-in tool called Apps Script. It runs small programs on your sheet. You do not install anything. Open your sheet and choose Extensions, then Apps Script.
Describe your sheet exactly: the column letters, the header names and what each column holds. Then say what should happen. The AI writes the script.
A script can change your data. Make a copy of your sheet first. Run the script on the copy. The first run asks you to give permission. Read what it asks, and only allow it for your own sheet.
Your task
Colour overdue tasks pink
Make a copy of a sheet with the headers Task, Owner, Due date and Status in columns A to D. Paste the prompt.
Write a Google Apps Script function named highlightOverdue for my active sheet. My sheet: row 1 is the header. Column A = Task, B = Owner, C = Due date (a real date), D = Status (values: Open, Done). Behaviour: for each row from row 2, if the due date is before today and the status is not "Done", set the background of columns A to D to pink. Otherwise clear the background. Rules: Compare dates without the time part. Do not delete or edit any values. Add a short comment before each step. Give me only the code, and 3 steps to run it.
What you should get
A real example of a good result. Your wording will differ a little. The shape and the facts should match.
Printed resultExample
function highlightOverdue() {
// Get the active sheet and all its data
const sheet = SpreadsheetApp.getActiveSheet();
const rows = sheet.getDataRange().getValues();
// Today's date without the time part
const today = new Date();
today.setHours(0, 0, 0, 0);
// Go through each row after the header
for (let i = 1; i < rows.length; i++) {
const due = rows[i][2];
const status = rows[i][3];
const overdue = due instanceof Date && due < today && status !== 'Done';
// Colour columns A to D, or clear the colour
sheet.getRange(i + 1, 1, 1, 4).setBackground(overdue ? 'pink' : null);
}
}
To run it:
1. In your sheet choose Extensions > Apps Script and paste the code.
2. Click Run and allow the permission for your own sheet.
3. Go back to the sheet. Overdue rows are pink.
Check yourself
Finished?
Stamp this ticket. Your pass on the home page gets punched.