API Connector Documentation
Flatten Fields to Columns
Contents
Example Data Structure
You may encounter an inconvenient pattern where key-value pairs are listed within a parent header, like this:
In this example, we have multiple keys like "Store name" and "Order number" grouped together under attributes.name, which can make the data hard to work with. Our desired output is to pull out each attribute key and give it its own column, like this:
How to Flatten Data
API Connector provides an option to automatically extract these keys and convert them into headers.
Facebook Ads actions
The Facebook Ads /insights endpoint returns "actions" (aka conversions or results) in an unflattened list. Selecting Facebook Ads actions will automatically flatten these actions into separate columns. This option is automatically selected for any requests that connect to Facebook Ads via OAuth.
Facebook Pages data
The Facebook Pages /insights endpoint returns page metrics in an unflattened list. Selecting Facebook Pages data will automatically flatten these page metrics into separate columns. This option is automatically selected for any requests that connect to Facebook Pages via OAuth.
Custom
The custom option enables flattening any data set through the use of two configuration settings: Path to header and Path to value. In Path to header, enter the field that should be converted into headers. In Path to value, enter the location of the associated value.
Flatten array option
The custom option contains an additional option called "Flatten array". This is to provide further customizability. For example, consider these two sample JSON snippets:
Example #1
{"data":[{"itemId":"1","value":"Accessories","itemAttributeName":"Shopify Product Type"},{"itemId":"2","value":"Furniture","itemAttributeName":"Shopify Product Type"},{"itemId":"3","value":"Toys","itemAttributeName":"Shopify Product Type"}]}
Example #2
{"Ratings":[{"Source":"Internet Movie Database","Value":"7.4/10"},{"Source":"Rotten Tomatoes","Value":"90%"},{"Source":"Metacritic","Value":"69/100"}]}
While the data structure is virtually the same for each, in the first example the user would most likely prefer to see each item ID in its own row, while in the second example, a user would most likely prefer to see all ratings in a single row. The "Flatten array" checkbox allows you to toggle between these two output choices.
Notes
- The path values should be the generic path without numeric arrays, e.g. enter
attributes.nameand notattributes.1.name. - The number of levels need to match, e.g. you can't use
attributes.namewithattributes.value.count
Hi Ana/team!
I couldn't be happier to have found this API connector! However, there's one thing is intriguing me 🙁
I have the following section on my JSON file (OMDB API) and the Flatten Fields option is working, but is retrieving the very last value (in this example: 'Metacritic')...Since I already have columns such as imdb and metascore , I would need to transform and display the name/value for 'Rotten Tomatoes' (2nd in the array).
PS: Path to header/value are Ratings.name & Ratings.value, respectively. THANK YOU VERY MUCH IN ADVANCE, you'd made my day, I have had no success. 🙁
Thanks! Marcos
(have tried this approach here, too: https://mixedanalytics.com/knowledge-base/filter-specific-fields-values/ but no success either)
...
"Ratings": [{
"Source": "Internet Movie Database",
"Value": "6.2/10"
},
{
"Source": "Rotten Tomatoes",
"Value": "63%"
},
{
"Source": "Metacritic",
"Value": "59/100"
}
]
...
Hey Marcos, based on that snippet the path to header should be Ratings.Source, not Ratings.Name, and the path to value should be Ratings.Value, not Ratings.value (it's case-sensitive). Can you please check if that resolves the issue?
Hello Ana!
First off, I want to thank you again for creating such a phenomenal API program. Your work has saved me - quite literally - HUNDREDS of hours of work (not to mention my sanity). And your knowledge base website is fantastic. I've learned so much and I can find an answer for everything here.
My question: regarding flattening fields, is it possible to set up two different flattening parameters in the same API request call? TBH I don't even know whether this is even programmatically possible in G-Sheets in general. If it is possible, is there a way to use this function within your amazing application?
I use the flatten function in your app regularly and it works beautifully; but I now have some extra column data in my GET calls, and specifying two different flattening values would greatly improve the readability.
I experimented with two formats for the flatten fields params:
i. path to name/path to value separated by a "," comma
ii. same but separated with a "|" pipe.
Neither format was successful. I assume the multiple values were just "cancelled out", because everything came back normally as if I did not specify any flattening.
For reference:
PARAM 1
[Path to header]
Products.DescriptiveElementGroup.DescriptiveElements.Name[Path to value]
Products.DescriptiveElementGroup.DescriptiveElements.DisplayValuePARAM 2
[Path to header]
Products.Specifications.Name[Path to value]
Products.Specifications.ValueEntered as:
Products.DescriptiveElementGroup.DescriptiveElements.Name | Products.Specifications.Name+
Products.DescriptiveElementGroup.DescriptiveElements.Value | Products.Specifications.Value------
Each of the Descriptive.Elements.Name/Value and Products.Specifications.Name/Value work perfectly when used alone in the flattening.
Thank you so much for all the amazing work you do!
-Julia
Thank you Julia, I really appreciate your support! Unfortunately it is currently NOT possible to set up two different flattening parameters in the same API request call. This is not due to any limitation in Sheets, it's just not something we've implemented support for yet. So I'll add that to our to-do list, but in the meantime you should be able to achieve the same results through a JMESPath expression. JMESPath is more complex but also extremely flexible in what it can achieve. If you'd like to send a message via support I'll be happy to help you create the correct expression.
Hi Ana, thank you for your quick response! That makes sense. I've experimented with JMESPath expressions in Postman and it looks like it would be a great solution using your application. I'm glad you recommended it. I really appreciate you offering to help out with the expression formatting. I'll reach out via support message if I can't figure it out correctly. (I'm a goldsmith by trade and a self-taught n00b dev-wannabe, so you'll probably hear from me soon, lol).
🙂
Thanks for the amazing customer service (again!).
Can you use this custom flatten option for more than one field? can you show an example of how this should be done?
Currently you can only apply the flatten option to a single field at a time. To flatten multiple fields, you can re-construct the data with a JMESPath expression. That article has an example of how it works, or feel free to reach out to support for more specific help.