Google Sheets single quote

When adding/updating cells in Google Spreadsheet, a single quote (') was prefixed to the value.

‘2022-05-01

This can be fixed by changing the valueInputOption to USER_ENTERED. There’re two options:

  • RAW: The values the user has entered will not be parsed and will be stored as-is.
  • USER_ENTERED: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc.

If you’re using https://github.com/burnash/gspread, change the value_input_option.

  • https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
  • https://github.com/burnash/gspread/issues/524

Was this page helpful?

Yes No

If you introduce any text around single quote, the error go away. Example, "’ " (space behind the single quote) However, I just need a single quote.

Is it a bug?

Some background. When you add a phone number with a prefix “+” sign like +1234545 via typing in a column, AppSheet will automatically add a single quote prefix in the google sheet cell. However, if you are setting the value via an action, it will not add the single quote. That lead to Google Sheet treating the cell as a formula.

How do you type a single quote?

On the keyboard You can make single quotation marks on most computers by pressing the apostrophe/quotation mark key to the left of ENTER. Double quotation marks are made on most computers by holding SHIFT and pressing the apostrophe/quotation mark key to the left of ENTER.

How do you pass a single quote in a string?

A single quote is not used where there is already a quoted string. So you can overcome this issue by using a backslash following the single quote. Here the backslash and a quote are used in the “don't” word.

How do I put single quotes in an Excel cell?

To add single quotes in Microsoft Excel, select the cell where you need the single quotes, type the formula =”'”&A1&”'” into the Formula bar, and tap Enter.