0

I would like to make a script that works like this script to print a timestamp when a cell was last updated but in this case I want to return when it was first updated, not last.

Here is one attempt:

function onEdit(cellOrigin,cellDestination) { 
var s = SpreadsheetApp.getActiveSheet(); // Get spreadsheet name 
var r = s.getActiveCell(); // store active cell name in current spreadsheet 
var cell1 = cellOrigin // This is the row I want to put values
if(r.getRow() != cell1) { // Ignores this row (where I put the dates)
var column = r.getColumn();  // Get column # from active cell
var time = new Date(); // Get date and time of last mod
time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss"); // Format date and time
SpreadsheetApp.getActiveSheet().getRange(cellDestination).setValue(time); // put in time in cell
};
};
  • @Rubén it is a drop-down (through content validation) of three possible options ("started", "ongoing", "finished") – luisgonzalez Jan 19 at 18:05
  • @Rubén, for what understand the referred code works showing the time it was updated last i.e. every time the target cell is modified, the destination is also updated to the most recent time. I do not want this. I want the cell to store the first time the target cell was modified, and never change again. – luisgonzalez Jan 19 at 19:38
  • 1
    Veo que en tu perfil dice que eres de Argentina. Si prefieres preguntar sobre programación en español puedes hacerlo en Stack Overflow en español. – Rubén Jan 19 at 20:09
  • Me voy a registrar en SO en español! In the meantime since I already asked here we can continue here. I tweaked the code in that sample (removing static references from column or sheets) but I think something breaks in the middle because the script then enters an infinite loop and keeps adding timestamps (even if the function is removed! from a cell). Maybe there's a better way to make that referred code more generalistic and flexible so applies to any target or destination cell? – luisgonzalez Jan 19 at 20:50
  • Maybe this one is what you are looking for webapps.stackexchange.com/q/37408/88163 – Rubén Jan 19 at 21:39
1

Try something like this

// Global variables
var origin = 'A1';
var destination = 'B1';

function onEdit(e){

  if(e.range.getA1Notation() == origin && 
     e.range.getSheet().getRange(destination).isBlank()){
    e.range.getSheet().getRange(destination).setValue(new Date());
  }
}

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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