A cell with Data Validation (Drop Down) has a value `undefined`. Why?

user_15215194 0
Viewed 79

I'm using Google Spreadsheets with Google Apps Script and I want to get the value of the edited cell which has a drop down list. I debugged using onEdit(e) and message box and e.range.value.

Browser.msgBox(`new value：${e.range.value},old value： ${e.range.old}`);

The result is

new value：undefined,old value： undefined

The value of the drop down cell was undefined even if the content is selected. Does that mean a cell with drop down list has no value even if the value can be seen in the formula box? Maybe it is the specification of Data Validation or Criteria but I couldn't understand the reference of Google Apps Script. Is it possible to let the drop down list cell have the value which is written in the formula box?

Below is the full code of onEdit function:

function onEdit(e) {
      Browser.msgBox("おんえでぃっとだ");
      const sheet =  e.source.getActiveSheet();
      const row = e.range.getRow();//数字
      const column = e.range.getColumn();//アルファベット
      Browser.msgBox(`行列は取得した:(${row},${column})`);
      
      const rule = e.range.getDataValidation();
      const criteria = (rule == null) ? null:rule.getCriteriaType();
      Browser.msgBox(`るーるとくらいてりあもいけた。るーる：${rule}くらいていりあ： ${criteria}`);
      Browser.msgBox(`new value：${e.range.value},old value： ${e.range.old}`);
}
google-apps-script google-sheets-api google-sheets
2 Answers

Here is the official documentation of the properties of the event object:

  • To get the new value: e.range.getValue() or e.value
  • To get the old value: e.oldValue

Solution:

Browser.msgBox(`new value：${e.value},old value： ${e.oldValue}`);

You can also print the parameters of the event object yourself:

Browser.msgBox(JSON.stringify(e));

enter image description here

user_112252910

You can refer to the old value and current value of an edited cell on an onEdit(e) trigger using e.oldValue and e.value respectively:

Sample Sheet:

enter image description here

Sample Code, using a dropdown box:

function onEdit(e) {
  console.log(e.oldValue);
  console.log(e.value);
}

Sample Output:

enter image description here

Reference:

https://developers.google.com/apps-script/guides/triggers/events

user_146060490
Related