Trouble getting data from deep nested array

[Mike_Hughes]

Total make newby here so if this was answered somewhere please just send me in that direction.I have a webhook running that brings me all the data I want to export but when I go to move it into google sheets the 3 main pieces of data do not move over.

You can see here there’s a section for protein, fats and carbs but when I connect google sheets only the fats data shows up

I am not looking to map anything fance just taking the raw data and getting it into a simple google sheets table

Here is the scenario. Im assuming I need to use an iterator or set variables but I’m struggling to map it all to pull out the info I would need.

[samliew]

Welcome to the Make community!

Yes, that is possible. You’ll need a minimum of two modules:

1. Iterate any metric’s data array

2. Use map and get functions to map each date’s data

Give it a go and let us know if you have any issues!

Output

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[samliew]

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
   

2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

3. Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

View Module Export Code

JSON

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 5,
                    "module": "json:ParseJSON",
                    "version": 1,
                    "parameters": {
                        "type": ""
                    },
                    "mapper": {
                        "json": "{\n  \"data\": {\n    \"metrics\": [\n      {\n        \"data\": [\n          {\n            \"qty\": 231.3304761904762,\n            \"date\": \"2024-06-20 00:00:00 -0400\",\n            \"source\": \"MacrosFirst\"\n          },\n          {\n            \"date\": \"2024-06-21 00:00:00 -0400\",\n            \"qty\": 289.82608695652175,\n            \"source\": \"MacrosFirst\"\n          },\n          {\n            \"source\": \"MacrosFirst\",\n            \"qty\": 78,\n            \"date\": \"2024-06-22 00:00:00 -0400\"\n          },\n          {\n            \"date\": \"2024-06-24 00:00:00 -0400\",\n            \"source\": \"MacrosFirst\",\n            \"qty\": 169.31293595263725\n          }\n        ],\n        \"units\": \"g\",\n        \"name\": \"carbohydrates\"\n      },\n      {\n        \"units\": \"g\",\n        \"name\": \"protein\",\n        \"data\": [\n          {\n            \"date\": \"2024-06-20 00:00:00 -0400\",\n            \"qty\": 132.2357142857143,\n            \"source\": \"MacrosFirst\"\n          },\n          {\n            \"qty\": 106.17391304347827,\n            \"source\": \"MacrosFirst\",\n            \"date\": \"2024-06-21 00:00:00 -0400\"\n          },\n          {\n            \"qty\": 56,\n            \"source\": \"MacrosFirst\",\n            \"date\": \"2024-06-22 00:00:00 -0400\"\n          },\n          {\n            \"qty\": 185.9289451022605,\n            \"date\": \"2024-06-24 00:00:00 -0400\",\n            \"source\": \"MacrosFirst\"\n          }\n        ]\n      },\n      {\n        \"data\": [\n          {\n            \"date\": \"2024-06-20 00:00:00 -0400\",\n            \"qty\": 52.56380952380952,\n            \"source\": \"MacrosFirst\"\n          },\n          {\n            \"qty\": 79.21739130434783,\n            \"date\": \"2024-06-21 00:00:00 -0400\",\n            \"source\": \"MacrosFirst\"\n          },\n          {\n            \"date\": \"2024-06-22 00:00:00 -0400\",\n            \"source\": \"MacrosFirst\",\n            \"qty\": 8\n          },\n          {\n            \"source\": \"MacrosFirst\",\n            \"date\": \"2024-06-24 00:00:00 -0400\",\n            \"qty\": 56.82576157158235\n          }\n        ],\n        \"units\": \"g\",\n        \"name\": \"total_fat\"\n      }\n    ]\n  }\n}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1,
                            "y": -265
                        },
                        "restore": {
                            "parameters": {
                                "type": {
                                    "label": "Choose a data structure"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "type",
                                "type": "udt",
                                "label": "Data structure"
                            }
                        ],
                        "expect": [
                            {
                                "name": "json",
                                "type": "text",
                                "label": "JSON string",
                                "required": true
                            }
                        ]
                    }
                },
                {
                    "id": 28,
                    "module": "builtin:BasicFeeder",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "array": "{{5.data.metrics[].data}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 245,
                            "y": -262,
                            "name": "Each Day"
                        },
                        "restore": {
                            "expect": {
                                "array": {
                                    "mode": "edit"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "array",
                                "type": "array",
                                "label": "Array",
                                "mode": "edit",
                                "spec": []
                            }
                        ]
                    }
                },
                {
                    "id": 27,
                    "module": "google-sheets:addRow",
                    "version": 2,
                    "parameters": {
                        "__IMTCONN__": 95013
                    },
                    "mapper": {
                        "mode": "fromAll",
                        "insertUnformatted": false,
                        "valueInputOption": "USER_ENTERED",
                        "insertDataOption": "INSERT_ROWS",
                        "spreadsheetId": "1G4oYDWJLUT2KhsC4TbFp7Owh_dyG_-0NQOEk4gcMVNU",
                        "sheetId": "Sheet1",
                        "includesHeaders": true,
                        "values": {
                            "0": "{{28.date}}",
                            "1": "{{get(map(first(map(5.data.metrics; \"data\"; \"name\"; \"protein\")); \"qty\"); 28.`__IMTINDEX__`)}}",
                            "2": "{{get(map(first(map(5.data.metrics; \"data\"; \"name\"; \"total_fat\")); \"qty\"); 28.`__IMTINDEX__`)}}",
                            "3": "{{get(map(first(map(5.data.metrics; \"data\"; \"name\"; \"carbohydrates\")); \"qty\"); 28.`__IMTINDEX__`)}}"
                        }
                    },
                    "metadata": {
                        "designer": {
                            "x": 487,
                            "y": -263
                        },
                        "restore": {
                            "parameters": {
                                "__IMTCONN__": {
                                    "collapsed": true,
                                    "label": "Google Docs",
                                    "data": {
                                        "scoped": "true",
                                        "connection": "google"
                                    }
                                }
                            },
                            "expect": {
                                "mode": {
                                    "label": "Select from all"
                                },
                                "insertUnformatted": {
                                    "mode": "chose"
                                },
                                "valueInputOption": {
                                    "mode": "chose",
                                    "label": "User entered"
                                },
                                "insertDataOption": {
                                    "mode": "chose",
                                    "label": "Insert rows"
                                },
                                "sheetId": {
                                    "mode": "chose",
                                    "label": "Sheet1"
                                },
                                "includesHeaders": {
                                    "nested": [
                                        {
                                            "name": "values",
                                            "label": "Values",
                                            "type": "collection",
                                            "spec": [
                                                {
                                                    "name": "0",
                                                    "label": "date (A)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "1",
                                                    "label": "protein (B)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "2",
                                                    "label": "fats (C)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "3",
                                                    "label": "carbs (D)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "4",
                                                    "label": "(E)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "5",
                                                    "label": "(F)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "6",
                                                    "label": "(G)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "7",
                                                    "label": "(H)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "8",
                                                    "label": "(I)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "9",
                                                    "label": "(J)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "10",
                                                    "label": "(K)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "11",
                                                    "label": "(L)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "12",
                                                    "label": "(M)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "13",
                                                    "label": "(N)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "14",
                                                    "label": "(O)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "15",
                                                    "label": "(P)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "16",
                                                    "label": "(Q)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "17",
                                                    "label": "(R)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "18",
                                                    "label": "(S)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "19",
                                                    "label": "(T)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "20",
                                                    "label": "(U)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "21",
                                                    "label": "(V)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "22",
                                                    "label": "(W)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "23",
                                                    "label": "(X)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "24",
                                                    "label": "(Y)",
                                                    "type": "text"
                                                },
                                                {
                                                    "name": "25",
                                                    "label": "(Z)",
                                                    "type": "text"
                                                }
                                            ]
                                        }
                                    ],
                                    "label": "Yes"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "__IMTCONN__",
                                "type": "account:google",
                                "label": "Connection",
                                "required": true
                            }
                        ],
                        "expect": [
                            {
                                "name": "mode",
                                "type": "select",
                                "label": "Choose a Method",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "select",
                                        "fromAll",
                                        "map"
                                    ]
                                }
                            },
                            {
                                "name": "insertUnformatted",
                                "type": "boolean",
                                "label": "Unformatted",
                                "required": true
                            },
                            {
                                "name": "valueInputOption",
                                "type": "select",
                                "label": "Value input option",
                                "validate": {
                                    "enum": [
                                        "USER_ENTERED",
                                        "RAW"
                                    ]
                                }
                            },
                            {
                                "name": "insertDataOption",
                                "type": "select",
                                "label": "Insert data option",
                                "validate": {
                                    "enum": [
                                        "INSERT_ROWS",
                                        "OVERWRITE"
                                    ]
                                }
                            },
                            {
                                "name": "spreadsheetId",
                                "type": "text",
                                "label": "Spreadsheet ID",
                                "required": true
                            },
                            {
                                "name": "sheetId",
                                "type": "select",
                                "label": "Sheet Name",
                                "required": true
                            },
                            {
                                "name": "includesHeaders",
                                "type": "select",
                                "label": "Table contains headers",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        true,
                                        false
                                    ]
                                }
                            },
                            {
                                "name": "values",
                                "type": "collection",
                                "label": "Values",
                                "spec": [
                                    {
                                        "name": "0",
                                        "type": "text",
                                        "label": "date (A)"
                                    },
                                    {
                                        "name": "1",
                                        "type": "text",
                                        "label": "protein (B)"
                                    },
                                    {
                                        "name": "2",
                                        "type": "text",
                                        "label": "fats (C)"
                                    },
                                    {
                                        "name": "3",
                                        "type": "text",
                                        "label": "carbs (D)"
                                    },
                                    {
                                        "name": "4",
                                        "type": "text",
                                        "label": "(E)"
                                    },
                                    {
                                        "name": "5",
                                        "type": "text",
                                        "label": "(F)"
                                    },
                                    {
                                        "name": "6",
                                        "type": "text",
                                        "label": "(G)"
                                    },
                                    {
                                        "name": "7",
                                        "type": "text",
                                        "label": "(H)"
                                    },
                                    {
                                        "name": "8",
                                        "type": "text",
                                        "label": "(I)"
                                    },
                                    {
                                        "name": "9",
                                        "type": "text",
                                        "label": "(J)"
                                    },
                                    {
                                        "name": "10",
                                        "type": "text",
                                        "label": "(K)"
                                    },
                                    {
                                        "name": "11",
                                        "type": "text",
                                        "label": "(L)"
                                    },
                                    {
                                        "name": "12",
                                        "type": "text",
                                        "label": "(M)"
                                    },
                                    {
                                        "name": "13",
                                        "type": "text",
                                        "label": "(N)"
                                    },
                                    {
                                        "name": "14",
                                        "type": "text",
                                        "label": "(O)"
                                    },
                                    {
                                        "name": "15",
                                        "type": "text",
                                        "label": "(P)"
                                    },
                                    {
                                        "name": "16",
                                        "type": "text",
                                        "label": "(Q)"
                                    },
                                    {
                                        "name": "17",
                                        "type": "text",
                                        "label": "(R)"
                                    },
                                    {
                                        "name": "18",
                                        "type": "text",
                                        "label": "(S)"
                                    },
                                    {
                                        "name": "19",
                                        "type": "text",
                                        "label": "(T)"
                                    },
                                    {
                                        "name": "20",
                                        "type": "text",
                                        "label": "(U)"
                                    },
                                    {
                                        "name": "21",
                                        "type": "text",
                                        "label": "(V)"
                                    },
                                    {
                                        "name": "22",
                                        "type": "text",
                                        "label": "(W)"
                                    },
                                    {
                                        "name": "23",
                                        "type": "text",
                                        "label": "(X)"
                                    },
                                    {
                                        "name": "24",
                                        "type": "text",
                                        "label": "(Y)"
                                    },
                                    {
                                        "name": "25",
                                        "type": "text",
                                        "label": "(Z)"
                                    }
                                ]
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}

