Value render & input options

How SheetDB reads values from Google Sheets and how it writes them, controlled with the mode parameter.

Value Render Option

You can add the optional mode parameter to your GET requests (read and search). It controls how cell values are returned. This parameter can have the following values:

• FORMATTED_VALUE (default) - Formulas are calculated and every value is returned as text, exactly as it is displayed in the spreadsheet. Formatting follows the spreadsheet's locale, not the locale of the person making the request.
• UNFORMATTED_VALUE - Formulas are calculated, but formatting is not applied. Numbers are returned as JSON numbers, and dates are returned as serial numbers (days since December 30, 1899).
• FORMULA - Formulas are not calculated. Cells with a formula return the formula itself, other cells return their unformatted value.

The value is not case-sensitive. An unknown value falls back to FORMATTED_VALUE.

Example

Let's take a sheet where price is 1.5 formatted as currency, total is the formula =B2*2 (also formatted as currency) and date is March 1, 2024 formatted as M/D/YYYY:

curl https://sheetdb.io/api/v1/58f61be4dda40?mode=FORMATTED_VALUE
curl https://sheetdb.io/api/v1/58f61be4dda40?mode=UNFORMATTED_VALUE
curl https://sheetdb.io/api/v1/58f61be4dda40?mode=FORMULA

[
    {
        "id": "1",
        "price": "$1.50",
        "total": "$3.00",
        "date": "3/1/2024"
    }
]

[
    {
        "id": 1,
        "price": 1.5,
        "total": 3,
        "date": 45352
    }
]

[
    {
        "id": 1,
        "price": 1.5,
        "total": "=B2*2",
        "date": 45352
    }
]

The mode parameter has no effect on the count endpoint.

Value Input Option

You can add an optional mode parameter to your POST and PATCH requests (create and update). It controls whether the values you send are parsed and how. This parameter can have one of two values:

• USER_ENTERED (default) - Values are parsed as if you typed them into the Google Sheets UI: "Mar 1 2024" becomes a date, "=1+2" becomes a formula and "$100.15" becomes a number with currency formatting.
• RAW - Values are not parsed and are saved as text, so "=1+2" is saved as the text =1+2, not as a formula. Numbers and booleans are always saved as they are.

The value is not case-sensitive. Any value other than RAW is treated as USER_ENTERED.

Example

Sending the same row in both modes:

curl -X POST https://sheetdb.io/api/v1/58f61be4dda40?mode=USER_ENTERED \
  -H "Content-Type: application/json" \
  -d '{"data": [{"id": "2", "total": "=1+2", "date": "Mar 1 2024"}]}'

curl -X POST https://sheetdb.io/api/v1/58f61be4dda40?mode=RAW \
  -H "Content-Type: application/json" \
  -d '{"data": [{"id": "3", "total": "=1+2", "date": "Mar 1 2024"}]}'

With USER_ENTERED the total cell contains the formula =1+2 and displays 3, and the date cell contains a real date you can sort and calculate with. With RAW both cells contain plain text: =1+2 and Mar 1 2024.