How to apply the same script for different sheets on the same spreadsheet?

Question

I have a google sheets document in which I need to run the same script separetely for each sheet, this script place a timestamp on column A whenever a cell is filled on column B

function onEdit(event)
{ 
  var timezone = "GMT-6";
  var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp 
  var updateColName = "ColumnB"; //Columna que es actualiza
  var timeStampColName = "ColumnA"; //Columna actualizada automáticamente
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); //Sheet 

  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 > 1 && 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);
  }
}

When I write another script for columnA, columnB and Sheet2, only puts the timestamp in sheet1, even when I write something on columnB from Sheet2.

Any suggestions?

One of the suggestions was this:

    function onEdit(event)
{ 
  var timezone = "GMT-6";
  var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp 
  var updateColName = "Timestamp"; //Columna que es actualiza
  var timeStampColName = "Ticket"; //Columna actualizada automáticamente
  var sheet = event.source.getActiveSheet();
  var actRng = event.source.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 > 1 && 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);
  }
}

But it does not place the timestamp on the columnA.

Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, feel free to tell me. I would like to study to solve your issues. — Tanaike
The same keyword cannot be used for multiple function definitions - only one is kept. — tehhowch

Tanaike Tanaike · Accepted Answer · 2018-06-17T22:54:04

When the column B is edited, you want to put the timestamp to the column A for the edited sheet which has ColumnA and ColumnB in the cells "A1" and "B1". For example, when you edit the column B at Sheet1, it puts the timestamp to the column A of Sheet1. When you edit the column B at Sheet2, it puts the timestamp to the column A of Sheet 2. If my understanding is correct, how about this modification? I think that there are several answers for your situation. So please think of this as one of them.

From :

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); //Sheet 
var actRng = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();

To :

var sheet = event.source.getActiveSheet();
var actRng = event.source.getActiveRange();

If my understanding is not what you want, please tell me. I would like to modify my answer.

How to apply the same script for different sheets on the same spreadsheet?

2 Answers

From :

To :