Here are some useful links and guides you can use to learn more on how to use the Make platform, apps, and app modules. I found these useful when I was learning Make, and hope they might benefit you too —

General

• Help Center | Tutorials
• Make Academy – learn Make and get your certificate
• Discord Server – join us in live chat for fans of Make!

Help Center Basics

• Mapping – What is mapping? What can I map?
• Mapping with arrays – How to map items in an array
• Aggregate an array for mapping complex fields
• Tokens for parseDate | Tokens for formatDate
• HTTP modules – Make a request, Get (download) a file
• Webhooks – Error Handling, Responding to webhooks

Articles & Videos

• Router Magic Formula - YouTube
• Error Handlers in Make - YouTube playlist
• Getting started with OpenAI
• How to use Regex in Make

samliew – request private consultation

Join the unofficial Make Discord server to chat with other makers!

[Mike_Hughes]

you are a life saver!! Thank you so much, this works for each day that gets input.

Do you suggest having the data come through in json text? My only concern is if it try to send multiple days of data it will enter both quantities into one cell. Ex: I send 6/26 and 6/27 data the protein cell would should " 171, 182".

If I have the json text come through would that allow the data to create two separate cells or should I only pull 1 day of data at a time?

[samliew]

@Mike_Hughes

Do you suggest having the data come through in json text?

I don’t know where you mean or got this idea from.

Just send the data as you have provided in the example above.

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[Mike_Hughes]

ignore that question. This worked perfectly!!! Thank you so so so much, please let me know if you want a review or testimonial or any way I can help you out. This was huge for me

[samliew]

No problem, glad I could help!

1. If anyone has a new question in the future, please start a new thread. This makes it easier for others with the same problem to search for the answers to specific questions, and you are more likely to receive help since newer questions are monitored closely.

2. The Make Community guidelines encourages users to try to mark helpful replies as solutions to help keep the Community organized.

This marks the topic as solved, so that:

• others can save time when catching up with the latest activity here, and
• allows others to quickly jump to the solution if they come across the same problem

To do this, simply click the checkbox at the bottom of the post that answers your question:

3. Don’t forget to like and bookmark this topic so you can get back to it easily in future!

4. Do join the unofficial Make Discord server for live chat and video assistance

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!