Why does getDisplayValue return an empty string for some cells?

getDisplayValue returns an empty string when the cell is blank or when the cell contains a formula that evaluates to an empty string. getValue() on a blank cell returns an empty string too, but on a formula cell that produces 0 it returns the number 0 while getDisplayValue() may return "0" or "" depending on the number format applied.

Can I use getDisplayValues (plural) to read a whole range at once?

Yes. Range.getDisplayValues() returns a 2D array of strings mirroring the range shape, the same way Range.getValues() returns a 2D array of typed values. Prefer the plural batch methods over looping with single-cell calls — each getRange().getValue() inside a loop is a separate Sheets API call and will hit the 6-minute execution limit on large sheets.

Does getDisplayValue reflect conditional formatting colors or just the text?

Text only. getDisplayValue returns the string visible in the cell, not color, font, or any other visual attribute applied by conditional formatting. There is no built-in Apps Script method to read the computed background color after conditional formatting rules are applied.

My date cell shows the right year in the sheet but getValue returns a date from 1899 — what is happening?

Sheets stores dates as serial numbers where 1 = January 1, 1900. If a cell is formatted as a number but contains what looks like a date, getValue() returns that serial number as a plain Number. You get 1899-era dates when you pass that number directly to new Date() without converting it. Either format the cell as a Date in Sheets first, or convert manually: new Date(Date.UTC(1899, 11, 30) + serialNumber * 86400000).

// Sheets · Apps Script

getValue vs getDisplayValue in Google Sheets.

getValue returns a typed JavaScript object (Date, number, boolean); getDisplayValue returns the string you see in the cell. Mixing them up causes silent comparison failures that are hard to debug.

“I want to know which method to call when reading a cell in Apps Script so my comparisons and conditional logic actually work instead of silently returning the wrong result.”

The script

copy · paste · trigger

readCellValue.gs

Apps Script

// getValue vs getDisplayValue — pick by what the value feeds
function compareDates() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // A1 contains a date formatted as "Jun 11, 2026"
  var typed = sheet.getRange('A1').getValue();       // -> Date object
  var rendered = sheet.getRange('A1').getDisplayValue(); // -> "Jun 11, 2026"

  var cutoff = new Date('2026-01-01');

  // Correct: compare two Date objects
  if (typed > cutoff) {
    Logger.log('after cutoff');
  }

  // Silent fail: Date > string always evaluates oddly
  if (rendered > cutoff) {
    Logger.log('this comparison is unreliable');
  }
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

What each method actually returns

getValue() hands you the raw JavaScript value that Sheets stores internally: a Date object for date cells, a Number for numeric cells, a Boolean for checkbox cells, and a String for text. The cell's number format has no effect on what you get back.

getDisplayValue() hands you whatever string appears in the cell after formatting is applied. A cell holding the number 1234567.89 formatted as currency returns "$1,234,567.89". A date cell formatted as "Jun 11, 2026" returns exactly that string, not a Date.

The first time I hit the silent-fail version, I was filtering rows by date and every row passed the condition. The rendered string "Jan 5, 2026" compared against a Date object with > produced true because JavaScript coerces the Date to a number and the string to NaN, and NaN comparisons do not behave the way you expect.

Choosing between them based on what the value feeds

Use getValue() when the value feeds a calculation, a comparison, a sort, or any logic that depends on type. Date arithmetic (getTime(), date math, .after()), numeric sums, boolean gates — all of these require the typed value.

Use getDisplayValue() when the value feeds a string that a human will read: writing to a Doc, building an email body, logging a status label, populating a dropdown for display. If you are constructing a sentence, the formatted string is almost always what you want.

A useful heuristic: if you are about to call a method on the value (getTime(), toFixed(), toString()), use getValue(). If you are about to concatenate it into a string for a human, use getDisplayValue(). If you reach for getDisplayValue() out of habit and then immediately compare it to another value, that is the sign you picked the wrong one.

The timezone trap hiding inside getValue on dates

Apps Script runs in the script's timezone, set under Project Settings. When getValue() returns a Date from a date-only cell (no time component), the Date object is midnight UTC of that day, which in a UTC-5 timezone renders as the previous day. This catches people who are already doing the right thing by using getValue().

The fix is to use Utilities.formatDate() with an explicit timezone when you need a string representation of a typed Date, rather than calling toString() on the Date object or switching to getDisplayValue() to avoid the conversion. Utilities.formatDate(typed, Session.getScriptTimeZone(), 'yyyy-MM-dd') is the safe path.

getDisplayValue() sidesteps this entirely because it returns what Sheets already rendered using the sheet's locale and timezone. That makes it tempting as the easy fix, but it only defers the problem: as soon as you need to parse or compare that string in a different script or a different locale, you are back to square one.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does getDisplayValue return an empty string for some cells?: getDisplayValue returns an empty string when the cell is blank or when the cell contains a formula that evaluates to an empty string. getValue() on a blank cell returns an empty string too, but on a formula cell that produces 0 it returns the number 0 while getDisplayValue() may return "0" or "" depending on the number format applied.
Can I use getDisplayValues (plural) to read a whole range at once?: Yes. Range.getDisplayValues() returns a 2D array of strings mirroring the range shape, the same way Range.getValues() returns a 2D array of typed values. Prefer the plural batch methods over looping with single-cell calls — each getRange().getValue() inside a loop is a separate Sheets API call and will hit the 6-minute execution limit on large sheets.
Does getDisplayValue reflect conditional formatting colors or just the text?: Text only. getDisplayValue returns the string visible in the cell, not color, font, or any other visual attribute applied by conditional formatting. There is no built-in Apps Script method to read the computed background color after conditional formatting rules are applied.
My date cell shows the right year in the sheet but getValue returns a date from 1899 — what is happening?: Sheets stores dates as serial numbers where 1 = January 1, 1900. If a cell is formatted as a number but contains what looks like a date, getValue() returns that serial number as a plain Number. You get 1899-era dates when you pass that number directly to new Date() without converting it. Either format the cell as a Date in Sheets first, or convert manually: new Date(Date.UTC(1899, 11, 30) + serialNumber * 86400000).

// one good script a week