Add multiple rows to Google Sheet based on list of variables

[Crumbs]

So I have a scenario where I get data about a purchase from a webhook, and I want to insert the purchased items into a Google Sheet. Sometimes, the customer buys one product, and sometimes they buy multiple different products. The goal is to get each product into a new/separate row in Google Sheets.

If the webhook provided an array of purchased items that would be easy. I’d use an Iterator to iterate over the array of items and insert each one’s into a (new) row.
However here’s the tricky part - I get data as variable names looking like this:
ProdName, ProdQuantity, ProdPrice, ProdName1, ProdQuantity1, ProdPrice1, etc.

In the image you can see a purchase of 3 different items. I guess I need to somehow convert these into an array/collection which I can then iterate through and insert as new rows (each item → new row). But I have no idea how to do this.

PS I can get the total number of products (see the ProdTotalLines variable in the image above).

[samliew]

Option 1:

See Structure flat webhook request to be able to iterate over it - #5 by samliew

You can do that by setting the Webhook “JSON Pass-through” to YES (it is “No” by default), when creating or editing a webhook.

Then, you will be able to map the entire JSON body payload in another module, which would allow you to dynamically extract the number of items.

Option 2:

You can use a repeater module with ProdTotalLines, and use the map function to extract each of the five keys associated with the product.

[Crumbs]

Hey @samliew

First of all thanks for the answer. I’ve just tried both options:

1. In option 1 I marked “JSON Pass-through” but I don’t get a text variable. I’m guessing this is because the data is sent to the webhook as form data and not as JSON… (the encoding for content-type header is application/x-www-form-urlencoded).

2. So I passed on to option 2, but in order to use map I actually need an array first, right? How do I create a suitable array from the webhook data items?

[samliew]

Please provide the output bundles of the modules by running the scenario, then click the white speech bubble on the top-right of each module and select “Download output bundles”.

A.

Save the bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.

Uploading it here will look like this:

bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted output bundle in this manner:

