CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
filwing
filwing

Reputation: 21

Google Sheet: Insert timestamp in specific rows while edit specific range

Because I'm low of scripting but I really have to use it (I didn't found any other solution) I would like to ask you for a help with timestamp. I'm looking for help with function onEdit that would allow me to add timestamp in specific column (S) in each of row while editing specific ranges F4:Q4, F5:Q5. The most important thing there is that I would like to avoid few rows (because it's a title rows and I don't want to add timestamp while changing title rows)

Part of it is working with code below, But there's not included mechanism of editing only specific range (any change in whole row is noticed as a modification) and also there is nothing for skipping the title rows

function onEdit(e) {
  // Your sheet params
  var sheetName = "Sklad";
  var dateModifiedColumnIndex = 4;
  var dateModifiedColumnLetter = 'S';

  var range = e.range; // range just edited
  var sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {
    return;
  }

  // If the column isn't our modified date column
  if (range.getColumn() != dateModifiedColumnIndex) { 
    var row = range.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "dd/MM/yy, hh:mm:ss");
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
    dateModifiedRange.setValue(time);
  };
 };

I would be very grateful for your help, Regards

Upvotes: 1

Views: 240

Answers (2)

filwing
filwing

Reputation: 21

Thank you @James

It works for me as

function onEdit(e) {
  // Your sheet params
  var sheetName = "Sklad";
  var dateModifiedColumnIndex = 3;
  var dateModifiedColumnLetter = 'S';

  var range = e.range; // range just edited
  var sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {
    return;
  }

  // If the column isn't our modified date column
  if (range.getColumn() != dateModifiedColumnIndex && range.getRow() > 3 &&  range.getRow() != 8 &&  range.getRow() != 15 &&  range.getRow() != 32 &&  range.getRow() < 49) { 
    var row = range.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT", "dd-MM-yyyy");
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
    dateModifiedRange.setValue(time);
  };
 };

Upvotes: 1

James D
James D

Reputation: 3152

You can add the number of header rows as part of your second if()

This change will not add a timestamp in the first 4 rows if edited.

if (range.getColumn() != dateModifiedColumnIndex && range.getRow() > 4) {

Upvotes: 1

Related Questions