Google Sheets Error Message Related to Array

App Building
#1

Hi there,

I keep getting this error message: "Request included an invalid value. Supported cell values are string, bool and numbers. If you are appending a cell that contains an array or JSON object make sure to cast it as a string."

This is what I get when I try to push data from an API into a Retool Table and then to a Google Sheet. In my table, I believe the issue is related to some data that comes in like this: [7, 7, 14, 0]. How do I edit this data in Retool so I can successfully push it to Google Sheets.

Thanks

#2

Hey @DSomers16! Would you mind sharing some screenshots of your current query?

#3

Hey @victoria thanks for following up. Thinking the issue is in the home_lin column.

image
image1538×837 84.3 KB

#4

And FWIW, here's the to Google Sheets query

image
image1249×446 25.6 KB

#5

Ah, yup!

I believe we just need to stringify that specific column before passing it along to the append query.

Maybe we can pass in something like this instead of {{table1.data}}:

{{formatDataAsArray(table1.data).map(row => Object.assign(row, {home_lin:JSON.stringify(row.home_lin)}))}}

Alternatively, if your column has some arrays of numbers and some numbers and you only want to stringify the arrays, you can do something like this (it only stringifies arrays):

{{formatDataAsArray(table1.data).map(row => ({...row, home_lin: Array.isArray(row.home_lin) ? JSON.stringify(row.home_lin) : row.home_lin} ))}}

Note that in the first solution I'm using Object.assign and in the second solution I'm using spread syntax. They're both doing the same thing here, I just wanted to present two options :slight_smile:

Both are to make sure we're returning the entire row after modifying (since map would just return the row we modified)

#6

Hey @victoria, thanks for the initial help. I still keep getting that error. Should I be doing it in the Google Sheets query or should have another query doing it and if so what type? JSON/Java/really all of this isn't my strong suit but that's one of the reasons why I came to retool!

Here's how I have it set up.

image
image1237×497 47.7 KB

#7

Ah, so it looks like you put the JS transforming in the query transformer of the append query. Query transformers happen after the query runs, so you actually want to put the transforming in the GET query or directly in the value to append field.

In your current setup, you're still just passing in the regular {{table1.data}} and then doing some transforming, which doesn't really help us.

Also, I noticed you're stringifying two columns so here's the code you might want:

{{formatDataAsArray(table1.data).map(row => Object.assign(row, {home_line_scores:JSON.stringify(row.home_line_scores, away_line_scores:JSON.stringify(row.away_line_scores)}))}}

And you'll be putting that code directly inside your values to append field of your update query (see green arrow)!

You can also remove the code you put in the update query transformer (see red x), since again, that stuff happens after the update query runs and at that point, we don't need to do any transforming :slight_smile:

#8

hey @victoria! Thank you for your help. Got this to work. I did have to remove the "formatDataAsArray" for it to pull in Google Sheets, but I really really appreciate your help here.

1 Like
#9