Correctly Format/Validate JSON data structure to send HTTP POST Request

[Ecco]

I’m building a scenario that obtains data from cells within a single column in Google Sheets, correctly formats the data into JSON, then sends an HTTP POST request into a Discord channel. I’ve tried using the following modules. Set A Variable, Aggregate/Parse to JSON, Text Aggregator but can’t seem to find the correct way to structure the data how I want it.

My desired outcome is to have a single json string which includes key:value pairs.

for example :

[
{
“1”: null,
“2”: “Academia”,
“3”: “english book (short story)”
etc…
}
]

Google Sheets Input

[
    {
        "tq": "select A",
        "from": "drive",
        "limit": 19,
        "select": "list",
        "sheetId": 0,
        "spreadsheetId": "/"
    }
]

Google Sheets Output

[
    {
        "0": null,
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 1
    },
    {
        "0": "Academia",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 2
    },
    {
        "0": "english book (short story)",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 3
    },
    {
        "0": "english textbook",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 4
    },
    {
        "0": "english tv series",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 5
    },
    {
        "0": "spanish book (novel)",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 6
    },
    {
        "0": "french language app",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 7
    },
    {
        "0": "french online tutor lesson",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 8
    },
    {
        "0": "french podcast",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 9
    },
    {
        "0": "french videos",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 10
    },
    {
        "0": "korean book (novel)",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 11
    },
    {
        "0": "korean flashcards/videos",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 12
    },
    {
        "0": "korean textbook",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 13
    },
    {
        "0": "italian language app",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 14
    },
    {
        "0": "italian review",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 15
    },
    {
        "0": "japanese review",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 16
    },
    {
        "0": "japanese book (graded reader)",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 17
    },
    {
        "0": "portuguese book (novel)",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 18
    },
    {
        "0": "portuguese textbook",
        "__IMTLENGTH__": 19,
        "__IMTINDEX__": 19
    }
]

Set Variable Input (total of 19 times but only showing the first 3)

[
    {
        "name": "1",
        "scope": "roundtrip",
        "value": null
    }
]
[
    {
        "name": "2",
        "scope": "roundtrip",
        "value": "Academia"
    }
]
[
    {
        "name": "3",
        "scope": "roundtrip",
        "value": "english book (short story)"
    }
]

Set Variable Output (total of 19 times but only showing the first 3)

[
    {
        "1": null
    }
]
[
    {
        "2": "Academia"
    }
]
[
    {
        "3": "english book (short story)"
    }
]

[Mr.Make]

I believe I was able to get your desired output please let me know if you need more help.

I aggregated you value from your initial module

then used a mapping function to get only the variables we want.

Good luck let me know if this helps you!!

[Ecco]

I got the same outcome. Now let me try and make the HTTP POST Request to see if it works

I just tried it and it did not work for me. I got the following error message.

I noticed that I got the same output as you did in the last photo you uploaded but when I went into the ‘Download Output Bundles’, it only showed the text (value) and not the numbers (key). I want both the key and value to be the output and not just the value.

[Mr.Make]

Hi I was able to figure this out for you. I think there is a bug in MAKE so you need to replace all of the places where you have the key as 0 with something else it seems using this as a key does not allow make to recognize it and kept giving me empty values in the collection until I was able to test and see changing the key name allows you do what you need done.

First I had to replace the JSON you gave you by doing this

{{replace(“JSON”; “0"”:“; “test””:")}}

I change the key from 0 to test. Another note it seems when you try to replace “0” in the replace function it automatically causes an issues and removes the 0 from the function this could all be related some how.

After I did that I parsed the new JSON then used an array aggregator and set the variable using toCollection

This got me your needed output

As an fyi what you will have to do is take the data from google sheets and use a create JSON then replace the 0 with a different key then parse your new JSON.

Let me know if you need more explanation.

@Michaela @Make_Bot FYI on the bug above.

TLDR on bug is when using a 0 as a value to create a collection it does not allow to pull the values with the key of 0. Also if you use “0” in replace it removes the 0 without the user doing anything.

Let me know if this was able to help you @Ecco

[samliew]

Welcome to the Make community!

For bugs like this, you should definitely contact support so that it can be fixed.

You can open a new ticket here, or if you are unable to login for some reason, you can create another new free account to access the ticketing system (which is only available to logged-in users). Alternatively, you should be able to send an email to helpdesk@make.com and support@make.com and it should create a ticket. After submitting a new ticket, you will receive an automatic confirmation email with the subject “Ticket Created” in the subject. If you do not receive this, try sending the ticket again.

If you manage to get your issue resolved with support, we’d still love to hear about it! Sharing your solution on the forum can help others facing similar problems.

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

[Ecco]

Can you please provide a screenshot of your Create JSON module? I want to see exactly how you wrote out and mapped the {{replace(“JSON”; “0"”:“; “test””:")}} function. I’m having trouble getting the same output as you did. Thanks in advance.

[Mr.Make]

Sure I’ll send it over tomorrow morning if no one else is able to come in and help. But I’ll try to explain without screenshots.

1. After your aggregator you need to use the Create JSON module using the data from the aggregator.

2. After we have the data in json form we can replace the key that is giving us the issue there using a set variable module.

3. Use the replace function then replace JSON
   This value 0”:
   With this test”:

I explained above why I’m doing it like this. But now all of the keys will be changed.

4.use the parse json module and covert this back to data structure.

5.use array aggregator.

6. Set variable and set tocollection like shown above.

Let me know if this clarifies.

[Ecco]

Thanks for the help.

I’ll still be here providing screenshots to see if anyone else can help me get the desired output.

[Ecco]

This is my most recent attempt but I got an error message. It might be because the module 49 needs to come before module 44? I’m not sure

[Mr.Make]

Hi @Ecco I’m back. Adding a blue print here which should help you much more. I’m sorry I was mistaken yesterday when I said create JSON. I meant transform to JSON. This is the issue.

The blue print I created Pulls data from my google sheet I wanted to make it as seamless transition to yours as possible so all you need to do is change the data source to your google sheet and you should get the data structured as you need it.

Just update the array aggregator to pull the data you need to aggregate the data you need.

blueprint (18).json (32.6 KB)