How to add a timestamp in specific cell whenever a cell in column “Column A” of the “Sheet Name” is edited
The onEdit function you provided is designed to add a timestamp in the adjacent cell whenever a cell in column A or E of the “SheetName” sheet is edited.
To add a record to the same cell every time the sheet is edited, you could modify the function like this:
function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getRange("A1"); // change this to the cell where you want to add the record
if (sheet.getName() == "SheetName" && sheet.getActiveCell().getColumn() == 0) {
// if we're on the right sheet and editing column A
cell.setValue(cell.getValue() + "New Record Added on " + new Date());
}
}
In this version of the function, we’re setting the cell variable to the range of the cell where you want to add the record (currently set to cell A1, but you can change this to any other cell). Then, we’re checking if we’re on the “RESTAPI” sheet and editing column A. If those conditions are met, we’re appending a new record to the cell’s existing value, along with the current date and time.
Note that this will overwrite the existing value in the cell every time the sheet is edited. If you want to add a new record to the cell without overwriting the existing value, you’ll need to modify the code to retrieve the current value of the cell and concatenate the new record to it.
How to add record on same cells for every row in the table
If you want to add a record to the same cell for every row in the table, you can modify the onEdit function like this:
function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // adjust this to the first row of the table
var endRow = sheet.getLastRow(); // gets the last row of the sheet
var cell = sheet.getRange("A2"); // change this to the cell where you want to add the record
if (sheet.getName() == "SheetName" && sheet.getActiveCell().getColumn() == 0) {
// if we're on the right sheet and editing column A
for (var i = startRow; i <= endRow; i++) {
var rowRange = sheet.getRange(i, 1, 1, 5); // adjust the range to cover all columns in the table
var rowValues = rowRange.getValues()[0];
cell.offset(i - startRow, 0).setValue(rowValues.join(", ") + "New Record Added on " + new Date());
}
}
}
In this version of the function, we’re first setting the startRow variable to the first row of the table (assuming the table starts on row 2), and the endRow variable to the last row of the sheet. Then, we’re setting the cell variable to the range of the cell where you want to add the record (currently set to cell A2, but you can change this to any other cell).
Next, we’re checking if we’re on the “SheetName” sheet and editing column A. If those conditions are met, we’re looping through all the rows in the table using a for loop, and for each row, we’re getting the values of all the columns using getValues, joining them together into a string using join, and then appending a new record to that string along with the current date and time.
Finally, we’re using offset to set the value of the appropriate cell in the same row as the current row in the loop. Note that we’re subtracting startRow from i to get the correct offset, since offset uses a zero-based index.