How to make a spreadsheet formula execute every day

Question

I have the following formula which fetches data from the same day from table UniqueLast:

=IFERROR(QUERY(UniqueLast!$A$3:X;"select * where dateDiff(A, now())=0"))

It runs correctly but sometimes the table where this formula is executed keeps the result from the previuos day which is already old. How can I make a regulation for running the formula in 00:00?

@WhatsThePoint Maybe because the title and the body doesn't match tightly. As I understand the question is about what sql statement should be used as the parameter of the Google Sheets' QUERY function. — Rubén, Jan 09 '18 at 17:18

score 0 · Answer 1 · edited Jan 09 '18 at 13:31

Create a macro function and include the above expression. Use VBExpress and create a script (.exe) to open the spreadsheet and run the macro. Add it in the Windows scheduled tasks.

Function to call in Macro

Function getNextRunTime() As Date
Dim curDate As Date
Dim curTime As Date
Dim chkTime As Date
Dim runTime As Date

curDate = Date
curTime = TimeValue(Now)

runTime = curDate + TimeValue("15:59:00")
chkTime = curDate + curTime

If chkTime > runTime Then 'schedule for next day'

runTime = DateAdd("d", 1, runTime)
End If

getNextRunTime = runTime

End Function

score 0 · Answer 2 · answered Jan 09 '18 at 17:13

The SQL statement parameter of the QUERY function should be a single value of type TEXT. NOW() it not valid Google Query Language function. Google Sheets magnes dates on the background as numbers with a different EPOCH date so we should convert it to a date with the proper format. Try

=QUERY(
UniqueLast!$A$3:X,
"select * where dateDiff(A, date """&TEXT(now(),"yyyy-mm-dd")&""")=0"
)

Removed IFERROR in order to make it easy to check if change proposed works with the OP data. You could add it later once you are sure that it does.

How to make a spreadsheet formula execute every day

2 Answers2