Time Stamp Trigger on Value CHANGE

[David Polansky]

Hi all, I'm very very new to Apps script and to coding period. I'm working on making a spreadsheet more dynamic for my organization, and part of this was an attempt to make a column get a time stamp to display a date when the neighboring column was updated. I have the code made so that the cells are left blank when the relevant cell is empty (the original code I found online and started with was setting time stamps whether the cell was empty or not)

My question is, how can I work to make this function only trigger when the cell value CHANGES, or if that's possible. I've done some research but it's almost exclusively turned up coding for web pages as opposed to apps script. The current code is copied below.

function setTimeStamp(x) {

if (x!="")

   return new Date()

     else  return

    }

If anyone has advice on the code itself or on some good resources for better learning Javascript syntax and function, I would appreciate either. 

Best,

David

[Bennett, Scott]

function onEdit(e) {

 

var s = SpreadsheetApp.getActiveSheet();

var r = s.getActiveCell();

var activeRow = r.getRow()// gets row of cursor

var nextCell = r.offset(0, 1);// references cell directly to the right of the active cell

var nextCellValue = nextCell.getValue(); //gets value in cell directly to the right of the active cell

Logger.log(nextCellValue)

Logger.log('New Value (from e.value) = '+e.value) //logs new value entered into active cell

Logger.log('Old Value (from e.oldValue) = '+e.oldValue)//logs old value that was in active cell

  if( r.getColumn() == 4 && activeRow >9|| r.getColumn() == 6 && activeRow>9) {

   if( nextCellValue == '' && e.value=='TRUE') {

   nextCell.setValue(new Date());

   }

   else if(nextCellValue!=''&& e.value=='FALSE'&& e.oldValue=='true'){

     nextCell.setNote(nextCellValue) //if there was an old value it sets it as a note and clears the cell.

     nextCell.clearContent()

     

   }

  }

}

This code looks to the left of the cell that gets the timestamp and adds one if it is checked true.   It is only looking at columns 4 and 6.  

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e68d88d6-7f7b-48b7-bd43-62977ccba1f5n%40googlegroups.com.

--

Scott Bennett

[David Polansky]

Thank you so much! This is way more help than I was ever hoping for when I posted this. I'm studying the code now to make sure I understand how it works before I engage it. Thank you so, so much.

Best,

David

[Keith Andersen]

I've done a video to address this and in the video is a link to a sheet that you can download and copy. Once you've copied it it unlocks it from read only to make you an editor.

https://youtu.be/EWagkthsleU

Should do what you need.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANHBT_rATaij2fvJkeO8yJNY9nprK1%2BtsAt0AmXNQ_aXdA%40mail.gmail.com.

[Bennett, Scott]

Here is the documentation for simple triggers.  There is information stored in the 'e' variable.  This will tell you what that is.  

https://developers.google.com/apps-script/guides/triggers

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/852b94a2-8b92-4a3f-b0fd-361fadcff27en%40googlegroups.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010

[David Polansky]

Scott, I'm so sorry to bother you. I've added your code and changed the column designation to reflect the location of the data in our spreadsheet, but it's not working. Can I link the sandbox copy (scrubbed of sensitive data)? If you're too busy I completely understand; I'm just trying to wrap my head around what specifically the problem is

Best,

David

On Wednesday, May 31, 2023 at 2:39:28 PM UTC-4 sben...@bbchs.org wrote:

[Bennett, Scott]

Sure,

I have deleted the and/or in the if statement.  Now it is only looking at the 4th column.

https://docs.google.com/spreadsheets/d/1C097MIvst_qI69f_DehVOcf9tdfok_yuq_ZRgkvZL-0/edit?usp=sharing

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0ba4947f-a1fd-4bcb-9f1d-64a3a2a5680an%40googlegroups.com.

[Bennett, Scott]

May need to refresh, I had it on view only.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0ba4947f-a1fd-4bcb-9f1d-64a3a2a5680an%40googlegroups.com.

[Bennett, Scott]

New link

https://docs.google.com/spreadsheets/d/1C097MIvst_qI69f_DehVOcf9tdfok_yuq_ZRgkvZL-0/edit?usp=sharing

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0ba4947f-a1fd-4bcb-9f1d-64a3a2a5680an%40googlegroups.com.

[David Polansky]

https://docs.google.com/spreadsheets/d/1V5-8yjg9fzIbL29gbq-4_eNlFcGi9QCXBQF1be9h6zU/edit?usp=sharing

Here's the link to my test document

[David Polansky]

Thank you all so much! I figured out the problem! 

Here's the code:

function onEdit(e) {

 

var s = SpreadsheetApp.getActiveSheet();

var r = s.getActiveCell();

var activeRow = r.getRow()// gets row of cursor

var nextCell = r.offset(0, 1);// references cell directly to the right of the active cell

var nextCellValue = nextCell.getValue(); //gets value in cell directly to the right of the active cell

Logger.log(nextCellValue)

Logger.log('New Value (from e.value) = '+e.value) //logs new value entered into active cell

Logger.log('Old Value (from e.oldValue) = '+e.oldValue)//logs old value that was in active cell

  if( r.getColumn() == 13 && activeRow >1) {

   if( nextCellValue == '' && e.value!=='') {

   nextCell.setValue(new Date());

   }

   else if(nextCellValue!=''&& e.value!==''&& e.oldValue==''){

     nextCell.setNote(nextCellValue) //if there was an old value it sets it as a note and clears the cell.

     nextCell.clearContent()

     

   }

  }

}

I hadn't been looking closely enough to realize that the TRUE/FALSE reflected the specifics of the cell information; I've changed it now so that it will update for any value changed.

Thank you so, so, so much.

[David Polansky]

Had to play with it a little bit, here's what I came up with. Ideally I'd want it to also clear the neighboring cell if data in the input column is deleted but I can absolutely live without that. 

function onEdit(e) {

 

var s = SpreadsheetApp.getActiveSheet();

var r = s.getActiveCell();

var activeRow = r.getRow()// gets row of cursor

var nextCell = r.offset(0, 1);// references cell directly to the right of the active cell

var nextCellValue = nextCell.getValue(); //gets value in cell directly to the right of the active cell

Logger.log(nextCellValue)

Logger.log('New Value (from e.value) = '+e.value) //logs new value entered into active cell

Logger.log('Old Value (from e.oldValue) = '+e.oldValue)//logs old value that was in active cell

  if( r.getColumn() == 13 && activeRow >1) {

  if( nextCellValue!==new Date && e.value!==e.oldValue) {

   if(e.value!=='' && e.value!==e.oldValue) {