• Either add three backticks ``` before and after the code, like this:

  ```
  <sup>input/output bundle content goes here</sup>
  ```

• Or use the format code button in the editor:
  

Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

Following these steps will allow others to assist you here. Thanks!

[Crumbs]

Hey, thanks. It allows me to upload only images and videos, so pasting the output of the webhook model here (I added the headers data just so you see the content-type, I don’t really need it).

[
    {
        "responsecode": "0",
        "terminalnumber": "87057",
        "xparameter": "0",
        "internaldealnumber": "163105994",
        "Sulac25": "1",
        "CardOwnerPhone": "0501234567",
        "MutagId": "2",
        "MutagName": "Visa",
        "Lest4Numbers": "1234",
        "ApprovelNumber": "0499665",
        "TotalCommition": "0",
        "suminagorot": "200",
        "suminfull": "2",
        "cointype": "1",
        "responsdescription": "Transaction completed successfully",
        "ProdName": "First Product Name",
        "ProdItemID": "7",
        "ProductID": "",
        "ProdQuantity": "1",
        "ProdPrice": "1",
        "ProdName1": "Second Product Name",
        "ProdItemID1": "10",
        "ProductID1": "",
        "ProdQuantity1": "1",
        "ProdPrice1": "1",
        "ProdTotalLines": "2",
        "ProdGroupID": "93",
        "GroupDescription": "General Description",
        "CardOwnerName": "John Smith",
        "UserEmail": "user@email.com",
        "UserIP": "2a00:a040:199:3b0b:e77a:a3c6:c5b9:9f1b",
        "invNumber": "6961",
        "invoicenumber": "6961",
        "invType": "3",
        "invResponseCode": "0",
        "intTo": "John",
        "intCity": "City Name",
        "InvAddress": "Street Name",
        "InvAddress2": "Zipcode 12345",
        "InvPhone": "",
        "InvMobile": "0501234567",
        "UID": "24012020555929582902956",
        "OriginalApprovalNumber": "0499665",
        "Custom01": "String1",
        "Custom02": "String2",
        "Custom03": "String3",
        "Custom05": "String4",
        "Custom06": "String5",
        "Custom07": "String6",
        "Custom08": "String7",
        "Custom09": "String8",
        "Custom10": "String9",
        "Custom11": "String10",
        "Custom12": "String11",
        "Custom13": "String12",
        "Custom14": "String13",
        "Custom19": "93",
        "NumberOfPaymentsToPrint": "1",
        "FirstPayment": "0",
        "ConstPayment": "0",
        "RecurringCode": "0",
        "RecurringIsNewAccount": "False",
        "RecurringAccountID": "3736",
        "RecurringOrderID": "20967",
        "invCompanyID": "",
        "CardOwnerID": "123456789",
        "DealDate": "2024-01-20",
        "DealTime": "20:55",
        "IsTrumaDebite": "True",
        "NumOfPaymentForTruma": "5",
        "CardYear": "2026",
        "CardMonth": "9",
        "Token": "479f1353-ba97-4b41-ad4e-1e45abd668ed",
        "BillLocation": "2",
        "CreditCardIssuer": "CAL",
        "__IMTHEADERS__": [
            {
                "name": "connection",
                "value": "upgrade"
            },
            {
                "name": "x-real-ip",
                "value": "172.69.128.143"
            },
            {
                "name": "x-request-id",
                "value": "bc04676c9aeb7c961bc6fdf61ecd9189"
            },
            {
                "name": "content-length",
                "value": "2442"
            },
            {
                "name": "accept-encoding",
                "value": "gzip"
            },
            {
                "name": "cf-ray",
                "value": "848988f13944e3cf-TLV"
            },
            {
                "name": "cf-visitor",
                "value": "{\"scheme\":\"https\"}"
            },
            {
                "name": "user-agent",
                "value": "CardCom/20170426"
            },
            {
                "name": "referer",
                "value": "https://secure.cardcom.solutions"
            },
            {
                "name": "content-type",
                "value": "application/x-www-form-urlencoded"
            },
            {
                "name": "traceparent",
                "value": "00-38a74107af3e57286c4d058a299ee351-bb6f006c718545b8-01"
            },
            {
                "name": "elastic-apm-traceparent",
                "value": "00-38a74107af3e57286c4d058a299ee351-bb6f006c718545b8-01"
            },
            {
                "name": "tracestate",
                "value": "es=s:1"
            },
            {
                "name": "request-id",
                "value": "|cd7125a8-4281f82109257ad2."
            },
            {
                "name": "cf-connecting-ip",
                "value": "82.80.227.17"
            },
            {
                "name": "cdn-loop",
                "value": "cloudflare"
            },
            {
                "name": "cf-ipcountry",
                "value": "IL"
            }
        ]
    }
]

[samliew]

Hmm, looks like it’s not possible.

It is highly recommended that requests sent to Make’s Custom Webhook module should be in JSON if possible.

A possible workaround I can think of is to use Zapier as the intermediary to parse the FormData into JSON, before forwarding it to your Make webhook as JSON, so that you can apply the “JSON Pass-Through” option to YES.

Unfortunately there is no “FormData Pass-Through” option, and Make just converts it all into variables.

I’m sure you and others would agree that this is a nice feature to have.

You can submit this suggestion to the Idea exchange, under App improvement ideas](https://www.make.com/en/app-improvement-ideas/).

Don’t forget to search for it first, just in case someone already suggested it, so that you don’t end up creating a duplicate.

[samliew]

Option 3:

One hacky workaround might be to do this:

Since you know the number of items in advance, repeat that number of times:

Set variable based on index:

(expand the switch function parameters based on the largest you are expecting)

Aggregate to array.

[Crumbs]

Hey, thanks os much! I tried your “hacky way” and it works like a charm. Just one question - you added “aggregate to array” as last phase. Why is that necessary? For now I just add the variables that come out from “Set multiple variables” model into the Google Sheets row, and it seems to work. Am I missing something?

[samliew]

Ah, if you don’t need the items at the same time, and want to process each item individually, then you won’t need an aggregator.

I only suggested it because usually others want to sort/filter/etc.

[Crumbs]

Awesome, thanks so much for your time and help!