Creating a method to turn a list of items in a table into a google doc using Make. everything is working but the joined list of items is output wrong, creating invalid JSON. Ive used the Json Template column to create a json object for each item. Then I want to join those items into one array. I used the join field, looking through a query, to the JSON Template. But in the webhook the output is wrapped in extra " ". How do I avoid this?
Create a Lookup column instead of a Joined List. Then create a JSON Object column that brings in that lookup column. That will create a proper JSON array in a JSON object.
2 Likes
Ok. That was the solution on that. Thank you @Jeff_Hager But now I’m hoping someone can help me with a related problem. In Make, I’m passing these values:
{{#each body.items}}
## {{title}} - {{topic}}
{{description}}
**Status:** _{{status}}_
---
{{/each}}
to iterate though the list of items and populate a google doc template.
In Glide, it shows that the output of the webhook looks like this:
{"body"
:
{
"items"
:
{
"0"
:
{
"title":"AB 239 (Harabedian)"
"topic":"L.A. County Fire Recovery Task Force "
"status":"Set for hearing Wednesday, April 30, 9 a.m. in Asm. Appropriations, 1021 O Street, Room 1100"
"description":"Requires HCD and the Office of Emergency Services to convene a state-led Los Angeles County disaster housing task force for the purpose of coordinating and streamlining efforts between HCD, FEMA, the Office of Emergency Services, and local governments to rebuild housing in communities impacted by the Eaton and Palisades fires."
}
"1"
:
{
"title":"AB 265 (Caloza)"
"topic":" Small Business Recovery Fund"
"status":"No hearing in Asm Appropriations currently scheduled"
"description":"Creates the Small Business Recovery Fund at the Office of the Small Business Advocate to help small businesses and nonprofits recover from declared disasters. The bill proposes matching grants to impacted small businesses and nonprofits ranging from $2,500 to $100,000, if money for the program is appropriated in the state budget."
}
Google doc template has one value: {{items}}. But in the google doc, it just gives the raw output like this
{“title”:“AB 239 (Harabedian)”,“topic”:“L.A. County Fire Recovery Task Force “,“description”:“Requires HCD and the Office of Emergency Services to convene a state-led Los Angeles County disaster housing task force for the purpose of coordinating and streamlining efforts between HCD, FEMA, the Office of Emergency Services, and local governments to rebuild housing in communities impacted by the Eaton and Palisades fires.”,“status”:“Set for hearing Wednesday, April 30, 9 a.m. in Asm. Appropriations, 1021 O Street, Room 1100”}, {“title”:“AB 265 (Caloza)”,“topic”:” Small Business Recovery Fund”,“description”:"Creates the Small Business Recovery Fund at the Office of the Small Business Advocate to help small businesses and nonprofits recover from declared disasters.
What am I doing wrong? Any hints?
I got this to work by using a standard template column to wrap it in [ ] after creating the join using a lookup as suggested.
1 Like
Wouldn’t this work for your case?
1 Like
I may not have approached it properly, but the Json Object column produced a result like this:
{
"items": [
"{ \"title\": \"AB 236 (Chen)\", \"topic\": \"Digital ....]
}
when I fed it my joined list from the lookup.
I need something like this:
[ { "title": "AB 236 (Chen)", "topic": "Digital financial asset businesses: regulatory fees.", "description": "Description", ... }, ]
Here are the steps I would follow to obtain the expected output @BLA_68 
- Create a
JSON of this rowcolumn (JSON Object) with:- title → Title
- topic → Topic
- status → Status
- description → Description
- Create a
JSON of all rowscolumn (Lookup):- Relation column → Items > JSON of this row
From a technical perspective, you have created an array Glide will use to format your JSON correctly (cf. Jeff’s answer).
- Relation column → Items > JSON of this row
- Create a
JSON with items onlycolumn (JSON Object):- items → JSON of all rows
In my opinion, this should be enough to populate the Google Docs template: you have an object (items) that contains all your records correctly formatted.
Make will be able to iterate on each element inside items. It means it will:- take the “AB 239” record
- read its properties (title, topic, status and description)
- push them at their places in your template
- move to the next record “AB 265” to proceed exactly the same way
Though, just to be complete, if you need to wrap “items” INSIDE another property such as “body” (cf. your previous posts), you can move to another option, and this will be step 4.
- items → JSON of all rows
- Create a
JSON with body and itemscolumn (JSON Template):- JSON Template →
{ "body": { "items": $listOfItems } }- listOfItems → JSON of all rows
Results
In JSON with items only column:
{
"items": [
{
"title": "AB 239 (Harabedian)",
"topic": "L.A. County Fire Recovery Task Force ",
"status": "Set for hearing Wednesday, April 30, 9 a.m. in Asm. Appropriations, 1021 O Street, Room 1100",
"description": "Requires HCD and the Office of Emergency Services to convene a state-led Los Angeles County disaster housing task force for the purpose of coordinating and streamlining efforts between HCD, FEMA, the Office of Emergency Services, and local governments to rebuild housing in communities impacted by the Eaton and Palisades fires."
},
{
"title": "AB 265 (Caloza)",
"topic": " Small Business Recovery Fund",
"status": "No hearing in Asm Appropriations currently scheduled",
"description": "Creates the Small Business Recovery Fund at the Office of the Small Business Advocate to help small businesses and nonprofits recover from declared disasters. The bill proposes matching grants to impacted small businesses and nonprofits ranging from $2,500 to $100,000, if money for the program is appropriated in the state budget."
}
]
}
In JSON with body and items column:
{
"body": {
"items": [
{
"title": "AB 239 (Harabedian)",
"topic": "L.A. County Fire Recovery Task Force ",
"status": "Set for hearing Wednesday, April 30, 9 a.m. in Asm. Appropriations, 1021 O Street, Room 1100",
"description": "Requires HCD and the Office of Emergency Services to convene a state-led Los Angeles County disaster housing task force for the purpose of coordinating and streamlining efforts between HCD, FEMA, the Office of Emergency Services, and local governments to rebuild housing in communities impacted by the Eaton and Palisades fires."
},
{
"title": "AB 265 (Caloza)",
"topic": " Small Business Recovery Fund",
"status": "No hearing in Asm Appropriations currently scheduled",
"description": "Creates the Small Business Recovery Fund at the Office of the Small Business Advocate to help small businesses and nonprofits recover from declared disasters. The bill proposes matching grants to impacted small businesses and nonprofits ranging from $2,500 to $100,000, if money for the program is appropriated in the state budget."
}
]
}
}
Hope it helps! 
2 Likes
Very helpful. Let me try again with that approach since it’s probably more bullet proof than what I came up with (though it is working).
So although this worked last week with my setup, when I tested it this week before adding it live, I got a message that the JSON was invalid. 
So, I’ve followed the steps suggested by @Nicolas_Joseph and have properly formed JSON like this:
{
"items": [
{
"title": "SB 31 (McNerney)",
"topic": "Water quality: recycled water.",
"status": "Set for hearing Monday, May 12, 10 a.m. in Sen. Appropriations, 1021 O Street, Room 2200"
},
{
"title": "AB 221 (Ramos)",
"topic": "Tribal Nation Grant Fund.",
"status": "Set for hearing Wednesday, May 7, 9 a.m. in Asm. Appropriations, 1021 O Street, Room 1100"
},
But in the workflow, when I examine the log, the webhook data sent shows this:
{
"body"
:
{
"items"
:
{
"items"
:
{
"0"
:
{
"title":"SB 31 (McNerney)"
"topic":"Water quality: recycled water."
"status":"Set for hearing Monday, May 12, 10 a.m. in Sen. Appropriations, 1021 O Street, Room 2200"
}
"1"
:
{
"title":"AB 221 (Ramos)"
"topic":"Tribal Nation Grant Fund."
"status":"Set for hearing Wednesday, May 7, 9 a.m. in Asm. Appropriations, 1021 O Street, Room 1100"
}
I don’t understand where the body and extra items are coming from and why it is no longer an array, but I assume it is something I’m doing wrong in my Make scenario?
This is what the output looks like in the webhook module:
the Json parser output looks like this:
Then through the Array Aggregator set up like this:
the file that is emailed works…but if delivers just the first item in the array.
Without being a Make specialist (or Glide for that matter 
), I would have used an Iterator instead of an Array aggregator before choosing the Insert a Paragraph to a Document in Google Docs module.
Here is my Google Docs output:
And the previous steps:
In Glide
- A
Single Valuecolumn to get the full JSON (in Users table)
- A button with
Trigger webhookaction
In Make
-
Webhooks Output (just like yours
)
-
Iterator
-
Google Docs with Insert a Paragraph to a Document → By appending to the body of document
I’ll admit the truth: I tried to format your document with bold and title style (using Markdown first then HTML)… and it’s an epic fail at this point. Make community as some posts about this topic (in this particular case), and a solution using Google Docs Api and/or App Script is given (cf. Append formatted text to Google Doc - How To - Make Community).
At this point, I have now reached my level of incompetence 
Hope it helps for the Glide part though 
1 Like
Hi thank you. I finally made the bold decision to start from scratch in MAKE instead of trying to use the existing scenario that was clearly not agreeing with me. Feeding the new scenario the JSON formatted using the instructions in this thread worked! I was able to get the html formatting to work by using a create google doc module (rather than a google doc template) and wrapping the html formatted output of the aggregator in a body tag. Thank you everyone for your help!
2 Likes