CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheets
AlexShevyakov
AlexShevyakov

Reputation: 423

Using onEdit(e) to update cells when new value is added

A piece of code I am working on consists of two functions - generateID and onEdit(e). generateID - generates a number using pattern and it works fine. onEdit(e) is the one I am having issue with.

In column A of a spreadsheet, a user selects a value from a dropdown (initially column A is empty, range is from row 2 to getLastRow(), row 1 for heading). Once the value is selected, I need the result of the generateID to be inserted into the next column B of the same row. If a value in coumn A is then changed (selected from dropdown), I need generateID to update its value.

Here is what I tried so far:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName("TEMP");
  var range = activeSheet.getRange(2, 1, activeSheet.getLastRow(), 1).getValues(); 

  for (var i =0; i < range.length; i ++) {
    if (e.range [i][0] !== "") {
       activeSheet.getRange(i + 2, 2, 1, 1).setValue(generateID());
    };
  };
};

onEdit seems to be the right choice to watch changes in the range. With (e) the code seems not to work at all. Trying it as onEdit() works but incorrectly. Apprecaite any help on this. Thank you.

Upvotes: 0

Views: 132

Answers (1)

TheMaster
TheMaster

Reputation: 50751

e.range returns a range object and not a array1. Try this instead of your code: 

e.value !=='' ? e.range.offset(0,1).setValue(generateID()) : null

Upvotes: 1

Related Questions