How can I set up a datestamp in Google Sheets for specific rows?

Question

I am trying to set up a Google Sheets spreadsheet where, when a specific cell is edited, the date in the cell to the right updates to indicate that a change has been made. On the Google Sheets forum, someone directed me here for the best guidance.

The script itself is working perfectly. However, I'd like to limit it to specific cells (or rows--either will work) so that it does not add a date stamp to my header rows. What do I need to add to the script to apply this to specific cells/rows?

Here's my script as it is now:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 3 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM/dd/yy");
    SpreadsheetApp.getActiveSheet().getRange('C' + row.toString()).setValue(time);
  };
 };

Hypothetically, let's say that I have a sheet with 30 active rows. I want the datestamp to appear in Rows 9 through 13; 15 through 18; and 20 through 26. The other rows are all headers and I want to be able to change the text there without Sheets populating the adjacent cell with a datestamp. How would I go about editing the script to achieve this in Google Sheets, assuming it's possible? Thank you!!

Serge insas Serge insas · Accepted Answer · 2014-06-23T22:40:13

just add more conditions to your column condition.

for example for the 2 first series of rows :

  if( r.getColumn() != 3 && ((row>8 && row<14)||(row>14 && row<19))){ // continue with other AND and OR for all your conditions

btw, I would also suggest to replace the hard coded time zone with this dedicated method :

var tz = Session.getScriptTimeZone();
var time = Utilities.formatDate(new Date(),tz, "MM/dd/yy");

it will match the Time zone of your sheet automatically, even if you are in a country that has daylight savings.

How can I set up a datestamp in Google Sheets for specific rows?

1 Answers