Vendor Contract Renewal Alert: A Weekly Zapier and Google Sheets Email Before Notice Deadlines Pass

What This Builds

Every week, an email tells you which vendor contracts are close to their notice deadline, which have already passed it, and which rows in your vendor list are missing the dates needed to calculate one. You keep a simple Vendors tab. The sheet works out each notice deadline and stage. A Zap reads one summary row and emails you.

The date that matters is not the renewal date. It is the last day you can tell the vendor you do not want to renew, and that day is usually earlier. Many contracts renew automatically if the notice window is missed, which is how a training platform or a content library gets renewed by accident. The alert gives you time to negotiate or switch, and it goes to you alone. No vendor and no colleague receives anything from the automation.

Prerequisites

• A Google account with Google Sheets and Gmail. Google Workspace pricing is on Google's own pricing page.
• A Zapier account on a plan that allows multi-step Zaps. Zapier's free plan allows two-step Zaps only, and this build has three steps, so you need Professional or higher ($29.99/month at the entry level). That is the total ongoing cost, because Sheets and Gmail come with your Google account.
• Permission to use Zapier with vendor information. Check your company's automation policy and approved tool list first.
• A list of your vendors with renewal dates and the notice period from each contract, in days
• About an hour

Keep contract detail out of the sheet. Contract terms and pricing are often covered by confidentiality clauses. This build asks for no prices, no discount terms and no clause text. The sheet holds the vendor name, product, renewal date, notice period in days, owner and status. If even the vendor names are sensitive in your company, use a short code such as V01 and keep the key to the codes in a private document. Understand what Zapier receives: the lookup step returns the whole Summary row, including the digest text with vendor names, products and owner initials. Zapier keeps each step's data in Zap History, which anyone with access to your Zapier account can open. Check that Zapier is an approved tool before you connect real data. Use owner initials rather than full names.

The Concept

Picture a calendar that never forgets. The Vendors tab is your list. Formulas turn each row into a deadline and a label such as "Act now". A Summary tab holds one line that always exists, even in a week with nothing urgent, and Zapier fetches that line and emails it to you every week.

The one-line design matters because of how Zapier searches. A Zapier lookup that finds no matching row stops the run, and Zap History shows it as Safely halted. Later steps then either do not run or fail for lack of data. If the Zap looked for "vendors due soon", a quiet week would produce no email, and you could not tell a quiet week from a broken Zap. Because the Summary row always exists, the email always arrives, and a missing email means a problem you can investigate.

Build It Step by Step

Part 1: Lay out the sheet

Create one Google Sheet with two tabs. Use the exact tab names.

Columns A to F on Vendors are yours to type. Columns G to J are formulas. Format column C as a date and column D as a plain number. Status is one of Active, Renewed or Cancelled. The sheet treats everything except Renewed and Cancelled as still open.

Check File, then Settings, then Calculation, and choose "On change and every hour" if the sheet offers it. TODAY() only refreshes when the sheet recalculates, and Zapier reads the values as last calculated.

Part 2: Vendors formulas

Put these in row 2 and copy them down to row 200.

G2, Notice Deadline

=IF(OR($A2="",ISBLANK($C2),ISBLANK($D2)),"",$C2-$D2)

H2, Days Until Notice Deadline

=IF($G2="","",$G2-TODAY())

I2, Stage

=IF($A2="","",IF(OR($F2="Renewed",$F2="Cancelled"),"Closed",IF($G2="","Missing dates",IF($H2<0,"Past deadline",IF($H2<=30,"Act now",IF($H2<=90,"Plan","Later"))))))

J2, Digest Line

=IF($I2="Past deadline",$A2&" | "&$B2&" | "&$E2&" | PAST NOTICE DEADLINE "&TEXT($G2,"yyyy-mm-dd")&" | "&ABS($H2)&" days ago",IF(OR($I2="Act now",$I2="Plan"),$A2&" | "&$B2&" | "&$E2&" | "&$I2&" | notice deadline "&TEXT($G2,"yyyy-mm-dd")&" | in "&$H2&" days",IF($I2="Missing dates",$A2&" | "&$B2&" | "&$E2&" | Missing dates | add a renewal date and notice period","")))

The thresholds of 30 and 90 days are choices you can change. Put the numbers you prefer inside the I2 formula. Dates joined into text use TEXT(), because a date joined directly into text appears as a serial number.

Walk the formulas through five rows. Assume the sheet is calculated on Monday 2026-10-05.

• Past deadline row (V01): The renewal is 2026-11-15 and the notice period is 60 days, so G2 gives 2026-09-16. H2 is 2026-09-16 minus today, which is negative 19. In I2 the status is not Renewed or Cancelled, G is not empty, and H is below 0, so the stage is Past deadline. J2 builds "V01 | LMS | JD | PAST NOTICE DEADLINE 2026-09-16 | 19 days ago". The row stays in the digest every week until you set the status to Renewed or Cancelled.
• Act now row (V02): The deadline is 2026-10-18, which is 13 days away. That is 0 or above and 30 or below, so the stage is Act now. J2 gives "V02 | Content library | AK | Act now | notice deadline 2026-10-18 | in 13 days".
• Plan row (V03): 67 days is above 30 and up to 90, so the stage is Plan and the row appears in the digest with the same format as Act now.
• Closed row (V04): The status is Renewed, so the stage is Closed and J2 is empty. The row never appears in the digest, even though its notice deadline is already in the past. Any row with more than 90 days to go gets the stage Later and is left out of the digest too.
• Missing dates row (V05): The renewal date is blank, so G2 is empty and H2 is empty. In I2 the status is open and G is empty, so the stage is Missing dates. J2 builds a line asking you to add the dates. Without this stage, a vendor with no date would never be flagged.

