Event from one sheet dumping timestamp on another sheet

Question

I need to create an operation on another sheet of where the event came from.

For example, when any cell in the "data" sheet is edited/changed, dump a timestamp in the "other" sheet (within the same spreadsheet). If it helps to make the code faster, the "data" sheet will at least always have cell 1,1 edited even if it's the same value because something else automatically dumps data and re-writes the entire "data" sheet.

The main problem is that I don't know what value to compare the conditional statement with since I don't know what values will change in the see. See below.

// Dumps the timestamp of the last time the "Data" sheet was edited
function onEdit(e){
  var dataSheetLastColumn = thisSpreadsheet.getSheetByName("Data").getLastColumn();
  var dataSheetLastRow = thisSpreadsheet.getSheetByName("Data").getLastRow();
  var dataSheetEvent = e.source.getSheetByName("Data").getRange(1, 1, dataSheetLastRow, dataSheetLastColumn);

  // puts a time-stamp on another sheet
  var theOtherSheet = thisSpreadsheet.getSheetByName("Other");
  theOtherSheet.getRange(2, 1).setValue(new Date());
}

You aren't missing anything except what constitutes a triggering event. Formula updates and programmatic updates? Nope. User edits? Yep. — tehhowch, Mar 27 '19 at 23:48
Possible duplicate of [Trigger script on non manual change/edit to cell - google script](https://stackoverflow.com/questions/52087609/trigger-script-on-non-manual-change-edit-to-cell-google-script) — tehhowch, Mar 27 '19 at 23:50
I edited my question as you asked. But it's not the same as the link you provided. The link shows how to compared with values in another sheet to make the operation. I don't know the values that will be there. I just want something to happen when the sheet changes in general. — , Mar 28 '19 at 00:31
There are probably better suggested duplicates. My point was that if you need to catch programmatic edits, you need to use a time based trigger and compare current state with a stored state. Or just modify the other function to call your edit handler. — tehhowch, Mar 28 '19 at 00:41

Cooper · Accepted Answer · 2019-03-28T00:56:47.833

0

try this:

function onEdit(e) {
  if(e.range.getSheet().getName()=='Other'){return;}  
  e.source.getSheetByName('Other').getRange(2,1).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
}

Note: this event only occurs on user edits.

Simple Trigger Restrictions

    function onEdit(e) {
      if(e.range.getSheet().getName()!='Data' ) {return;}
      e.source.getSheetByName('Other').getRange(2,1).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
    }

edited Mar 28 '19 at 00:56

answered Mar 28 '19 at 00:32

Cooper

59,616
6
23
54

OK... In my initial question, I had said that whenever a change happens on "data" sheet it puts a time stamp in "other" sheet. Because all these sheets are on in the same spreadsheet, no matter what change I do in any of the spreadsheets, it works. However, I only want it for when the "Data" sheet is changed. – Mar 28 '19 at 00:43
OK, I understand now... The source event is for all the sheets. I thought I needed to assign the event somehow to sheet first before using it. But it looks like it looks at everything going on in the spreadsheet. Jeez.... Thank you! – Mar 28 '19 at 01:06
Yes all scripts in a given project have access to all sheets within the spreadsheet. – Cooper Mar 28 '19 at 01:30

Event from one sheet dumping timestamp on another sheet

1 Answers1