Search code examples
arraysdateif-statementsplitgoogle-sheets-formula

Generate formula for compare dates and if condition is met, put custom text

I'm working with Google Sheets and Google App Scripts1 for retrieve search results from YouTube - by using YouTube Data API - and write those search results in batch on a sheet.

What I'm trying to accomplish is:

Compare the publish date of the video (values in "C" column) and compare it with the current date (i.e. today's date).

If the video was published today, then, write (on the cells in "E" column) the word: NEW!, otherwise, leave it blank.

I'm looking for a formula that can be set on the cell - rather than just modifying the script and clear the whole column (or a range) everytime the script starts running and rewriting the word if the condition is met1.

I tried modifying these formulas shared on this answer and this answer, but, I haven't figured out how to deal with dates on Google Sheets.

Sample:

Video_ID Title Date Uploaded Video link ¿Is New Video? Desired output
rNp9qmASSlw Privileged Victim Archetype? ~ Meghan Markle "Archetypes" Analysis 2022-08-25T04:37:48Z https://www.youtube.com/watch?v=rNp9qmASSlw NEW!
MkBQXzmyh1A Did Gary Busey Add 'Horror' to Monster Mania Convention? ~ Gary Busey Case Analysis 2022-08-24T04:38:19Z https://www.youtube.com/watch?v=MkBQXzmyh1A
q2QNjccf6hw 10 TRUE Disturbing & Terrifying Followed Home Horror Stories ~ Scary Stories To Fall Asleep To 2022-08-25T00:15:01Z https://www.youtube.com/watch?v=q2QNjccf6hw NEW!
-Imgs5T2A8w 50 TRUE Disturbing & Terrifying Scary Stories In The Dark ~ Horror Stories To Fall Asleep To 2022-08-22T03:19:25Z https://www.youtube.com/watch?v=-Imgs5T2A8w
2hw4EPZLO_A evento 12 de agosto pelea 11 2022-08-25T12:13:23Z https://www.youtube.com/watch?v=2hw4EPZLO_A NEW!
LY6unkFL3R4 evento 12 agosto pelea 10 2022-08-23T00:42:16Z https://www.youtube.com/watch?v=LY6unkFL3R4
QHW9wddNJQE WHEN A SMART NUMERON PLAYERS MEETS A STUPID NUMERON PLAYER 2022-08-25T00:16:52Z https://www.youtube.com/watch?v=QHW9wddNJQE NEW!
ol6Yt0TjB0E WHEN YOUR OPPONENT IS THE MOST DUMB PLAYER EVER IN MASTER DUEL 2022-08-24T00:00:11Z https://www.youtube.com/watch?v=ol6Yt0TjB0E
BisJI5BGloY WHEN YOU DESTROY A THICC WAIFU BOARD WITH ONE SMALL MONSTER 2022-08-20T00:05:00Z https://www.youtube.com/watch?v=BisJI5BGloY

This is the link of the sample spreadsheet.

1 I'm using GAS for retrieve the data and I want to avoid calling for write/clear cells/ranges from the script due to GAS limitations - (if the script takes five minutes or more, a timeout exception is raised and the script is interrumpted).

Solution

  • try:

    =ARRAYFORMULA(IFERROR(IF(TODAY()=INDEX(SPLIT(C2:C, "T"),, 1), "NEW!", )))

    enter image description here

    update:

    delete everything in E column and use this formula in E1 cell:

    ={"¿Is New Video?"; ARRAYFORMULA(IFERROR(IF(TODAY()=
 INDEX(SPLIT(C2:C, "T"),, 1), "NEW!", "OLD VIDEO")))}