Parsing rows from Sheets to JSON

Hey,

I have a scenario that takes data from spreadsheet rows and uses it to send an HTTP request (JSON).

As you can see in the image, sometimes the data isn’t perfect for JSON. For example, one of the values has double quotes (").

I know there’s a replace function, so the “stupidest” solution would simple be to wrap each of the green values in the above screenshot with a replace function. But that seems tedious, as there are many such values, and more than one characters to replace.

I’m sure there’s some smart way to first get the row data through a parser that replaces all “bad” characters for JSON, then continues to the JSON module as shown.

Any help would be greatly appreciated!

Use Create a JSON Module instead of Parse JSON.

So, basically what you want to do is Use Create a JSON module, over there define the datastructue of the JSON, you can use the generate option while adding data structure in it.

After which, instead of mapping it as a a JSON, just map it against the datastructure which will allow you to escape characters breaking your JSON.

3 Likes

Unfortunately I’m not sure I get it. I used “create JSON” instead of “parse JSON”, and created a datastructure, but that ended up giving me only a “json stringified” string for sending the HTML request. This is less convenient as I don’t have the separate fields now…

I am not sure if I get what you want to do, don’t you want to create a JSON that you then use it in HTTP module to send to the API you are consuming?

1 Like

Yes and it does work now, so first of all thanks!
I just thought I’d have access to the JSON object fields later on, but I only have access to the original Sheet fields and to one blob of “JSON string”. If I later on need access to the JSON object values, is there a way to access them? Thanks again.

You can use the parse JSON module later(add pass JSON String over there) if you need to access the JSON values.

2 Likes