New to JS (Slowly working my way through), but I’ve got an issue on a Google Sheet where I’d like to have a date displayed in a specific Cell (C1), when any cell is edited in a range (C2:C50).

Ideally I’m wanting to apply this to multiple ranges (5 – C2:50) but to treat each range individually so that when an edit occurs within each individual range, it will populate the corresponding ranges first row (C2:C50 > Populate C1 etc).

It would be convenient to have it only accept the first edit and not change the value in C1:G1 on further edits, but I’m not sure it’s a good idea to implement it this way.

I’ve tried modifying the below script, but couldn’t find the right function to select a specific cell rather than an offset from an individual cell in the range.

Thanks in advance to anyone that can help!

/**
* Creates a Date Stamp if a column is edited.
*/

//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 1;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,1];
// Sheet you are working on
var SHEETNAME = 'Sheet1'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
  }
}

>Solution :

I believe your goal is as follows.

• When the cells "C2:C50" are edited, you want to put the value of new Date() to the cell "C1".
• You want to use onEdit simple trigger.

In this case, how about the following modified script?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please edit the cells "C2:C50" of "Sheet1". By this, the script is automatically run by the simple trigger of onEdit. By this, new Date() is put to the cell "C1".

function onEdit(e) {
  var COLUMNTOCHECK = 3;
  var SHEETNAME = 'Sheet1';

  var range = e.range;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != SHEETNAME || range.columnStart != COLUMNTOCHECK || range.rowStart < 2 || range.rowStart > 50) return;
  sheet.getRange("C1").setValue(new Date());
}

Note:

• This script is run by onEdit trigger. So when you directly run this script with the script editor, an error like TypeError: Cannot read property 'range' of undefined occurs. Please be careful about this. When you use this script, please edit the cells "C2:C50" of "Sheet1".

References:

• Simple Triggers
• Event Objects