Warn before SSL certificates expire

Every morning at 08:00, the domains listed in your spreadsheet are read, the certificate on each one is looked up through a certificate-checking service you point it at, and the days left before it expires are worked out. Anything inside your warning window is collected into a single email, so a renewal is booked while there is still time rather than after the site has gone down.

You get one email listing each domain that is expiring soon with its expiry date and the days remaining, and any certificate already expired is called out as such. A domain that cannot be reached, or whose answer carries no expiry date, is listed separately in the same email and never stops the rest being checked. A row with no domain is skipped. When every certificate is comfortably valid, nothing is sent.

Before the first run you need a spreadsheet tab listing your domains under a column headed 'Domain', a certificate-checking service that answers for one domain at a time, and an inbox for the warnings.

Run every day at 08:00.Run every day at 08:00.

Set domainSheetUrl to blank.Set domainSheetUrl to blank.

Set domainSheetTab to blank.Set domainSheetTab to blank.

Set certificateApiUrl to blank.Set certificateApiUrl to blank.

Set alertEmail to blank.Set alertEmail to blank.

Set warnWithinDays to 30.Set warnWithinDays to 30.

Google SheetsRead the rows of domainSheetUrl. Save it as domainSheet.Read the rows of domainSheetUrl. Save it as domainSheet.

Set sites to domainSheet.rows.Set sites to domainSheet.rows.

Set expiringSoon to an empty list.Set expiringSoon to an empty list.

Set couldNotCheck to an empty list.Set couldNotCheck to an empty list.

For each site in sites:For each site in sites:

Set domain to blank.Set domain to blank.

If a step below fails, save the error as missingDomainIf a step below fails, save the error as missingDomain

Try

Trim site.Domain and save it as domain.Trim site.Domain and save it as domain.

On error

Change domain to blank.Change domain to blank.

If domain is emptyIf domain is empty

Yes

Skip to the next item.Skip to the next item.

No

No steps

Set daysLeft to 0.Set daysLeft to 0.

Set expiresOn to blank.Set expiresOn to blank.

Set wasChecked to false.Set wasChecked to false.

If a step below fails, save the error as checkFailedIf a step below fails, save the error as checkFailed

Try

Fetch certificateApiUrl plus domain and save it as certificate.Fetch certificateApiUrl plus domain and save it as certificate.

Trim certificate.body.result.valid_till and save it as expiryDate.Trim certificate.body.result.valid_till and save it as expiryDate.

Count the days from today's date to expiryDate and save it as daysLeft.Count the days from today's date to expiryDate and save it as daysLeft.

Change expiresOn to expiryDate.Change expiresOn to expiryDate.

Change wasChecked to true.Change wasChecked to true.

On error

Add domain - checkFailed.message with certificateApiUrl replaced by the endpoint address to the end of couldNotCheck and save it as couldNotCheck.Add domain - checkFailed.message with certificateApiUrl replaced by the endpoint address to the end of couldNotCheck and save it as couldNotCheck.

If wasChecked is yes and daysLeft is less than or equal to warnWithinDaysIf wasChecked is yes and daysLeft is less than or equal to warnWithinDays

Yes

If daysLeft is less than 0If daysLeft is less than 0

Yes

Add domain - EXPIRED on expiresOn to the end of expiringSoon and save it as expiringSoon.Add domain - EXPIRED on expiresOn to the end of expiringSoon and save it as expiringSoon.

No

Add domain - expires on expiresOn (daysLeft days left) to the end of expiringSoon and save it as expiringSoon.Add domain - expires on expiresOn (daysLeft days left) to the end of expiringSoon and save it as expiringSoon.

No

No steps

Set report to blank.Set report to blank.

If expiringSoon isn't emptyIf expiringSoon isn't empty

Yes

Change report to Certificates expiring within warnWithinDays days:Change report to Certificates expiring within warnWithinDays days: expiringSoon joined by a new line .

No

No steps

If couldNotCheck isn't emptyIf couldNotCheck isn't empty

Yes

Change report to reportChange report to report These sites could not be checked: couldNotCheck joined by a new line .

No

No steps

If report is emptyIf report is empty

Yes

Finish the run: Every certificate checked is valid for more than warnWithinDays more days.Finish the run: Every certificate checked is valid for more than warnWithinDays more days.

No

No steps

GmailSend an email to alertEmail.Send an email to alertEmail. Subject: SSL certificate check: the length of expiringSoon expiring within warnWithinDays daysreport