Blog / Construction Operations

Warranty Tracking After Closeout with Expiry Reminders

By Keyona Meeks, Founder, ReRev Labs · Oct 31, 2026 · 10 min read

The short answer: A warranty is only useful if someone knows when it ends. Keep one row per warranty in a Google Sheet: the item, the contractor, the start date and the length in months. A formula works out the expiry date and the days left. A short Apps Script emails you the ones that expire within a window you choose, so you can raise a problem before the date passes. Your contract decides when a warranty starts and what it covers. This is a tracking aid. I tested the script with a mock in Node and the formulas in LibreOffice. Nothing was run in Google.

By the end of this post, you'll be able to:

• Set up a warranty tracker with expiry dates.
• Get a daily email of warranties that expire soon.
• Tell a warranty from a correction period.
• Know what this tracker cannot do.

I sell build work, so weigh this post with that in mind. This is a record-keeping aid, not legal advice. Check with an attorney, insurer or safety professional before you rely on any log for any contract or legal question.

Is a one-year correction period a warranty?

Not always, and the difference matters. AIA Contract Documents published an article titled "Remember, It is a One-Year Correction Period, Not a One-Year Warranty" (dated March 31, 2023 on the page). It says: "Importantly, the one-year correction period should not be referred to as a one-year warranty." It also says: "Contractors often have legal warranty obligations that extend long after the project's completion." It advises that if you find a condition that may fall under the contractor's warranty after the correction period ends, you should consult an attorney. I read the page through a tool that summarizes, so check the quotes on the page itself.

So track the correction period and each warranty as separate rows, and use the dates your contract gives. I do not supply any default length.

How do you set up the tracker?

Row 1 holds headings. Use a tab named Warranties.

Put these in row 2 and fill them down. Put the number of warning days in cell J1, for example 60. That number is your decision. The script below has its own WARN_DAYS setting. Keep the two numbers the same.

E2: =IF(C2="","",EDATE(C2,D2))
F2: =IF(E2="","",E2-TODAY())
G2: =IF(A2="","",IF(E2="","No start date",IF(F2<0,"Expired",IF(F2<=$J$1,"Expiring soon","Active"))))

I tested these in LibreOffice with five made-up rows: a 10-year roof, a 12-month item expiring in about 25 days, an expired item, a row with no start date, and an item about 65 days out. They returned Active, Expiring soon, Expired, No start date and Active. LibreOffice showed Days left in a date format, so set that column to a plain number format. I could not confirm the menu steps in Google Sheets. I did not run the formulas in Google Sheets, so test with your own rows.

Who decides when a warranty starts?

Your contract does. It might be the date of substantial completion, the date of acceptance or another date. I do not know which applies to you and I do not supply a default. Enter the start date your documents give, and ask your attorney if it is unclear.

What does the script look like?

Paste this into the Apps Script project attached to the sheet. It reads the tab and changes nothing. Nothing in it runs until you call it.

// Emails a list of warranties that expire soon. It reads the "Warranties" tab and changes nothing.
var RECIPIENT = '';       // Paste the address that should get the list. Your decision.
var SEND_HOUR = 7;        // Hour of day, in the script time zone.
var TAB = 'Warranties';
var WARN_DAYS = 60;       // List warranties that expire within this many days. Your decision.
var MAX_LINES = 40;       // Most lines in the email.
var HEADERS = {           // Change the text on the right to match YOUR column headings.
  item: 'Item', who: 'Contractor', expires: 'Expires'
};

function dayKey(value, tz) {
  if (value === '' || value === null || value === undefined) return '';
  if (Object.prototype.toString.call(value) === '[object Date]') return Utilities.formatDate(value, tz, 'yyyy-MM-dd');
  return String(value).slice(0, 10);
}

function dayNumber(key) {
  var p = key.split('-');
  return Math.round(Date.UTC(Number(p[0]), Number(p[1]) - 1, Number(p[2])) / 86400000);
}

function findColumns(headerRow) {
  var idx = {};
  Object.keys(HEADERS).forEach(function (k) {
    var at = headerRow.map(function (h) { return String(h).trim(); }).indexOf(HEADERS[k]);
    if (at === -1) throw new Error('Missing column in ' + TAB + ': ' + HEADERS[k]);
    idx[k] = at;
  });
  return idx;
}

function findExpiring(rows, idx, todayKey, tz) {
  var soon = [];
  var noDate = 0;
  var today = dayNumber(todayKey);
  rows.forEach(function (r) {
    if (String(r[idx.item]).trim() === '') return;
    var key = dayKey(r[idx.expires], tz);
    if (key === '' || isNaN(dayNumber(key))) { noDate++; return; }
    var left = dayNumber(key) - today;
    if (left >= 0 && left <= WARN_DAYS) soon.push({ item: r[idx.item], who: r[idx.who], expires: key, left: left });
  });
  soon.sort(function (a, b) { return a.left - b.left; });
  return { soon: soon, noDate: noDate };
}

function buildEmail(result, todayKey) {
  var lines = ['Warranties that expire within ' + WARN_DAYS + ' days, as of ' + todayKey, ''];
  if (result.soon.length === 0) {
    lines.push('None.');
  } else {
    result.soon.slice(0, MAX_LINES).forEach(function (o) {
      lines.push(o.item + '  (' + o.who + ')  expires ' + o.expires + ', ' + o.left + ' days left');
    });
    if (result.soon.length > MAX_LINES) lines.push('Plus ' + (result.soon.length - MAX_LINES) + ' more.');
  }
  lines.push('', 'Rows with no usable expiry date: ' + result.noDate);
  return lines.join('\n');
}

