Problem with getDisplayValue Returning an Object?

Question

The big picture problem is I am adding a date to cell one week from today when an adjacent cell is edited. So far so good. However, it needs to not change when the irl date changes, or if the adjacent cell is edited a second time.

Basically been trying to check and see if there is already a date in the cell, and if so, simply returning. But it isn't doing the string comparison properly for some reason. It never recognizes it as an empty string. Then I try to use includes(), it tells me that includes() cannot be used on an object. My understanding is getDisplayValue() should always return a string? A bit at a loss.

function getDate(progress) {
 //progress: cell showing progress state (Not started, begun, completed)

  var range = SpreadsheetApp.getActiveRange();
  var d = range.getDisplayValue();

  if(progress == 'Completed') { return 'done'; } 
  if(d != '') { return; } //if due date cell already has a date, don't change it
  today = new Date();
  due = new Date();
  due.setDate(today.getDate() + 7); //one week from today
  return due;
}

Any idea what the issue is with this code? Or open to another solution if there is something more elegant.

Try Logger.log({d:d,type:typeOf d})

TheMaster
– TheMaster

2019-10-30 03:57:19 +00:00
Commented Oct 30, 2019 at 3:57 — TheMaster
– TheMaster, Commented Oct 30, 2019 at 3:57

Tanaike · Accepted Answer · 2019-10-30 00:10:32Z

1

You want to run the script when the value of the active range is the date object.
You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification point:

In order to confirm whether the value of the active range is the date object, I used getValue() for retrieving the value and Object.prototype.toString.call(d).slice(8, -1) == 'Date' for the if statement.
- getDisplayValue() returns the string value even when the value of cell is the date object. So in this case, I used getValue() for confirming whether it is the date object.

Modified script:

function getDate(progress) {
  Utilities.sleep(5000); // <--- Added

 //progress: cell showing progress state (Not started, begun, completed)

  var range = SpreadsheetApp.getActiveRange();
  var d = range.getValue(); // Modified

  if(progress == 'Completed') { return 'done'; } 
  if (d && Object.prototype.toString.call(d).slice(8, -1) == 'Date') { return; } // Modified
  today = new Date();
  due = new Date();
  due.setDate(today.getDate() + 7); //one week from today
  return due;
}

When the function of getDate is run, if the value retrieved by getValue() is the date object, Object.prototype.toString.call(d).slice(8, -1) == 'Date' returns true. So the script below the if statement is not run.

References:

If I misunderstood your question and this was not the result you want, I apologize.

edited Oct 30, 2019 at 0:10

answered Oct 29, 2019 at 22:05

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

JRHB Over a year ago

This seems really close, but what's happening now is that if I edit a cell, prompting a "Loading..." message, I believe it's actually passing in that string instead of the original content of the cell.

Tanaike Over a year ago

@JRHB Thank you for replying. I apologize for the inconvenience. would like to confirm whether my understanding for your situation is correct. In your situation, getDate is run by the OnEdit event trigger. When the active range is edited, Loading... is displayed in the cell. Under this situation, the function of getDate is run. By this, d of var d = range.getValue(); returns Loading.... Is my understanding correct?

JRHB Over a year ago

Yep, that's the issue.

Tanaike Over a year ago

@JRHB Thank you for replying. Unfortunately, I couldn't replicate your situation. I could confirm that even when the active cell shows Loading... for 10 seconds, var d = range.getValue(); of the script which was run by the OnEdit event trigger returns the calculated value which is not Loading.... So although I'm not sure whether this is the direct solution, I updated my answer. Could you please confirm it? Utilities.sleep(5000); is put to the top of function. I'm not sure whether 5000 is suitable. So please modify this. If that was not the result you want, I apologize.

Tanaike Over a year ago

@TheMaster Thank you for your comment. Yes. instanceOf can be also used. In this case, instanceOf can be simpler modification.

|

Collectives™ on Stack Overflow

Problem with getDisplayValue Returning an Object?

1 Answer 1

Modification point:

Modified script:

References:

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification point:

Modified script:

References:

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related