Help Please! Need Auto timestamp when a cell in a given range has data entered.

[Shawn Michaud]

Hello! Im currently looking for a script solution that can automatically add a timestamp in an empty cell - when another cell in an adjacent column has data entered. 

 *This timestamp needs to be activated only when a cell in specific range is updated and NOT the entire spreadsheet.* 

I do have a working script, but the closest I could get to this function working correctly is when ANY cell within the sheet is updated a cell 5 columns to the right will display a timestamp of when the data was entered. 

What I would like to accomplish is: Only having a timestamp being activated when a cell within a specific cell range is updated. For example:; When cells A10-A125 have a data entry (say A10) cell F10 will automatically populate with the time the data in cell A10 was updated. 

I do not want an automatic timestamp being entered in cell F1 when A1 has data entered. 

This is the current code im using (which doesn't quite do what I need it to specifically): 

function onEdit() {

  var s = SpreadsheetApp.getActiveSheet();

  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet

    var r = s.getActiveCell();

    if( r.getColumn() == 1 ) { //checks the column

      var nextCell = r.offset(0, 5);

      //if( nextCell.getValue() !== '' ) //is empty?

      nextCell.setValue(new Date());

    }

    if( r.getColumn() == 7 ) { //checks the column

      var nextCell = r.offset(0, 5);

      //if( nextCell.getValue() !== '' ) //is empty?

      nextCell.setValue(new Date());

    }

    if( r.getColumn() == 13 ) { //checks the column

      var nextCell = r.offset(0, 5);

      //if( nextCell.getValue() !== '' ) //is empty?

      nextCell.setValue(new Date());

    }

    if( r.getColumn() == 19 ) { //checks the column

      var nextCell = r.offset(0, 5);

      //if( nextCell.getValue() !== '' ) //is empty?

      nextCell.setValue(new Date());

    }

    if( r.getColumn() == 25 ) { //checks the column

      var nextCell = r.offset(0, 5);

      //if( nextCell.getValue() !== '' ) //is empty?

      nextCell.setValue(new Date());

    }

  }

}

Rather than having the entire column being referenced, I would like to be able to apply this to a cell range. Specifically cells A10:A125, G10:G125, M10:M125, S10:S125, Y10:Y125

Can someone please help me find a solution to this? ANY help would be very appreciated. Thanks!

[Tanaike]

In your situation, how about the following modified script?

function onEdit() {
  var ranges = ["A10:A125", "G10:G125", "M10:M125", "S10:S125", "Y10:Y125"]; // This is from your question.

  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getSheetName() != "Sheet1") return;
  var range = sheet.getActiveRange();
  var row = range.getRow();
  var col = range.getColumn();
  var check = ranges.some(r => {
    var rng = sheet.getRange(r);
    var startRow = rng.getRow();
    var endRow = rng.getNumRows() + startRow - 1;
    var startCol = rng.getColumn();
    var endCol = rng.getNumColumns() + startCol - 1;
    return row >= startRow && row <= endRow && col >= startCol && col <= endCol;
  });
  console.log(check)
  var nextCell = range.offset(0, 5);
  nextCell.setValue(new Date());

  // if( !nextCell.isBlank() ) { // If you want to run when the cell is not empty, please use this.
  //   nextCell.setValue(new Date());
  // }
}

In this modification, from your question, when the cells of "A10:A125", "G10:G125", "M10:M125", "S10:S125", "Y10:Y125" are edited, the whole script is run.

From your showing script, I thought that you might have wanted to directly run the script with the script editor. So I modified your script without using the event object of OnEdit. Please be careful about this.

[Jason Cobble]

hi,

I'm in need of one very smart multilingual when it comes to coding and network infrastructure as well as current on the trending SaaS, AWAOSP, multicloud security and policy and federation as well asulti group structural management and policies as well as all the changing every single day terms and acronyms.

This is potential for multiple projects that would have you set for life.

I'm only asking for one at the time being but will more than likely need you to find your doppelganger when the time comes. I can't run you 24/7..

Yes I realize where I'm posting this and I can't think of a better place.

Email me... ja...@somethinghuman.com if interested.

Thank you for your time

<3

--
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/9972a743-3088-4eab-a2fa-e69e66a07d8dn%40googlegroups.com.

[Shawn Michaud]

Thanks for the response! Unfortunately when testing the code you've provided, cells other than the target cells i've listed are still generating timestamps when data is entered. 
Although I could be doing something wrong. Not completely sure about what you mean here:

 " From your showing script, I thought that you might have wanted to directly run the script with the script editor. So I modified your script without using the event object of OnEdit. Please be careful about this"

[Keith Andersen]

This explains and demonstrates what you're trying to accomplish. A link to the spreadsheet is in the YouTube video description. Copy the spreadsheet to view the app script and apply it to your situation.

https://youtu.be/EWagkthsleU

Keith

--

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/508ad028-e8b4-4e00-9bbf-37fc0ed92e6fn%40googlegroups.com.

[Shawn Michaud]

This solution worked perfectly! Thank you Keith! Much appreciated. 

[Keith Andersen]

My pleasure.

Cheers 

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/990d3ccc-5ee5-4ea9-ad74-467b23cf8a2an%40googlegroups.com.