Shortens the data back to 5 characters if you put more that 5 in

Question

I have this code that works for restricting data in all cells to 5 characters. I like that it shortens the data back to 5 characters if you put more that 5 in.

function onEdit(e) {
  var limit = 5;
  if(e.value.length > limit) {
    e.range.setValue(e.value.substring(0, limit));
  }
}

If I click the Run button it does show an error.

TypeError: Cannot read property "value" from undefined. (line 3, file "Code") But it seems to work fine.

I would like to have the script work on a certain range of cells only. Say C3:C100. So I had to reference the sheet and cells, so changed the script to this.

function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("C3:C100");  
var limit = 5;
  if(e.value.length > limit) {
    e.range.setValue(e.value.substring(0, limit));
 }
}

This script does not work on any of the cells, let alone the selected ones.

Answer 1

A onEdit script is triggered by any edit in a spreadsheet. It 'captures' the edit in what is called an event object. The event object can be passed as an argument to the function. In that case you'll see onEdit(e) or onEdit(event) as the function name. That object has some parameters attached to it: e.range (the edited range), e.value (the value of the edit), e.source (the spreadsheet where the edit is done), etc.. Now when you run the function from the script editor (by clicking the play button) no actual edit is taking place in the spreadsheet. Therefore no event object will be created and all parameters of that object will be undefined.

If you want to 'limit' the scope of the script to the range C3:C100, you can change the script as follows:

function onEdit(e) {
var limit = 5;
if (e.value.length > limit && e.range.rowStart > 2 && e.range.rowStart < 101 && e.range.columnStart == 3) {
    e.range.setValue(e.value.substring(0, limit));
}
}

Note that currently the script will work on every tab/sheet inside your spreadsheet/workbook. If you want to further limit the script to work on only one sheet (e.g. 'Sheet1'), you can try something like

function onEdit(e) {
var limit = 5;
if (e.source.getActiveSheet().getName() == 'Sheet1' && e.value.length > limit && e.range.rowStart > 2 && e.range.rowStart < 101 && e.range.columnStart == 3) {
    e.range.setValue(e.value.substring(0, limit));
}
}