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());
      }
  }
}

CodePudding user response：

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

Added:

About the following new question,

If I wanted to apply this to several columns, how would I do this?

The sample script is as follows.

Sample script:

Please set the column numbers you want to use to var COLUMNTOCHECK = [3, 4, 5];.

function onEdit(e) {
  var COLUMNTOCHECK = [3, 4, 5]; // 3, 4, 5 are corresponding to columns "C", "D" and "E".
  var SHEETNAME = 'Sheet1';

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

• In this case, the value of new Date() is put to the 1st row of each column. If you always want to put it to the cell "C1", please modify getRange(1, col) to getRange("C1").