How to convert JSON in Google Sheets to Target Fields

Need help
1

Hi,

I have built a flow adding some columns from Google sheets to a target API. Now, my Google sheets has some extra fields in one column in JSON, it it seems I can’t access those fields in the normal mapping, the data selector only offers the sheets columns. Any suggestion how to convert the normal columns in Google sheets + the JSON in the google sheets column, to an overall list of parameters?

2

@Harald_Reitz Welcome to the community :wave:,

You can use the Convert Text to JSON action for those extra fields. Can you share how the data for those fields looks in the Google Sheets step output?

3

Column L of Goole Sheets has this JSON:

L:

“[{“name”:“email”,“values”:[“mail@gmail.com”]},{“name”:“first_name”,“values”:[“Firstname”]},{“name”:“last_name”,“values”:[“Lastname”]},{“name”:“city”,“values”:[“City”]},{“name”:“state”,“values”:[“State”]},{“name”:“job_title”,“values”:[“Job”]}]”

4

Thanks, see JSON in one Google Sheets Cell. I saw the JSON conversion functions, but dont know how to pick up the Sheets columns A to K directly, and then extract the JSON in column L into additional fields. Was assuming I have to convert JSON to XML in between Sheets and my target API, but not sure how …

Solution may be obsolete, since I just found Facebook leads does now integrate to Sheets directly, and all fields are then directly in Sheets columns. The current sheets is from IFTTT, and that puts custom leads fields in such a Json in sheets …

5

You can use the Get Row action from the Google Sheets piece to retrieve specific row values from columns A to K. Then, you can map the column L value in the next step. Does that help?

6

Thanks, I can have a look - overall issue is, how can I move the other columns into some field list, and then process the JSON value into the addl fields and values in the same list, then send that into my target API. Know the tools to little to know how to approach that.

7

For example, can I split the sheets input into two processes, pick the JSON and convert to. XML, then send fields from that into an extended target field list? Not sure if that can be achieved with modeling only ….