The Sheets API can return a displayed string, an underlying calculated value, or a formula. Choose the render option that matches the receiving program’s purpose.
Distinguish the output modes
FORMATTED_VALUE calculates values and applies cell formatting. A currency cell can therefore return text containing a symbol and localized separators. Formatting follows the spreadsheet’s locale, not the requesting user’s locale.
UNFORMATTED_VALUE returns calculated values without that display formatting. FORMULA returns formula expressions rather than their calculated results for formula cells.
Handle dates and numbers explicitly
Use underlying numeric values when the receiving system needs arithmetic, and preserve units separately. Do not strip arbitrary symbols from display strings as a substitute for selecting a suitable output mode.
Dates and times use numeric representations in Sheets. Review the request’s date-time rendering settings and the file’s time-zone context before interpreting a returned serial as an external timestamp.
Test a representative fixture
Read the same small range in the required modes. Include a currency-formatted number, percentage, date, plain text, and a formula referencing another cell. Compare both value and JSON type.
If the task is auditing formulas, calculated output alone is insufficient. If the task is exporting current results, formula text alone is insufficient. Retain a clear schema for the receiving program and avoid parsing the spreadsheet’s human display as though it were a locale-independent data format. A read can succeed technically while giving the wrong representation for the next processing step, so verify the interpretation before scaling the integration.
References
- ValueRenderOption | Google Sheets | Google for Developers
- Set a spreadsheet’s location & calculation settings
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.