How can I set up an alert if the sheet didn't get any new row added in the past 7 days?

Shikha

I have a sheet that gets daily updates through an automation. I want to set up an alert to flag if the sheet didn't get any update in the past 7 days so I know the automation is broken,

ker9

Hi @Shikha

This is my suggestion using a helper row and 3 helper columns. There may be other/better methods.

Create a helper row at the top (I always color this row orange)

Create 3 helper columns: (the date column is your existing date column)

1) Helper 01 (Add identifier in the Helper Row - "Sheet INFO". The identifier is used in automation.)

2) Helper Auto Today (manually enter today's date so you have a date to work with, but it will be automated below)

3) Helper Notify (add formula below on helper row)

=IF(MAX(Date:Date) < ([Helper Auto Today]@row - 7), "No New Items", "New Items")

Create automation to update Today's date in the Helper Auto Today cell. (Using a formula with the "TODAY ()" function causes trouble, better to automate the date.)

Create another notification when Helper Notify = "No New Items" to notify you that nothing has been received.

I hope this helps!

Shikha

Thanks @ker9. This is great! I will check the alert tomorrow, but it seems your solution will work.

ker9

Hi @Shikha @Shikha

A simpler method might be to use a helper column "Max Date" with this formula: =MAX(Date:Date)

Then setup automation to run when Max Date is NOT in the last 7 days.

Shikha

Thank you @ker9. This method is even simpler and working perfectly. I appreciate your help!