Send notification only once

Nic0

Hello Community,

I have a problem with notifications and so far no workaround.

I have a sheet that pulls all the information from other sheets so no manual entries are done in that list. If there is a new date in one column (project end date) the sheet should send a notification.

Since the notification automation works with cross reference formulas (in contrast to the cell editing automation) this works technically but the information in the source sheet is populated by a data shuttle which means on every run (every night) all cells in the source sheet are deleted and newly created (I don't have a unique identifier in the source excel sheet so "merge" is not an option).

This leads to redundant notifications even though this end date was already in the list before. It's just "re-entered" every night and triggers the automation.

I already inserted the condition that the automation only fires when the date did not change to "empty" (which happens before the DataShuttle updates the source sheet) but I didn't find a way so far to flag rows that were already reported as newly finished.

I played with "record date" and "check checkbox" automations but since data is coming from another sheet the cell edit automations won't work.

The "project end dates" are not necessarily "today" when they are entered so a "if date is not more than 1 day in the past" will probably miss some of the new entries.

Long story short:

Any ideas on how to send the notification only once and mark lines that have already been reported?

SmartWay360

Hi, @Nic0

Have you tried to put the Condition to the Workflow where it will be triggered only if the "change date" is empty?

Best,

Beata

Nic0

Hi @SmartWay360

thank you for the rapid answer.

If I would change the condition to "change date" is empty I think this would lead to a notification every night for all entries in the list since DataShuttle deletes the whole source sheet (So all dates are empty now for ~1 minute --> notification for all entries will be sent) and then re-enters all the dates that were in the list before.

But please let me know if I missed anything or misunderstood your reply.

Best wishes

Nico

SmartWay360

Hi, @Nic0

You could combine the empty field with automatic field Modified Date. If field is empty, but was modified in the past you don't need to sent out the notification.

Best,

Beata

Nic0

Hi @SmartWay360,

Sending a notification only if it the cell did not change in the past would be definitely the solution for my problem!

Thanks for this great idea.

Nevertheless I'm not sure how to achieve this since I did not find this functionality in the automation settings.

I only see if date is older/younger/empty/in the past etc. but not something like "if field was modified in the past"

The cell activity log for "old cells" is [blank --> date --> blank --> date --> blank --> date] (every night another blank/date sequence)

The cell activity log for "new cells" looks like [date]

If I'll find a way to trigger notifications only if a date appears in the cell for the first time this would be the solution I'm looking for.

Is there a way (by automation or helper column) to pull the date when the cell was modified the first time?

Best wishes and thank you for your ideas :-)

Nico

SmartWay360

Hi @Nic0

You can use the Automatic field Modified date that capture the date when a row was last changed>

Then you can set up the workflow using the combination of these 2 values>

Best,

Beata

Nic0

Hi @SmartWay360

the problem in using the DataShuttle is that every row is updated once it ran. There is no option for modification date of one cell only.

The modification date/time is the same for every row (every night at 3 AM) so I can't use this one unfortunately. The full activity log is only accessible over API which is no option here.

I think I also explained my use case not properly. I need to have a notification sent once the study is finished (eg. there is an end date for that project)

This means Smarthsheet has to send an email if there is a date in the column "project end date"

In an ideal world I could use an automation that sends an email once a date appears (grabbed by cross reference formula) and after the email is sent, it checks a checkbox in that sheet to avoid sending another mail the next night when the date is cleared and re-entered by the data-shuttle.

The only workaround so far is to hope that the newly entered "finished date" is never older than 14 days and the automation runs once a week for all rows that have a "project end date" within the last 14 days.

Not the most pretty way and still slightly error prone but better than nothing.

If there is any creative idea, I'm super happy to try something different :-)

Best regards and thanks for all of you ideas

Nico

SmartWay360

Hi, @Nic0

What about copying the data in your sheet to a new sheet, just before the new upload is done? This then you could use to identify the changes.

Best,

Beata