Google Sheets Script Editor - Creating a Timestamp and keeping that Timestamp from changing after first entry

Question

I am new to Script Editing in Google Sheets. I am attempting to create a Timestamp in a cell in rows 31 and 32 when input is received in rows 13 and 19. Row 13 is for inputting names and row 19 is for tracking the projects completion. The names on the projects will change throughout the duration, so once the first name is created in a cell, I want to make sure the Timestamp will not change. Any ideas on how to make this work?

Editable link to an example of the sheet I am creating: https://docs.google.com/spreadsheets/d/1bsA8h0VGudmvB_qHOJrpz4UI9aRvIwRLXR3086rWAVw/edit?usp=sharing

The current script I have created:

function onEdit(e) {

var row = e.range.getRow();
var col = e.range.getColumn();

if(col === 13){
e.source.getActiveSheet().getRange(row,31).setValue(new Date()); 
if(e.source.getActiveSheet().getRange(row,31).getValue() == ""){
}
}

if(col === 19){
e.source.getActiveSheet().getRange(row,32).setValue(new Date()); 
}




}

The spreadsheet is a little incomplete, could you share how values should be in the sheet when your code works as you expect it to work? — alberto vielma
@albertovielma Updated with what it should look like. When you use the drop down to change the name, it changes the time, which is what I want to keep from happeneing. — david04

alberto vielma alberto vielma · Accepted Answer · 2020-02-28T14:37:37

For your case, I would recommend you to use the getCell(row, column) method and then evaluate if the cell is blank using the isBlank() method. This is an example code that will help you:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  // col M = 13, col S = 19
  if(col === 13) setTimestamp(ss, row, 31); // col AE = 31
  else if(col === 19) setTimestamp(ss, row, 32); // col AF = 32
}

function setTimestamp(ss, row, col){
  // Get the modified Cell
  var cell = ss.getRange(row, col).getCell(1, 1); 
  var isCellBlank = cell.isBlank();
  // Check if it's blank
  if(isCellBlank) cell.setValue((new Date()));
}

Google Sheets Script Editor - Creating a Timestamp and keeping that Timestamp from changing after first entry

1 Answers