Writing JSON to Google Sheet isn't working

[Chris_Wood]

I want to update a google sheet when someone subscribes to Action Network.
I have got Make to connect to both the Action Network webhook and the google sheet.
However the data from the json is not put into the sheet - a row is added and the date is put in row column G, and the fixed text into column H, but columns A to F are empty.

The input bundle appears to be empty?
The output bundle is

[
    {
        "osdi:submission": {
            "created_date": "2024-02-20T00:45:12Z",
            "modified_date": "2024-02-20T00:45:12Z",
            "identifiers": [
                "action_network:xxxxxx"
            ],
            "person": {
                "created_date": "2024-02-20T00:45:12Z",
                "modified_date": "2024-02-20T00:46:18Z",
                "family_name": "Wood",
                "given_name": "Chris",
                "postal_addresses": [
                    {
                        "primary": true,
                        "locality": "Exeter",
                        "region": "England",
                        "postal_code": "xxx xxx",
                        "country": "GB",
                        "location": {
                            "latitude": xxxxx,
                            "longitude": xxxxx,
                            "accuracy": "Approximate"
                        }
                    }
                ],
                "email_addresses": [
                    {
                        "primary": true,
                        "address": "chris@fakeemail2.co.uk",
                        "status": "subscribed"
                    }
                ],
                "phone_numbers": [
                    {
                        "primary": true,
                        "number": "xxxxxxxx",
                        "number_type": "Mobile"
                    }
                ],
                "custom_fields": {
                    "@!@tag@!@xxxxx": "Exeter_CallMe",
                    "GDPR_consent": "GDPR_Policy_V3"
                },
                "languages_spoken": [
                    "en"
                ]
            },
            "add_tags": [
                "Exeter_CallMe"
            ],
            "action_network:referrer_data": {
                "source": "widget",
                "website": "xxxxxxxx"
            },
            "_links": {
                "self": {
                    "href": "https://actionnetwork.org/api/v2/forms/xxxxxx"
                },
                "osdi:form": {
                    "href": "https://actionnetwork.org/api/v2/formsxxxxxxx"
                },
                "osdi:person": {
                    "href": "https://actionnetwork.org/api/v2/people/xxxxx"
                }
            }
        },
        "action_network:sponsor": {
            "title": "xxxxx",
            "url": "https://actionnetwork.org/groups/xxxxxx"
        },
        "idempotency_key": "xxxxxx"
    }
]

Thanks in advance…

[samliew]

What is the output bundle of your WEBHOOK module?

Welcome to the Make community!

Please provide the input and output bundles of the modules by running the scenario (or get from the scenario History tab), then click the white speech bubble on the top-right of each module and select “Download input/output bundles”.

A.

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

Uploading them here will look like this:

module-1-input-bundle.txt (12.3 KB)
module-1-output-bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted bundles 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 input/output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

This will allow others to better assist you. Thanks!

[Msquare_Automation]

Hi @Chris_Wood

Please check the history data and ensure that elements A to F have values. Confirm if the values are available from the mapped variable or from another variable.

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support
Visit us here
Youtube Channel

[Chris_Wood]

Thanks @samliew I have edited the question to include the output bundle

[samliew]

Thanks, the output bundle was crucial to answering your question. Please remember to provide this detail in future when asking questions on this forum.

It looks like you are trying to map custom_fields – a collection/object, into a text field (cell).

You need to select something within custom_fields of type text, or convert whatever you are trying to insert into a text (string) variable.

One way of converting a collection to an array, is to use the built-in function toArray, with a map and join

e.g.:

{{join(map(toArray(1.`osdi:submission`.person.custom_fields); "value"); ", ")}}

For more information, see https://www.make.com/en/help/functions/array-functions#toarray--collection-

Once I did this, the spreadsheet module worked.

[Chris_Wood]

Awesome, thanks so much. And for being patient and describing what I needed to do (which I ought to have understood in the first place) to give you the info you needed to diagnose

[samliew]

No problem, glad I could help!

1. If you have 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!

[Chris_Wood]

@samliew So, the solution you posted resolves the issue for that cell, but not the others that were not being written to their cell. Cells A,B,C,D and F are still blank. I can see that the data for C,D & F may need some resolving (do I need to specify an index into the respective arrays) but A & B are simply text fields in the json.

It is a painful business testing this, especially as I do not have the permissions to delete the test subscribers from the Action Network database so I have done what I imagine you did, set up a new scenario with an integration JSON module configured from the output bundle above, and using that bundle as the input. It works perfectly… all of the cells are written to.

I had at one stage played with a filter between the modules and it isnt clear how one deletes a filter in Make and thought maybe the data was getting inadvertently filtered so I decided to create a new scenario with a new webhook from scratch. Implemented that and I’m back to square one.

The difference now is that E is still being filled using the formula I copied from your answer.

I see that the way the json is described in E is different to the other fields, pulled from Make’s suggestions:

It references osdi:submission not osdi:signature and is displayed red on white outlined in red not white on red background - these must be referring to different types of things.
Knowing you probably generated your formula from a JSON Parse module I went back to my other working scenario (Parse JSON->Google Sheets) and copied what was in A to A in this scenario so I had:

Now A works , with B still not working - my sheet looks like this:

row 10 generated by the Google Sheet set up above
(I changed the subscriber info for row 9, just to check that your formula was working on live data which is why the Exeter_CallMe tag is missing from E9)

The input to the google sheet is:

So it looks like I have a workaround (I can copy the remaining fields from the JSON/Sheets scenario), but this is far from satisfactory, possibly not stable and surely not how Make intend this to work.

Is there a problem in Make’s Webhook module or am I missing something (I tried at one point putting a Parse JSON module between the two modules, but that type of module only uses the fixed JSON, not input JSON). Maybe I am entering the data incorrectly into the values for the sheet, I have tried typing osdi:submission and seeing how else I can generate it but nothing works except copying it from the other scenario.

I know there is a lot to absorb in this reply but would be really grateful if you could have another look at this issue…

[samliew]

I think you gotta type it out somehow.

The colon : in the key names might be the issue, so you cannot pick from the variables list since it’s bugged.