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

Options
Shikha
Shikha ✭✭✭
edited 09/22/23 in Smartsheet Basics

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,

Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    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
    Shikha ✭✭✭
    Options

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

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    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
    Shikha ✭✭✭
    Options

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