I created the code below based on lots of googling and inspiration from this post. I'm new to coding so you'll have to forgive my ignorance as I'm sure some of my methods are a bit rudimentary. I'm hoping you guys can offer some tips on how to improve my existing code and/or implement the modifications I laid out below.
The purpose of my code is to generate timestamps when certain cells in certain sheets are modified.
Sheet 1: Videos
Cells that trigger timestamp: Columns 1, 2, 3, 4, 5, 22, 23, 24 where Row > 10
Cells that receive timestamp: Column Y if blank, else Column Z
Sheet 2: Categories
Cells that trigger timestamp: Columns 13, 16, 17 where Row > 3
Cells that receive timestamp: Column T if blank, else Column U
My code
/*----------------------------Timestamps for Videos----------------------------*/
function onEditVideoTimestamps() {
//check to make sure on the right sheet
var s = SpreadsheetApp.getActiveSheet()
if (s.getName() == "Videos") {
//check to make sure edited row and column is within range
var cell = s.getActiveCell();
var rownbr = cell.getRow();
var colnbr = cell.getColumn();
var nbrcolumns = s.getMaxColumns();
var r = s.getRange(rownbr, 1, 1, nbrcolumns);
var rowArray = r.getValues();//now all your data for that row is in a two Dimensional array [[1,2,3,4,'My Data','etc']]
if (rownbr > 10 && (colnbr == 1 || colnbr == 2 || colnbr == 3 || colnbr == 4 || colnbr == 5 || colnbr == 22 || colnbr == 23 || colnbr == 24)) {
//is Y empty?
var checkY = s.getRange('Y' + rownbr.toString()).getValue()
if(checkY == '') {
//Yes
var time;
time = new Date();
time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
//the column letter is where the timestamp will appear
SpreadsheetApp.getActiveSheet().getRange('Y' + rownbr.toString()).setValue(time);
} else {
//No
var time;
time = new Date();
time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
//the column letter is where the timestamp will appear
SpreadsheetApp.getActiveSheet().getRange('Z' + rownbr.toString()).setValue(time);
}
}
/*----------------------------Timestamps for Categories/Albums----------------------------*/
} else {
//check to make sure on the right sheet
var s = SpreadsheetApp.getActiveSheet()
if (s.getName() == "Categories") {
//check to make sure edited row and column is within range
var cell = s.getActiveCell();
var rownbr = cell.getRow();
var colnbr = cell.getColumn();
var nbrcolumns = s.getMaxColumns();
var r = s.getRange(rownbr, 1, 1, nbrcolumns);
var rowArray = r.getValues();//now all your data for that row is in a two Dimensional array [[1,2,3,4,'My Data','etc']]
if (rownbr > 3 && (colnbr == 13 || colnbr == 16 || colnbr == 17)) {
//is T empty?
var checkT = s.getRange('T' + rownbr.toString()).getValue()
if(checkT == '') {
//Yes
var time;
time = new Date();
time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
//the column letter is where the timestamp will appear
SpreadsheetApp.getActiveSheet().getRange('T' + rownbr.toString()).setValue(time);
} else {
//No
var time;
time = new Date();
time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
//the column letter is where the timestamp will appear
SpreadsheetApp.getActiveSheet().getRange('U' + rownbr.toString()).setValue(time);
}
}
}
}
}
Problems
This code can take upwards of 40 seconds to run. Ideally it would take no more than 10, even less if possible since it's triggered onEdit. As it stands, I exceed my daily GAS limit pretty quickly.
I would like to add the following logic to my script:
Videos
- If column 1 is changed to "Approved", a new timestamp is generated in the initial timestamp column (Y) even if Y is already populated. Column 1 is a status field that can be changed at any time to "Approved", "Pending", "Removed", or "Denied". If it's changed to "Approved", I want Y to get the timestamp regardless of its contents. Otherwise, my current logic applies: If Y is populated, then the timestamp goes in Z (the Last Modified Timestamp).
Categories
I really just want the same change for this sheet, just different cell references.
- If column 13 is changed to "Approved", a new timestamp is generated in column T regardless of whether or not it's already populated. If any other change is made within the range, and T is already populated, then the timestamp is placed in U.
Like I said, I'm very much a beginner, so any help is appreciated. Let me know if I need to clarify anything. Thanks
