I have made a script that google sheet
writes me the modification date of a specific range of cells and now I want to make it detect the cell that I am modifying and detect if it is within specific ranges.
This is the script I have:
function onEdit(HoraModificacion){
var DateCol = "K9";
var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
var range = sheet.getRange('C11:K109');
var rowIndex = range.getRowIndex();
var colIndex = range.getColumnIndex();
// May need to set up multiple ranges like this and check all ranges if
// checked fields are not in adjacent cells
var watchRange = {
top : 11, // start row
bottom : 109, // end row
left : 2, // start col
right : 12, // end col
};
// Exit if we're out of range
if (rowIndex < watchRange.top || rowIndex > watchRange.bottom) return;
if (colIndex < watchRange.left || colIndex > watchRange.right) return;
var currentValue = range.getValue();
currentValue = currentValue.replace(/^\s+|\s+$/g,""); //"trim" kludge
if (currentValue.length != 0){
// Set a column to show last edit date
SpreadsheetApp.getActiveSheet().getRange(DateCol).setValue(curDate);
};
}
The problem is that I don't know how to make it detect the cell that I am modifying and from this detect in which range it is previously selected, I had thought of doing something like the one below, but it doesn't work out for me. The purpose of this is to have a series of controlled ranges, to know when they have been modified, each range has its own cell to write the modification date.
function onEdit(HoraModificacion){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
//Get the cell you are modifying to make a conditional for multiple range detect
var activeCell = sheet.getActiveCell();
//var activeRange = sheet.getActiveRange();
var range1 = sheet.getRange('C11:K109');
if ( activeCell == range1){
var DateCol = "K9";
var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
var rowIndex = range.getRowIndex();
var colIndex = range.getColumnIndex();
// May need to set up multiple ranges like this and check all ranges if
// checked fields are not in adjacent cells
var watchRange = {
top : 11, // start row
bottom : 109, // end row
left : 2, // start col
right : 12, // end col
};
// Exit if we're out of range
if (rowIndex < watchRange.top || rowIndex > watchRange.bottom) return;
if (colIndex < watchRange.left || colIndex > watchRange.right) return;
var currentValue = range.getValue();
currentValue = currentValue.replace(/^\s+|\s+$/g,""); //"trim" kludge
if (currentValue.length != 0){
// Set a column to show last edit date
SpreadsheetApp.getActiveSheet().getRange(DateCol).setValue(curDate);
};
}else if{
//Check the other range
return;
};
}
Based on what Ruben told me, I have modified my script and now I have this:
function onEdit(e){
var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
var colInicio = e.range.getColumn();
var filaInicio = e.range.getRow();
var ref = e.range.getA1Notation(); // Notación tipo A1 o A1:B2
var range = e.getRange();
var colIndex = colInicio.getColIndex();
var rowIndex = filaInicio.getRowIndex();
var watchRange1 = {
top : 11, // start row
bottom : 109, // end row
left : 3, // start col
right : 11, // end col
};
var watchRange2 = {
top : 11, // start row
bottom : 109, // end row
left : 13, // start col
right : 21, // end col
};
if(rowIndex >= watchRange1.top && rowIndex <= watchRange1.bottom &&
colIndex >= watchRange1.left && colIndex <= watchRange1.rigth &&
range.getValue() != 0){
var DateCol1 = "K9";
SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue(curDate);
}else if (rowIndex >= watchRange2.top && rowIndex <= watchRange2.bottom &&
colIndex >= watchRange2.left && colIndex <= watchRange2.rigth &&
range.getValue() != 0){
var DateCol2 = "U9";
SpreadsheetApp.getActiveSheet().getRange(DateCol2).setValue(curDate);
}else{
//if test
var DateCol1 = "K9";
SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue("Fallo");
}
}
When trying to run this script I get this error: Cannot read property "range" of undefined. (line 4, file "Day1")
I'm still pretty stuck with the script
I detect that the problem is before the if, but I can't solve it.
NOTE: Answer corresponding to revision 3 of the question. For the bug introduced in revision 5 of the question
see my second answer .
Short answer
To detect the range being edited use
Expanded answer
The first thing to keep in mind is that you can edit more than one cell at a time.
I find it very unusual to call
HoraModificacion
the function parameteronEdit
or any other name reserved for simple trigger functions and even functions to be called by installable triggers. The usual thing is to useevent
ore
.The parameter of the onEdit function will be assigned an event object whose properties include the range that was edited. The name of this property is
range
. You can check the rest of the properties at https://developers.google.com/apps-script/guides/triggers/events#editIt is worth mentioning that the event object of the "edit" trigger, in addition to the properties mentioned in the documentation, includes
columnStart
androwStart
indicates the column and row respectively of the first cell of the edited rangeFrom revision 5 of the question
The error is due to the fact that you are directly executing the script, that is, without passing an event object, in this case it
e
isundefined
. How can I test a trigger function in Google Apps Script explains how to test simple triggers.Another potential error that I could see with the naked eye occurs in the line
If
e
it were the event object, it would not be an object of type Spreadsheet nor of type Sheet, instead of this line you could useHowever, I don't see much point in saving two characters, but that is already a matter of style.
to detect the cell you can call the event itself using onEdit(e) as referred to in the official help
In this example, it is checked if the edited cell corresponds or is within columns 2 to 6
if you add the "getRow()" method to the second if you could get to verify that it is within the range of rows that you need to evaluate.
I hope it is useful for you