I am trying to write a google scripts app that picks up on a gsheets users email whilst they fill in a value into a row of a cell (promotion cell) in A and writes the email to the email cell (B) then the date they filled it in into the Date cell (C).
| Promotion Cell | Date | |
|---|---|---|
| 1 | johndoe@gmail | 2022-10-10 |
The idea is that using the onEdit I can get a users email and the date automatically in rows B and C. But for some reason, my code is not working the error being on the range variable in the fillEmailAndDate function. I am not sure why this is not executing.
function onEdit(e) {
Logger.info(e)
// get the active sheet
sheet = e.source.getActiveSheet()
// Get the active cell
var cell = e.source.getActiveCell();
// Get the row and column of the active cell
var row = cell.getRow();
var col = cell.getColumn(); // 3.0
// Get the letter of the promotion column
var promotionColumn = 'C';
// Get the letters of the email and date columns
var emailColumn = 'E';
var dateColumn = 'F';
// If the active cell is in the promotion column
if (Math.round(col) == getNumber(promotionColumn)) {
// Run the fillEmailAndDate function
fillEmailAndDate(e, coverage_sheet, promotionColumn, emailColumn, dateColumn, row);
}
}
function fillEmailAndDate(e, sheet, promotionColumn, emailColumn, dateColumn, lastRow) {
// Get the range of cells to be checked
var range = sheet.getRange(promotionColumn + "2:" + promotionColumn + lastRow);
// Use the onEdit trigger to detect when a cell in the promotion column is edited
sheet.getRange(range).setOnEdit(function(e) {
// Get the active cell
var cell = e.source.getActiveCell();
// Get the row and column of the active cell
var row = cell.getRow();
var col = cell.getColumn();
// If the active cell is in the promotion column
if (col == promotionColumn) {
// Get the email and date columns for the active row
var emailCell = sheet.getRange(emailColumn + row);
var dateCell = sheet.getRange(dateColumn + row);
// Get the user's email address from the email column
var email = Session.getActiveUser().getEmail();
// Fill in the email and date cells with the user's email address and the current date
emailCell.setValue(email);
dateCell.setValue(new Date());
}
});
}
function getNumber(letter) {
// Make sure letter is uppercase
letter = letter.toUpperCase();
// Convert letter to a character code and return the corresponding number
return letter.charCodeAt(0) - 64;
}
