Want to create timestamp for time added and create unique incremental user ID. And also include a last changed column

Question

I am using Google Sheets for a (originally planned to be) quick and easy database. Users submit a form, it is then sent to a Sheets for storing.

I have three columns that I'm trying to auto-update through Google Apps Scripts:

1. A 'user ID' (this should be automatically created when a new row is added, and shouldn't be able to be changed)
2. A 'createdAt' column, which is just a timestamp for when the row was added
3. A 'lastChanged' column, which represents the last time a row was changed.

I've used advice from How can I run a script only when someone adds new rows and Determining the last row in a single column, but I'm still having problems.

I'm running it on an onEdit event right now. But it seems that the 'lastChanged' column is the only one that should be triggered by this event, the others should be used with something like an changeType=='INSERT_ROW'. When the user details are submitted (through Zapier), a row is being added, and so the onEdit doesn't work for setting the userId and createdAt.

I also know that my method for generating the userID is far from ideal.

Here is where I have got so far.

function onEdit(e) {

var userId = 1 // column A  
var createdTime = 2 //column B
var lastChangedTime = 24 //column X

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = e.source.getSheets()[1]; //gets the second sheet
var editedCell = sheet.getActiveCell(); //gets the edited cell

var Avals = ss.getRange("A2:A").getValues();
var Alast = Avals.filter(String).length;

//set date in same row as edit happens, at fixed column  
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), lastChangedTime, 1, 1).setValue(new Date());

//set the created time
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), createdTime, 1, 1).setValue(new Date());
//set the userID
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), userId, 1, 1).setValue(Alast+1); }  

Answer 1

Update from original poster here - I have a solution that isn't the most elegant, but serves my purposes. Criticism & suggestions very much welcome :)

Now on to figuring out the best way to insert dynamic formulas via GAS...

function onChange(e) {

var userId = 1 // column A  
var createdTime = 2 //column B
var lastChangedTime = 24 //column X

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = e.source.getSheets()[1]; //gets the second sheet

var Avals = ss.getRange("A2:A").getValues(); //gets all the values of column A 
var Alast = Avals.filter(String).length; //puts all values in a string and gets the length

var now = new Date();

if (e.changeType == "INSERT_ROW") {

//set the created time
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), createdTime, 1, 1).setValue(new Date());
//set the userID
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), userId, 1, 1).setValue(Alast+1);

//set lastChangedTime when the row is created 
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), lastChangedTime, 1, 1).setValue(now);
   } 

  if (e.changeType == "EDIT") {

//set date in same row as edit happens, at fixed column  
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), lastChangedTime, 1, 1).setValue(new Date());

   }  
}