-1

I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell. But the problem is that I want to use the same script for multiple instances in multiple tabs of google sheets. But it is only working for 1st instance.

You can see my sheet here - https://docs.google.com/spreadsheets/d/1qLshSOsv1b7lc9s8F2RVV0CsDqBdiIv7p4MAVYOxShQ/edit?usp=sharing

In the first tab "Sheet 1' We have columns like Update 1 and 'Update 1' timestamp also 'Update 2' and 'Update 2 timestamp' Here I want to use same script for both update 1 and update 2 timestamp.

Same goes with 'Sheet2'

Script I am using:


​ ​function onEdit(event)
{ 
  var timezone = "GMT+05:30";
  var timestamp_format = "dd-MM-yyyy hh:mm"; // Timestamp Format. 
  var updateColName = "Update 1";
  var timeStampColName = "U1 - Timestamp";
  var sheet = event.source.getSheetByName('Sheet1'); //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);
  }
}

Kindly help me how I can use the same above script multiple times in same tab and in other tabs in a Google Sheet.

​Any kind of help is much appreciated. Thanks in advance.

Regards, Rahul.

0

Welcome to Web Apps SE! I hope you get your project working the way you need.

When something is edited, the onEdit function you made is called, with the event of the edit passed to it. If we want many possible different things to happen onEdit, we can test for them all and set values accordingly, right there in that one function. For example:

function onEdit(event) {
  // Settings
  var timezone = "GMT+05:30",
    timestamp_format = "dd-MM-yyyy hh:mm",
    targetSheet = "Sheet1",
    u1c = 3,
    u2c = 5,
    u3c = 7;

  // Init
  var editSheet = event.range.getSheet(),
    editRow = event.range.getRow(),
    editCol = event.range.getColumn();

  if (editSheet.getName() != targetSheet) {return};
  Logger.log("Edit was in timestamp-required sheet.");
  if ( !( editCol==u1c || editCol==u2c || editCol==u3c ) ) {return};
  Logger.log("Edit was in an 'Update x' column.");

  var cell = editSheet.getRange(editRow, editCol + 1);
  Logger.log("Target timestamp cell is: R"+editRow+"C"+(editCol+1));
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
  Logger.log("Current timestamp is: "+date);
  cell.setValue(date);
}

In English, first it
sets up your variables,
but also tells the script about what sheet we care about and for your three columns "Update 1", -2, and -3, what their column numbers are.
Then it grabs the sheet, row, and column of the edit that the user just made back in the sheet.

Next, on to testing, it returns out of the onEdit function completely if the edit was on a different sheet, or then also if they were not in one of the "watched" columns. If we've passed everything to that point, it writes your timestamp in the row that was edited, but right one column.

Note that I dropped your checks for clarity, and added some Logger calls. You can see the log after any Sheet edit by returning to the script and from the menus choosing View → Logs. You can delete them or turn them to comments if you like.

I hope you can adapt something like the above to your file. Please follow up with us and comment if this answer missed the target or needs more explanation.

  • Please avoid the so called "noise" on Q & A like "Welcome to ..." --> Expected behavior – Rubén Apr 12 at 22:01
  • I love the cleanliness of SE. It's not a forum post, or a business letter. I know the rules, and support the reasons behind them. In that context, I write answers in a conversational tone iff the asker is a new user. I've also observed markedly lower user retention when the only answer is technical, "cold", and explanation-less. Will Rahul come back? So your "avoid noise" request is noted and I agree. But I think the <1 line welcome is utterly appropriate here in leading my chatty prose answer. Reading the article you linked to—above "Do not use … greetings" is "Be nice" and "Be Welcoming". – Joel Reid Apr 13 at 20:54
  • I think that warm welcome and other social conventions should be posted as comments rather than on the question/answers. – Rubén Apr 14 at 0:26

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.