I would like to ask for your help on Google Spreadsheet & Date Management!
I have a Google Spreadsheet in which I have insurance details of clients. My goal is to have each customer receive an automatic customized reminder email before the insurance anniversary expires.
The Scenario in my mind seems simple: 1) Google Sheets module Search Rows → set up filters with datetime operators that filter only the people relevant → 2) Update a row module to let the relevant people get tagged in a new column → 3) Send an Email module that sends a personalized email based on the data in the spreadsheet to everyone who has been tagged.
THE PROBLEM: In my test spreadsheet, the dates are correct and in a consistent format (YYYY.MM.DD.). Apparently, the datetime operator settings are also correct (earlier than / later than) like this:
{{parseDate(addDays(now; 50); “YYYY.MM.DD.”)}}
{{parseDate(addDays(now; 42); “YYYY.MM.DD.”)}}
Yet, when I run the module, the Otuput Bundle is empty, returning nothing, even though 3 of the 5 examples in the test Sheet should fall within the range that is 43-49 days in the future relative to today’s date.
What am I doing wrong? Perhaps the “now” value should be formatted somehow so that the minutes/seconds not to be confusing for Google Sheet? Is it even possible to have a nested formatDate & parseDate function within a function? The parsing is supposed to be needed so that Make.com can interpret the string as a date. However, I don’t see much point in doing mathematical operations with the parsed string, since the datetime operators are designed exactly to allow the user to configure the dates. Unfortunately for some reason, it doesn’t work…
I’ve read dozens of case studies in the Community, but I can’t find an answer. Please give me some tips!
Hi @Janos
I see that you’r trying to perform parseDate() function on date that is already in date format
I believe you should use the formatDate()
Instead of parseDate()
the parseDate function only helps Make.com to understand the date structure I think. In the meantime, I made a discovery, but it only made me more perplexed
If I click on Show advanced settings then manually delete the “Value render option” and the “Date and time render option” and AFTER I run the scenario, it works!! But just one time. If I run it again, it automatically resets itself to “formatted value” and “formatted string”, and then returns 0 output bundle. But on the first run it returned 4 bundle, exactly what I needed! (4 out of 5 rows matched the filters).
Does anyone know why this is happening and how to configure it not to reset itself?
Did you ever sort this out? I am actually trying to do something VERY similar with reminders to be sent around a specific date.
It seems to me that the data coming from Google Sheets is actually being read in a string or text format and not as a date format. I can perform text functions and match the dates but I cannot use the date functions. I would like to use the Date function “Earlier than or equal to” but it will not work.
The first option of the filter is the field to filter on, the second is the value you want to match or use for the filter and anything in that field seems only to affect my searching. So I have used formatDate ( now ; DD/MM/YYYY ) to match my date format. But I need to make the data coming from Google a date format not a string which the value field is not going to allow me to do.
Hi crose, yes, it is quite simple although I don’t get the logic.
I just simply clicked on “Show advanced settings”, and changed the “Value render option” and the “Date and time render option” to “Empty”. It should be empty as default, and after you run your first scenario it is automatically changed to Formatted value and Formatted strings, but for some reason when you don’t change it to empty, it does not work, but after the first successful run it works without changing it in the “Show advanced settings” field.
