# Timestamp on switch action

**URL:** <https://community.glideapps.com/t/timestamp-on-switch-action/8277>\
**Category:** Ask for Help\
**Created:** [May 1, 2020, 11:33am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277 "2020-05-01T11:33:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![D\_J](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/d_j/32/6213_2.png) [@D\_J](https://community.glideapps.com/u/D_J)\
**Post date:** [May 1, 2020, 11:33am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/1 "2020-05-01T11:33:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 11:38am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/2 "2020-05-01T11:38:57Z")

</div>

Hi D\_J,

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

I will make a demo later for you.

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [May 1, 2020, 11:41am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/3 "2020-05-01T11:41:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 11:50am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/4 "2020-05-01T11:50:15Z")

</div>

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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/6fb57b2e7a6b95c90477e6cb1a457ed0ca1c4071.png)

The way it works:

![ezgif-1-13d8c8315c0d](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/2/247f1c08617eb3466508fd67d09cc124e1f9939c.gif)

---

<div class="post-metadata">

**Author:** ![D\_J](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/d_j/32/6213_2.png) [@D\_J](https://community.glideapps.com/u/D_J)\
**Post date:** [May 1, 2020, 2:49pm UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/5 "2020-05-01T14:49:06Z")

</div>

Thank you @ThinhDinh will give it a try today

---

<div class="post-metadata">

**Author:** ![D\_J](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/d_j/32/6213_2.png) [@D\_J](https://community.glideapps.com/u/D_J)\
**Post date:** [May 1, 2020, 2:50pm UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/6 "2020-05-01T14:50:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 3:20pm UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/7 "2020-05-01T15:20:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![D\_J](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/d_j/32/6213_2.png) [@D\_J](https://community.glideapps.com/u/D_J)\
**Post date:** [May 1, 2020, 3:53pm UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/8 "2020-05-01T15:53:15Z")

</div>

Great. Thank you 🙏🏽

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 3:57pm UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/9 "2020-05-01T15:57:56Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![Raj\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/raj_mehta/32/95_2.png) [@Raj\_Mehta](https://community.glideapps.com/u/Raj_Mehta)\
**Post date:** [June 17, 2020, 4:00am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/10 "2020-06-17T04:00:56Z")

</div>

> [@ThinhDinh](#):
>
> 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”);  
> }  
> };

Hi,

I tried this and I keep getting the following error.

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 17, 2020, 4:03am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/11 "2020-06-17T04:03:40Z")

</div>

> [@ThinhDinh](#):
>
> var celladdress =‘B’+ r.getRowIndex()

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

---

<div class="post-metadata">

**Author:** ![Raj\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/raj_mehta/32/95_2.png) [@Raj\_Mehta](https://community.glideapps.com/u/Raj_Mehta)\
**Post date:** [June 17, 2020, 4:12am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/12 "2020-06-17T04:12:06Z")

</div>

> 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”

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 17, 2020, 4:27am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/13 "2020-06-17T04:27:42Z")

</div>

Can you share the edit access to a copy of your sheet to [ariesarsenal@gmail.com](mailto:ariesarsenal@gmail.com)? Thank you.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 17, 2020, 7:58am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/14 "2020-06-17T07:58:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Raj\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/raj_mehta/32/95_2.png) [@Raj\_Mehta](https://community.glideapps.com/u/Raj_Mehta)\
**Post date:** [June 17, 2020, 8:41am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/15 "2020-06-17T08:41:52Z")

</div>

> 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’?

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 17, 2020, 8:42am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/16 "2020-06-17T08:42:42Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 17, 2020, 9:00am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/17 "2020-06-17T09:00:51Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![Raj\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/raj_mehta/32/95_2.png) [@Raj\_Mehta](https://community.glideapps.com/u/Raj_Mehta)\
**Post date:** [June 17, 2020, 9:07am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/18 "2020-06-17T09:07:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 17, 2020, 9:45am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/19 "2020-06-17T09:45:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Raj\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/raj_mehta/32/95_2.png) [@Raj\_Mehta](https://community.glideapps.com/u/Raj_Mehta)\
**Post date:** [June 17, 2020, 9:58am UTC](https://community.glideapps.com/t/timestamp-on-switch-action/8277/20 "2020-06-17T09:58:14Z")

</div>

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’?

[Next page](https://community.glideapps.com/t/timestamp-on-switch-action/8277.md?page=2)