function sendExpiryList() {
  if (!RECIPIENT) throw new Error('Set RECIPIENT first.');
  if (MailApp.getRemainingDailyQuota() < 1) throw new Error('No email quota left today.');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(TAB);
  if (!sheet) throw new Error('Missing tab: ' + TAB);
  var values = sheet.getDataRange().getValues();
  if (values.length === 0) throw new Error('The ' + TAB + ' tab is empty.');
  var idx = findColumns(values[0]);
  var tz = Session.getScriptTimeZone();
  var todayKey = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  var result = findExpiring(values.slice(1), idx, todayKey, tz);
  MailApp.sendEmail(RECIPIENT, 'Warranties expiring soon: ' + result.soon.length, buildEmail(result, todayKey));
  return result;
}

function installTrigger() {
  ScriptApp.newTrigger('sendExpiryList')
    .timeBased()
    .everyDays(1)
    .atHour(SEND_HOUR)
    .inTimezone(Session.getScriptTimeZone())
    .create();
}

What the parts do, with Google's own descriptions as I read them in the method tables this session:

• getActiveSpreadsheet(): "Returns the currently active spreadsheet, or null if there is none." getSheetByName(name): "Returns a sheet with the given name." getDataRange(): "Returns a Range corresponding to the dimensions in which data is present." getValues(): "Returns the rectangular grid of values for this range."
• Utilities.formatDate "Formats date according to specification described in Java SE SimpleDateFormat class." Session.getScriptTimeZone() "Gets the time zone of the script."
• MailApp.sendEmail(recipient, subject, body) "Sends an email message." getRemainingDailyQuota() "Returns the number of recipients you can send emails to for the rest of the day."
• everyDays(n) "Specifies to run the trigger every n days." atHour(hour) "Specifies the hour the trigger at which the trigger runs." inTimezone(timezone) "Specifies the timezone for the specified dates/time when the trigger runs."

The script reads the Expires column. Because that column holds a formula, the script sees the value the formula produced. It finds columns by the headings in the HEADERS block. It lists warranties that expire from today up to WARN_DAYS days out, soonest first, and counts rows with no usable expiry date. It sends a short "None" email when nothing is near, so you know it ran. It does not list warranties that have already expired.

Who should get the email?

You decide. RECIPIENT is a paste value, and the script will not run until you set it. Ask the people involved before you add them.

How do you put it on a daily schedule?

Run installTrigger once and check your project's triggers list for exactly one entry. I could not confirm which minute of the hour a trigger fires. Per an earlier review of Google's triggers page, installable triggers "always run under the account of the person who created them." Per an earlier review of Google's authorization page, scopes are "determined automatically by scanning the script code." Read the permission prompt before you approve it.

How do you test it?

1. Enter five test lines: one expiring in 10 days, one in 100 days, one expired, one with no start date and one expiring today.
2. Check the Expires, Days left and Status columns by hand.
3. Set RECIPIENT to your own address and run sendExpiryList. Only the 10-day and today lines should appear.
4. Change a heading by one letter. You should see the missing-heading message. Fix it.
5. Run installTrigger and check the triggers list.
6. Delete the test lines.

What did the test show?

I tested the script in Node with a mock of the Google services. All 15 checks passed. They covered the window edges at 60 and 61 days, an expiring-today line, expired lines left out, date cells and text dates, rows with no date, soonest first, a "None" email, a missing heading, an empty tab, a blank recipient, a used-up quota, a missing tab, the 40-line cap, headings with stray spaces and the trigger chain. The mock cannot show how Google returns your dates, so steps 1 to 3 matter.

Where does an LLM fit in this?

An LLM can help with the messy input. For example, it can read a warranty letter or a long closeout document and suggest the item, the contractor, the start date and the length. A person must check each value against the document, because an LLM can read a date wrong and sound sure. The expiry math and the email are plain rules. My stance: AI writes the code, plain automation runs it, and an LLM shows up only where the input is messy and a person checks the result.

Where this has limits

• It is a tracking aid, not a legal record. I give no legal advice. Check with an attorney, insurer or safety professional before you rely on any log for any contract or legal question.
• It does not know what a warranty covers. It only tracks the end date you typed.
• I give no default start date or length. Your contract decides.
• Nothing was run in Google. The script test was a mock in Node. The formula test was in LibreOffice.
• The AIA article was read through a summarizing tool. Check the quotes on the page.
• Email quotas apply. I quote the quota call above. Check Google's limits for your account type.
• No ReRev prices and no promises about results.

If you want help turning this into a closeout and warranty system for your team, see our AI operations service or tell us how you track warranties now. Do not send contracts, passwords or login details.

Frequently asked questions

How do I track construction warranties in a spreadsheet?

One row per warranty, with a start date and a length in months. Formulas give the expiry date and days left.

When does a warranty start?

Your contract says. I do not know yours.

Is the one-year correction period the same as a warranty?

AIA Contract Documents says it should not be called a one-year warranty. Track them separately and ask an attorney about your contract.

Does the script send reminders?

Yes, a daily email of warranties that expire within the window you set, after you install the trigger. Test it first.

Does it list expired warranties?

No. It lists those expiring from today to your window.

Is this a legal record?

No. Ask an attorney what counts as a record under your contract.

Read next: The closeout documents tracker is where the warranty documents this post tracks get collected.