Nested Data in JSON String

[DSomers16]

Hi there,

So I have this app, which pulls data into Google sheets. However, most of the results, there is a nested set of data in the column. (See venue). Is it possible for me to split that out similarly so that each of those gets separated into their own column once it's pulled into Google Sheets?

[TabraizAhmed]

Hi @DSomers16, Yes, you can use multiple approaches to resolve this.

1. Create a custom column. Use the selected row property to populate it with desired data.
2. Use a JS transformer to flatten this data per your requirements.

[marknoel]

Try something like the following as a transformer in your query. You'd want to change lines like "newData.user_id = x.user.id" to something like "newData.venue_name = x.venue.name" based on the example you gave.

const flattenData = (x) => {
    let newData = {}
    // Examples of top-level stuff in JSON
    newData.id = x.id
    newData.date = x.date

    // Examples of nested stuff in JSON
    newData.user_id = x.user.id
    newData.user_name = x.user.name
    // Example of splitting a string while I'm already here
    var fullName = x.user.name.split(' ')
    newData.first_name = fullName[0]
    newData.last_name = fullName[fullName.length - 1]
  
    return newData
  }
  const results = data.results.map(a => flattenData(a))
  const newData = ({}, {search: {total: data.results.total, results: results}})

  return newData

[DSomers16]

Thank you. This did the trick!

[Tess]

Thanks for the solution!