Filter Exisitng Entry Every 'X' number of Hours from HTTP Request

Questions
, ,
1

Hey guys,

So I have a client I’m helping send out Messages to Twilio

Basically what I’m doing is I have a custom form that’s 24/7 collecting information on Google Sheets
(Names & Phone Number) then using Google Sheets Trigger (Custom Node from : n8n-nodes-google-sheets-trigger - npm) once a new row is detected a message is sent to that new Number.

Essentially the messages being sent are “Hi [[username]], thanks for filling out our Form. We’ll get back to you shortly”

So everything’s working well to that end, the thing is some of the customers are in a situation where they have to fill in 2 or more Questionnairs thereby getting 2 or more “Thank you Messages” (I’m trying to avoid spamming)

I’d like to know if there’s a way I can filter out (using ‘IF Node’ maybe? 'Date/Time Node maybe?) numbers that are already existing not to be sent the message

i.e if number has been sent a Thank you message in the past 24hours, don’t send message.

The Nodes are
GoogleSheets Trigger > Set Node > Twilio

2

You could look at using getWorkflowStaticData.

You could save the user/number and check if they already exist. You probably want to store them with a timestamp and clean out the list for any entries over 24hrs

3

@pemontto thanks for taking the time to give this a look

The entries each have unique timestamps on Google Sheet as well (i.e the time the entry was made & filled on Google Sheets)

Do I then use the timestamp (Ps: do they have to be in ISO-8601 format e.g 2020-07-10 15:00:00.000 ?) & pass it on the expression : getWorkflowStaticData ?

Kindly, if you can, do provide with an example workflow…

Thanks.

4

Can you post a redacted JSON sample of the data from the trigger in your workflow and I’ll have a crack?

BTW managing the date is just so your static data doesn’t balloon out unbounded. We need to know which entries we can clean up safely!

5

Here’s some randomly generated data just to get the gist of the format of data that’s coming from Google Sheets.

I’m assuming the getWorkflowStaticData Node will come between the Set & Twilio Node?

Ps : if Phone Number has been sent a Thank you message in the past 24hours, don’t send message.

Contains;

  1. Name
  2. Number
  3. Timestamp
  4. Message
6

Here’s something that might get you started

  • Cleanup clears entries older than 24hrs
  • Throttle will set the item.send field to false if an SMS has already been sent
1 Like
8

HI @pemontto

It works! Had to adjust the hours to minutes in “CleanUp cache” by setting 0.0833333 ( minutes) while testing & I saw it does the trick.
Thank you very much!

9

…another quick one If it’s not too much to ask :slight_smile:

I’m trying to find a way of sending different messages to different Customers filling out the Questionnaires at different times e.g

If Customer timestamp is between 12PM & 3PM send “Hi [[username]], thanks for filling out our Form. We’ll get back to you shortly - Enjoy your afternoon!”

If Customer timestamp is between 6PM & 9PM send “Hi [[username]], thanks for filling out our Form. We’ll get back to you shortly - Enjoy your evening!”

Possible using the Javascript Magic you’ve used on the “CleanUp Cache” Node on the previous example?

The time is in this format : 2023-04-30 15:40

10

Hey @KevinK,
it’s probably not the most elegant solution, but I’ve solved it by extracting the hour from the date with the “Date” node and used Expressions to set the closing message depending on the hour.

Here with your data, (12pm to 3PM and 6PM to 9PM and holes inbetween)

Also, please create a new forum post in the future for a new question, with that questions can get answered faster and the forum is better searchable :blush:

11

Hi @FelixL
Thanks for checking this out…

Regarding starting a new forum topic - I thought of it but then decided to continue on this one since I figured the 2 sort of fall in the same category & would be ideal for anyone who might think of running a similar project maybe? :slight_smile:

…
So on the example Nodes you’ve provided, how would I split the values from the Set Node to go to two different Twilio Nodes?
Ps: Would it assist if I had a ‘Time-Only’ record that’s in 24hour mode e.g 15:00 for 3pm

1 Like
12

Your example date before already was in 24hour mode, I’ve just used the Date node to easily extract the hour from the date. I think the Date node also works with 12hour mode (pm/am notation) but i’ve not tried that yet. just tried it and it works perfectly fine for a date like 2023-04-30 07:00 PM it returns 19 as hour.

If you want different paths for your different closing messages you can use the filter (or if node).
This method probably is also a little better readable ^^

2 Likes
13

Thank you - that filter Node seems to do the trick!

Appreciate your time.

1 Like
14

Hi @pemontto

Seems the Nodes you provided aren’t working on a Live environment.
The aim was to filter messages & only pas through if it hasn’t been sent in the past 24 hours however clients are still getting duplicate messages in the same 2H hours if they make more than one entry

When the execution is run manually it works but on LIVE it doesn’t. What could be the issue? SOmewhere in the

‘Throttle’ & ‘Clear Cache’ Nodes maybe?

I’ve tested with placing 1 hour wait time & 0.83333 (5 Minutes) & neither work