4

I have a Sheets file with a tab/sheet for each month. I am editing the Script Editor to apply an automatic Timestamp when an item is scanned. How do I make the same script for each sheet, but make it that it only adds the timestamp to that specific sheet I'm working in and not all sheets?

Here's what I have. I have a file currently for each month, but when scanned in March, it goes to all months.

function onEdit(event)
{
  var timezone = "GMT-5";
  var timestamp_format = "MM-dd—yyyy"; // Timestamp Format.
  var updateColName = "ITEM";
  var timeStampColName = "DATE";
  var sheet = event.source.getSheetByName('MARCH'); //Name of the sheet where you want to run this script.  

  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);
  }
}
  • 1
    Welcome. Please add the code as text instead of screenshot. Also add what you searched/researched regarding this situation. Referencs How to Ask. – Rubén Mar 24 '17 at 22:34
3

Get the sheet that was edited. Change code to:

var sheet, ss;

ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getActiveSheet();//Get the active sheet

Now the sheet variable is the active sheet that was just edited. So anything done with sheet will affect that sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.