Rows with a blank Vendor cell return empty in every formula column.

Part 3: Summary formulas

Type the word summary (lowercase) in A2. Then enter these in row 2. There is no row 3. The Summary tab has exactly one data row.

B2, Past Deadline Count

=COUNTIF(Vendors!I2:I200,"Past deadline")

C2, Act Now Count

=COUNTIF(Vendors!I2:I200,"Act now")

D2, Plan Count

=COUNTIF(Vendors!I2:I200,"Plan")

E2, Missing Dates Count

=COUNTIF(Vendors!I2:I200,"Missing dates")

F2, Digest Lines

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Vendors!J2:J200,Vendors!J2:J200<>"")),"None this week")

G2, Report Date

=TEXT(TODAY(),"yyyy-mm-dd")

Both ranges in the FILTER run over rows 2 to 200, so they are the same height. On a week with no urgent vendors, FILTER finds nothing, IFERROR catches the error, and F2 reads "None this week". With the five example rows, B2 is 1, C2 is 1, D2 is 1, E2 is 1, and F2 holds three lines (V01, V02 and V03) plus the Missing dates line for V05, so four lines in all.

The digest lists rows in sheet order, so keep the Vendors tab sorted by renewal date.

Part 4: Build the Zap

Open Zapier and create a new Zap.

1. Trigger: Choose Schedule by Zapier and pick the event Every Week. Pick the Day of the Week, the Time of Day and your timezone. Triggers do not use Zapier tasks.
2. Action: Add Google Sheets and choose the event Lookup Spreadsheet Row. Set Spreadsheet to your vendor sheet, Worksheet to Summary, Lookup column to Key, and Lookup value to summary. Run the test. It returns the Summary row with fields such as Past Deadline Count, Act Now Count, Plan Count, Missing Dates Count, Digest Lines and Report Date. If the fields do not appear, refresh the fields so Zapier re-reads the header row.
3. Action: Add Gmail and choose the event Send Email. Put your own address in To. For the subject use "Vendor renewal digest" followed by the Report Date field. If the step has a Body type option, choose plain text to keep the line breaks. Build the body from the mapped fields:

Past notice deadline: [Past Deadline Count]
Act now (30 days or fewer): [Act Now Count]
Plan (31 to 90 days): [Plan Count]
Missing dates: [Missing Dates Count]

Details (vendor | product | owner | stage | notice deadline | timing):
[Digest Lines]

Change the Status column to Renewed or Cancelled to remove a vendor from this list.

Each square-bracket item is a field you insert from step 2 using the field picker.

1. Turn the Zap on.

Zapier bills only action steps that run successfully. Triggers, and steps that error or halt, do not count as tasks, so each weekly run uses two tasks. The finished Zap has three steps, which is why the free plan does not allow it.

Part 5: Test it

1. Enter the five example rows. Compare each formula result with the walk-through in Part 2.
2. Test the quiet week. Set every row's status to Renewed. The Summary should read 0, 0, 0, 0 and "None this week". Run the Zap. The email should still arrive.
3. Replace the example rows with your real vendors before you turn the Zap on.

Real Example: An Autumn Review

Setup: An L&D manager keeps 14 vendors in the sheet: the LMS, a content library, a survey tool, a video platform and several smaller providers. The Zap runs every Monday at 8.

Input: The sheet holds the five kinds of rows above.

Output: On Monday the manager sees one vendor past the notice deadline, one to act on within two weeks, one to plan for, and one row with no dates. The manager checks the LMS contract that day and asks the vendor what options remain now that the notice date has passed. The manager sets V01 to Renewed or Cancelled once the decision is made, and the row leaves the digest.

Time saved: The manager no longer scans a calendar and a folder of contracts each week. The real work, which is negotiating, comparing alternatives and deciding, stays with the manager.

What to Do When It Breaks

• A Monday passes with no email (the silent failure) → Open Zapier, then Zap History, and read Monday's run. No run at all means the Zap is off or the schedule needs checking. A Safely halted run means the Lookup step found no row where Key equals summary. Check that A2 on the Summary tab still says summary, that the row exists and that the Worksheet is Summary. An error run has a message you can read in the step. Also look in Gmail's spam folder. Because a stopped Zap sends nothing to notice, add a recurring calendar reminder for Monday at 9. If the digest is not in your inbox, open Zap History.
• The stages never change → The sheet is not recalculating. Set the calculation option to every hour, and compare the Report Date in the email with today's date.
• A vendor that should appear is missing → Check its stage. A row with stage Later is more than 90 days away, and a row with a status of Renewed or Cancelled is Closed.
• Notice Deadline shows an error → The renewal date is text or the notice period is not a number. Reformat column C as a date and column D as a number.
• A vendor keeps showing as past deadline after you dealt with it → Set its status to Renewed or Cancelled. The alert never removes a row on its own.
• The Zap turns itself off after errors → Read the error in Zap History, fix it and turn the Zap back on.
• Rows added below row 200 are ignored → Extend every range in the formulas, keeping the two ranges in the FILTER the same height.

Variations

• Simpler version: Drop the Plan stage and email only Past deadline and Act now rows.
• Extended version: Add an AI by Zapier step (the action is called Analyze and Return Data) to write a short summary from the four counts only. Leave vendor names out of its prompt. A Standard-tier step uses one task per run, and higher tiers use more.

What to Do Next

• This week: Enter your real vendors and confirm each notice period against the contract yourself.
• This month: Add a monthly reminder to review the Vendors tab for new vendors.
• Advanced: Use the same pattern for other dated obligations, such as certification renewals or licence expiries.

Advanced guide for Learning & Development Manager professionals. These techniques use more sophisticated AI features that may require paid subscriptions.