Timestamp on switch action

Ask for Help
1

Hey, I have a form that users can submit and then an admin can approve using a Switch component. When the admin toggles the switch it’s automatically recorded in the sheets, and then I hide it using visibility filter.

I would like to record the timestamp of the action - to have another column in the sheets that will add the current time when the switch field is 'TRUE".

I was trying many techniques I found online, but couldn’t get the NOW() to stay fixed.

Does anyone have any tips on how to add a timestamp on a switch?

Thanks

1 Like
2

Hi D_J,

I think this would have to involve the use of Google Scripts.

I will make a demo later for you.

3

Right. This would be a script or zapier/integromat integration.

4

An update to this:

Google Script:

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveCell();
if (r.getColumn() < 2 && ss.getName()==‘Timestamp update’) {
var celladdress =‘B’+ r.getRowIndex()
ss.getRange(celladdress).setValue(new Date()).setNumberFormat(“MM/dd/yyyy hh:mm”);
}
};

Change ‘Timestamp update’ to your sheet name
Chang B in celladdress = ‘B’ to the column in said sheet you want to have the timestamp

The setup looks like this:

image

The way it works:

ezgif-1-13d8c8315c0d

3 Likes
5

Thank you @ThinhDinh will give it a try today

6

In your example they are all the same time, does each one have a different timestamp?

7

Because I clicked it just seconds apart, it should be different when you apply it to the real case.

8

Great. Thank you :pray:t4:

1 Like
9

Yeah try it and give me an update, I’m willing to offer more help if needed (it’s 11pm here in Vietnam so might be a little bit delayed, I will check it when I’m up in the morning).

1 Like
10

Hi,

I tried this and I keep getting the following error.

SyntaxError: Invalid or unexpected token (line 6, file “Code.gs”)

11

Sorry if I indeed missed this but can you try adding a “;” to the end of this line?

12

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveCell();
if (r.getColumn() < 2 && ss.getName()==‘TIME’) {
var celladdress =‘B’+ r.getRowIndex();
ss.getRange(celladdress).setValue(new Date()).setNumberFormat(“MM/dd/yyyy hh:mm”);
}
};

I tried it still I get the same error.

I have replicated the exact sheet you prepared, the only diff is the sheet name. “TIME”

13

Can you share the edit access to a copy of your sheet to ariesarsenal@gmail.com? Thank you.

14

Your sheet name has a blank space after the TIME, remove that space in the Sheet name and you’re good to go.

15

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveCell();
if (r.getColumn() < 2 && ss.getName()==‘TIME’) {
var celladdress =‘B’+ r.getRowIndex();
ss.getRange(celladdress).setValue(new Date()).setNumberFormat(“MM/dd/yyyy hh:mm”);
};
};

Nope still facing the same Error.

  1. This time I have removed the spacing in the name.
  2. Still showing Error in Code line 6.
  3. Should I remove the " ’ " in the ‘TIME’ and ‘B’?
16

Probably because you copied it from here the quotation marks are showing the wrong way. Is the TIME and MM/dd… thing colored brown or are they black?

17

@Raj_Mehta since you replied to the wrong post I’ll take it here.

Yes you can add as many columns as you would like, whether it be timestamp or switches. Do you want them to show up in your sheet or not?

18

Yes, would like it to show-up on the sheet.

19

You can either add it in the editor (with the right type), it will show up in the Sheets, or you can just add it straight to your sheet and refresh the data.

Mind you not every column will be synced back to Sheet. Maths, relations, lookups, user-specifics etc. won’t be synced back.

20

What I meant was, Right now the only columns that triggers timestamp is when Row ‘A#’ is edited, which can update any columns’ row. How do I use this script to use specific Columns only say row ‘C#’ to timestamp in ‘D’?