Pasting strings in Google Sheets prepends ’

I’ve noticed that the Google Sheets node is prefixing a single quote (') to the start of any string. I’ve tried following the instructions provided in this topic: Pasting Date in Google Sheets prepends ', but I’ve encountered an issue when I enable the “Google Sheets formatting” option.

When this option is enabled, cells that contain TRUE or FALSE are not converting to a dropdown selection box, as shown in this image:

image

Could this be a bug, or is there a workaround available to address this?

Hi @lpadula :wave: Sorry you’re running into this!

Could you share an example spreadsheet that doesn’t contain any sensitive data, and your workflow? I’d like to test this.

Can you also let us know the following:

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:

GSheet: n8n GSheet Test - Google Tabellen

Workflow:

  • n8n version: 1.1.1
  • Database (default: SQLite): Postgres
  • n8n EXECUTIONS_PROCESS setting (default: own, main): default
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: Ubuntu Server 22.04
1 Like

Thanks for that @lpadula - how are you sending the data such as {{ $json.body.post.post_date }} to the webhook for use with your sheets? Can you provide an example request you’re sending?

Here is an example:


[
{
"headers":
{
"host":
"n8n.broobe.com",
"x-real-ip":
"85.239.248.222",
"x-forwarded-for":
"85.239.248.222",
"x-forwarded-proto":
"https",
"content-length":
"2467",
"user-agent":
"WP Webhooks/3.3.1 Trigger (WordPress/6.2.2)",
"accept":
"*/*",
"accept-encoding":
"deflate, gzip, br",
"content-type":
"application/json; charset=UTF-8",
"x-wp-webhook-source":
"https://ideatonsalud.ar/",
"x-wp-webhook-name":
"post_create",
"x-wp-webhook-url-name":
"n8n-nuevo-post"
},
"params":
{
},
"query":
{
},
"body":
{
"post_id":
20854,
"post":
{
"ID":
20854,
"post_author":
"480",
"post_date":
"2023-08-04 14:54:03",
"post_date_gmt":
"2023-08-04 17:54:03",
"post_content":
"Amanda es un asistente digital ...",
"post_title":
"Amanda",
"post_excerpt":
"",
"post_status":
"publish",
"comment_status":
"open",
"ping_status":
"closed",
"post_password":
"",
"post_name":
"amanda",
"to_ping":
"",
"pinged":
"",
"post_modified":
"2023-08-04 14:54:03",
"post_modified_gmt":
"2023-08-04 17:54:03",
"post_content_filtered":
"",
"post_parent":
0,
"guid":
"https://ideatonsalud.ar/",
"menu_order":
0,
"post_type":
"project",
"post_mime_type":
"",
"comment_count":
"0",
"filter":
"raw"
},
"post_meta":
{
"rank_math_internal_links_processed":
[
"1"
],
"applicant":
[
"institution"
],
"fullname":
[
""
],
"dni":
[
""
],
"email":
[
""
],
"telephone":
[
""
],
"province":
[
""
],
"localities":
[
""
],
"perfil_de_linkedin":
[
""
],
"business_name":
[
"Amanda"
],
"residence":
[
"Espronceda"
],
"cuit":
[
"---"
],
"representative":
[
"Presidente"
],
"website":
[
"www.amanda.com"
],
"themes_key":
[
"1._digitalización_y_modernización_de_la_investigación_clínica."
],
"terms_and_conditions":
[
""
],
"upload_documentation":
[
"20853"
],
"_wpuf_form_id":
[
"19921"
]
},
"post_thumbnail":
false,
"post_permalink":
"https://ideatonsalud.ar/",
"taxonomies":
[
]
}
}
]

Hey @lpadula,

In your example sheet I can’t see any cell which is defined as a check box but to resolve the issue with the ' character under the options for the node try setting the format to “Let Google Sheets Format” this will then use whatever formatting the cell has defined.

image

1 Like

Hi Jon,

When I send either ‘TRUE’ or ‘FALSE’, it gets converted into a checkbox: checked for ‘TRUE’ and unchecked for ‘FALSE’. However, when I use the “Let Google Sheets format” option, this conversion doesn’t occur. If I send without this option, there’s an issue with the " ’ " character. But if I remove that character directly from the Sheet, everything works fine.

Hey @lpadula,

You will need to use the Let Google decide option and for me I made sure I was sending a bool rather than a string. Have you checked your data type to make sure it is true and not "true"?

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.