How to put timestamp in forms?

Hello,

is there a way in forms to have a timestamp like =NOW() update whenever the page is refreshed? It seems to be stuck at the time when I entered the =NOW() formula in the cell, even after refreshing the page. Also, functions like refresh do not work in forms, only tables.

Thank you

2 Likes

Hey @Stuart_John_Chuan :wave:t4: thanks for reaching out.

Our =NOW(), =TODAY(), =RAND() and similar functions are non-volatile in nature and so it wouldn’t automatically change unless there’s some form of trigger that drives the change (ex: buttons, checkboxes etc.). Please feel free to share more details and add it as a feature request ask here — https://feedback.rows.com/

Are you trying to log the timestamp when someone submits the form?
If so, I’d recommend an alternative approach instead —

And, you are correct. Forms cannot contain automation functions like REFRESH, REPEAT or SCHEDULE

Screenshot 2022-08-22 at 13.08.52

Hi @sourabh,

thanks for the tip. I’ve built a save/submit button already for form submissions. There are 3 actions but they follow the same pattern like this:

=BUTTON(“SAVE NOW”, UPDATE(RANGE2JSON(B3:B31,C3:G31),‘[“Email”,“DESCRIPTION”]’,‘Log activity (MH)’!A1:AD1)

Sorry if this is a basic question, how to I include the timestamp with NOW() is the button with a RANGE2JSON formula?

Thank you

1 Like

It’s a bit complex of an approach but here you go :sweat_smile:

Let me know in case you’d want to catch-up over a quick call where we could screen share and go over it together as well!

Best,
Sourabh

Thank you Sourabh.

I’ll give it a go and will come back to you if I can’t work it out myself.

Many thanks!

1 Like

Hi @sourabh ,

That worked a treat!

So I edited the function from

=BUTTON(“SAVE NOW”, UPDATE(RANGE2JSON(B3:B31,C3:G31),‘[“Email”,“DESCRIPTION”]’,‘Log activity (MH)’!A1:AC1)

To
=BUTTON(“SAVE NOW”,UPDATE(UNNEST(PAIR2JSON(“”,RANGE2JSON(B3:B31,C3:G31),“Time”,NOW())),‘[“Email”,“DESCRIPTION”]’,‘Log activity (MH)’!A1:AD1)

And it added an extra column at the end of my data table with the timestamp.

Thank you so much!

Stuart

1 Like

Hello @sourabh ,

I want to take this one step further and send the table data straight to a google sheet.

So within the submit button I have this formula as one of the actions:
=ADD_CELLS_GOOGLE_SHEETS(F89,“NDactivityV2!A1:AL3000”,“{url}”)

and cell reference F89 reads:

=UNNEST(PAIR2JSON(“”,RANGE2JSON(B3:B50,C3:G50),“Time”,NOW()))

However, every time the button is actioned, the header is appended each time in the google sheet table. How do I append the JSON data to the Google Sheet without the header?

Thank you,

Stuart

1 Like

Hey @Stuart_John_Chuan :wave:t4: glad to see you’re progressing well. Here’s what I’d do —

I’m using the following formula —
=PARSE(FILTER(C1,FILTER_COLUMNS(C1,"Time")<>""),"[1:]")

The logic here is that I’m converting the object (which has a key<>value pair) into an dimensional array, thanks to our FILTER & FILTER_COLUMNS function.

Once that’s done, our PARSE is handling the part that ignores the first row from the 2d array (that holds all the headers), leaving us with just the data.

Sorry for the complexity of my solution but this is the best that I could think of :sweat_smile:

Thanks @sourabh . That was a quick response!

I’ll give it a go and report back.

Thank you, Stuart

1 Like

Hi @sourabh,

So I got it to work but because the timestamp - NOW() function - was not sitting in the formula in the submit button it was not showing the correct timestamp.

So I did the following, putting the timestamp within the formula action in the button:

=ADD_CELLS_GOOGLE_SHEETS(PARSE(FILTER(UNNEST(PAIR2JSON(“”,E88,“Time”,NOW())),FILTER_COLUMNS(UNNEST(PAIR2JSON(“”,E88,“Time”,NOW())),“Email”)<>“”),“[1:]”),{name of gsheet tab},{url})

Where cell E88 is RANGE2JSON(B3:B50,C3:G50)

This means in google sheets I now have the timestamp added and the added cells do not contain the headers.

It works but is very complicated to handover and explain to colleagues. Is there a simpler way to do this?

Much appreciate the help you have given so far! No rush to reply to this as it is working.

Many thanks, Stuart

1 Like

Glad to hear things are working as needed now, @Stuart_John_Chuan :muscle:t4:

I hear you about the complexity and wish there was simpler approach to this scenario (I bet there could be one but I might’ve just not thought about it yet :sweat_smile:).

As for sharing and explaining the workflow, sometimes, I break my formula into multiple, individual functions like so —

And the TO_TEXT column you see would actually show the function with its input when being run.

I doubt that :point_up:t4: helped much and so another recommendation would be to do what we do with our templates —

Add an instructions page, if that helps.

Hi @sourabh ,

Thanks for the tip. The only other solution I found was to filter on the google sheets side by applying the UNIQUE function to the data range in a separate tab.

Thanks again for all of your help.

Cheers,

Stuart

1 Like