0

I am having 10 Sheets for making entries and I want to update Current Time and Date in Column last Modified when changes have been made in Column Cum, i was able to do so but editing Cum in any Sheets (all sheets are Identical) adds current Date and Time to Last Modified Column of All Sheets,I also tried to change name of Last Modified Column for Different Sheets still It does the Same I am Adding Script for two sheet.

First I tried using these but it does Update Column Last Modified in all sheets:

    function onEdit(event)
    { 
      var timezone = "IST";
      var timestamp_format = "dd-MM-yyyy hh:mm:ss"; 
      var updateColName = "(Cum)";
      var timeStampColName = "Last Modified";
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('C.Entry - 1');
      var actRng = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
      var editColumn = actRng.getColumn();
      var index = actRng.getRowIndex();
      var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
      var dateCol = headers[0].indexOf(timeStampColName);
      var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
      if (dateCol > -1 && index > 2 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
        var cell = sheet.getRange(index, dateCol + 1);
        var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
        cell.setValue(date);
      }
    }

I tried changing Column Name for Different Sheets in different sheets:

function onEdit(event)
{ 
  var timezone = "IST";
  var timestamp_format = "dd-MM-yyyy hh:mm:ss"; 
  var updateColName = "(Cum)";
  var timeStampColName = "Last Modified2";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('C.Entry - 2');
  var actRng = sheet.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 2 && editColumn == updateCol) { // only timestamp if 'Last Modified2' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

Last Modified Column must update only if Cum Column in that Particular Sheet is updated, I am also wondering if I can Define A whole Range Instead of a Particular Column For Eg. Whenever Range A3:J3 is updated it must create new timestamp in Column Last Updated

ross
  • 2,684
  • 2
  • 13
  • 22
Trushit Kakrecha
  • 1
  • Have you tried getting the sheet name from the Event Object, such as `var eventSheet = event.range.getSheet().getName();`? Then use the variable to be define the sheet that is to be updated; for example, eventSheet.getRange(index, dateCol + 1); – Tedinoz Jul 25 '19 at 07:45
  • Regarding "Whenever Range A3:J3 is updated it must create new timestamp in Column Last Updated", look at the answer to [How do I use variables to avoid having to create a set of these for all of the columns in my sheet?](https://stackoverflow.com/a/57193102/1330560); the "IF" statement evaluates whether an edit occurred on a given sheet, within a given column range, and within a given row range. That's the kind of approach that you would need to take. – Tedinoz Jul 25 '19 at 07:52
  • Could you share an example sheet to see how your data is organized and be able to go over the code? – AMolina Jul 25 '19 at 13:22

0 Answers0