CODEWITHSUNDEEP
google-sheets
Jason
Jason

Reputation: 3

Google Sheets - Add note on edit when user edits only a portion of the sheet

This may be a simple answer, I'm very new to the script editor in google sheets.

I have about 8 sheets for individual users to enter data. I would like a note showing the last modified date on a range of cells in each of the 8 sheets. Each of the 8 sheets is identical in the layout. With the code below, I'm able to get a last modified date note on column 2, row 26, and it works great. However, I can't figure out how to expand that to include columns 2-7 and rows 26-35. I assumed just entering 2:7 under range.getRow() would work, but it does not.

Any suggestions on how to achieve this? Even better, ideally having just one comment note in a single cell on the sheet(L24 as an example), that shows the last modified date of any cell edited between columns 2-8 and rows 26-35?

My goal is really just to identify the last time a user has modified any cells within that specified range.

Any suggestions would be greatly appreciated. The script is below. 

    /**
 * The event handler triggered when editing the spreadsheet.
 * @param {Event} e The onEdit event.
 */
function onEdit(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  var range = e.range;
  if(range.getRow() == 26 && range.getColumn() == 2){
        range.setNote('ROW: ' + range.getRow() + " COLUMN: "+ e.range.getColumn());
  range.setNote('Last modified: ' + new Date());
  }}

Upvotes: 0

Views: 605

Answers (1)

Tedinoz
Tedinoz

Reputation: 8044

The OP wants to create a note in cell L24 on any sheet where the edited cell is in the range B26:H35 (columns 2 to 8 inclusive).

The OP's own code could create a note in the edited cell but was only configured for edits in the range "B26". However, most of the logic was in place, and only modest changes are required.

The points to note in this answer are:

  • the Note is entered in cell "L24" of the edited sheet; this is made easier by defining a variable noterange.
  • the row and column numbers for the edited range are assigned to variables; this makes it easier to use when building the IF statement.
  • the edited range is assigned to a variable; this simplifies displaying the cell reference in the note.
  • the IF statement is in two parts: it tests whether i) the edited row is between 26 and 35 AND ii) the edited column is between 2 and 8.
  • the cell reference in the note is simplified by using getA1Notation.
  • I left the date as used by the OP, but this can be shortened/simplified
  • I inserted a line break between the range reference and "Date Modified" for easier reading
  • I created an Installable onEdit Trigger 
function so54658181(e) {

  // setup spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get the active sheet
  var eactiveSheet = e.source.getActiveSheet();

  // set the location for the Note
  var noterange = eactiveSheet.getRange("L24");

  // get the edited cell and find the row and column numbers
  var erange = e.range;
  var editedRow = erange.getRow();
  var editedColumn = erange.getColumn()

  // if the row is between 26 and 35, and the column is between 2 and 8, then set the note
  if ((editedRow >= 26 && editedRow <= 35) && (editedColumn >= 2 && editedColumn <= 8)) {
     noterange.setNote("Cell " + erange.getA1Notation() + String.fromCharCode(10) + "Last modified: " + new Date());
  }

}

Formatting the Date

  • Add these two line anywhere above the IF statement
    // set date format var curDate = Utilities.formatDate(new Date(), "GMT+1", "E, MMM dd yyyy");

  • Edit the following line of the IF statement; note that it now references curDate noterange.setNote("Latest edit: Cell " + erange.getA1Notation() + String.fromCharCode(10) + "On: " + curDate);

  • A link to the most recent Date and Time Patterns is included in the Google documentation.

Upvotes: 0

Related Questions