Generating a JSON file in a workflow

[Hussein_Osseily]

Hello Community, 

I am trying to generate a JSON file in a workflow , "generate a document", but the result is most likely incorrect, any help would be really appreciated. 

Example:

Table1:

ID	Client	Rep
1A	A	H
2B	B	H
3C	C	I

Template: 

 <<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
}
   <<END>>

Result: 

{
"ID": "1A",
"Client": "A",
"Rep": "H"
}
{
"ID": "2B",
"Client": "B",
"Rep": "H"
}

Shoudn't the multiple objects be either enclosed within an array [ ] or separated by commas , if they are part of the same array or object as below ? 

[
  {
    "ID": "1A",
    "Client": "A",
    "Rep": "H"
  },
  {
    "ID": "2B",
    "Client": "B",
    "Rep": "H"
  }
]

I tried adding the [   ] in the template but the , between the objects are still not appearing

[scott192 · Accepted solution]

I can see that you have had a few replies to this and I know getting JSON templating correct can be difficult at first.  So my answer is to paste a JSON template that I created for a specific job.  It has Main, sub and sub sub records.  I hope you can study it and see how it all fits together.

{
    "Quotes":[<<Start: Select(QuotesV2[QuoteID],[QuoteID] = [_THISROW].[QuoteID],TRUE)>>
    {
    "QuoteFolder":"<<[FolderID]>>",
    "QuoteDocID":"<<[QuoteDocID]>>",
    "Filename":"<<Concatenate('Quote ',[TFPReference],' V',[QuoteVersion] + 1)>>",
    "QuoteRef": "<<[TFPReference]>>",
    "EnquiryRef":"<<[EnquiryID].[EnquiryRef]>>",
    "InitialInformation": "<<[InitialInformation]>>",
    "Venue": "<<[Venue]>>",   
    "QuoteFor": "<<[ContactName]>>",
    "Email": "<<[ContactEmail]>>",
    "QuoteGenerated": "<<TEXT([LastQuoteGen],'DDD MMM dd yyyy')>>",  
    "PreDiscountTotal":"<<[PreDiscTot]>>",
    "QuoteDiscount":"<<[Discount]>>",
    "DiscountValue":"<<[DiscountValue]>>",
    "QuoteTotal":"<<[QuoteTotal]>>",
    "QuoteLines":[<<Start: [Related QuoteLines]>>
      {
         "ItemName": "<<[ItemName]>>",   
         "Description": "<<[Description]>>",    
         "Amount": "<<[Amount]>>", 
         "PreDiscountCost": "<<[PreDiscountCost]>>",
         "PreDiscountTotal": "<<[PreDiscountTotal]>>",
         "Discount": "<<[Discount]>>",
         "DiscountValue": "<<[DiscountValue]>>",   
         "PostDiscountCost": "<<[PostDiscountCost]>>",        
         "TotalCost": "<<[TotalCost]>>",
         "Therapy":[<<Start: [Related Itemisation]>>
        {
            "ItemName": "<<[ItemName]>>",
            "Description": "<<[Description]>>",
            "UnitCost": "<<[UnitCost]>>",
            "Unit": "<<[Unit]>>",
            "Amount": "<<[Amount]>>",
            "Length": "<<[Length]>>",
            "Workers": "<<[Workers]>>",
            "Units": "<<[Units]>>",
            "Cost": "<<[Cost]>>",
            "RowCost": "<<[RowCost]>>",
            "TimeCost": "<<[TimeCost]>>",
            "Journeys": "<<[Journeys]>>",
            "JL": "<<[JL]>>"          
        }
        <<End>>
        ],
        "AdditionalExpenses":[<<Start: [Related AdditionalExpenses]>>
        {
               "ItemName": "<<[ItemName]>>",
               "Description": "<<[Description]>>",
               "UnitCost": "<<[UnitCost]>>",
               "Unit": "<<[Unit]>>",
               "Amount": "<<[Amount]>>",
               "Cost": "<<[Cost]>>"
        }
        <<End>>
        ],
        "TravelExpenses":
        [<<Start: [Related TravelExpenses]>>
        {
               "ItemName": "<<[ItemName]>>",
               "Description": "<<[Description]>>",
               "UnitCost": "<<[UnitCost]>>",
               "Unit": "<<[Unit]>>",
               "Journeys": "<<[Journeys]>>",        
               "Length": "<<[Length]>>",
               "Workers": "<<[Workers]>>",
               "Cost": "<<[Cost]>>"
        }
        <<End>>
        ]
      }
      <<End>>
      ]
    }
    <<End>>
    ]
}

[TeeSee1]

Would this work?

[
<<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
},
<<END>>
]

[Hussein_Osseily]

Thanks for your response. 

While this would work if there are multiple Reps , wouldn't it be invalid if there is only one Rep as the example in above table  FILTER("Table1", [Rep] = "I")

Generating a JSON file shouldn't be structured correctly out of the box ?

[TeeSee1]

I fail to see where it might be incorrect in having one Json object in an array. Whatever application that processes an array of JSON objects should be able to handle one regardless of the number of objects contained in it, even zero objects.

[Hussein_Osseily]

Please correct me if i am wrong. 
your suggestion has a "," before the <<END>>  which is my concern

If let's say its a Single object output , wouldn't the "," before the "]"  make the JSON invalid due to line 6 having a ","?  

[
  {
    "ID": "3C",
    "Client": "C",
    "Rep": "I"
  },
]

 Double output, same at line 11, the extra ",": 

[
  {
    "ID": "1A",
    "Client": "A",
    "Rep": "H"
  },
  {
    "ID": "2B",
    "Client": "B",
    "Rep": "H"
  },
]

[TeeSee1]

Absolutely no issues!

[Hussein_Osseily]

Thanks for testing it, strange, i have tested it with some JSON validator and i based my words on that. if it works that is great. 

Response from JSON Validator: 
Invalid JSON!

Error: Parse error on line 11:
...",   "Rep": "H"},]
--------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '[', got ']'

 But thanks i will send a test JSON anyway to the end user to test it in their system

[TeeSee1]

My apologies.

I did more digging and actually I was wrong and you are correct. JSON does not allow a trailing comma in an array. What I tested is a Javascript array and not JSON.

[Hussein_Osseily]

Am wondering why it doesn't auto generate a correct structure out of the box when there are multiple objects, JSON is not only made to carry a single object . 

Do you have any idea how to include the "," if there are multiple objects but not after the last object, and not when its a single object ? 

[scott192 · Accepted solution]

Ok, your start statement needs a little change to generate correct JSON.

 <<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
}
   <<END>>

// Change it to

{
"JSON":[<<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
}
<<END>>
]
}


// this should also work

{
[<<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
}
<<END>>
]
}

[TeeSee1]

Thanks for your post. I learned a lot since I had not understood how JSON works in AppSheet (you can only produce an OBJECT as the most outer element and not an array) as well as how JSON should be formatted (like no trailing commas allowed).

Just a comment. @scott192 , can you check if the last expression is valid? It seems to produce an object directly containing an array without forming a name-value pair which is not a valid JSON and I don't think it works. 

[scott192]

No, I don't think it would work.  The objective at the other end is probably to turn the JSON into a Javascript Object.  Let me track down the code I use in Appscript to parse the JSON...  I'll be back!

[scott192]

So here is a small snippet of the code.  The JSON file that has been created was saved in a known google drive folder with a known name.  It is the known name that is passed into the script on the very first line.

function generateJSONQuote(JSONFilename) {
  
  var files = DriveApp.getFolderById(jsonfilestore).getFilesByName(JSONFilename);

  if (files.hasNext()) {
    var file = files.next();
    var content = file.getBlob().getDataAsString();
    var json = JSON.parse(content);
    //Logger.log(JSON.stringify(json, null, 2));
    file.setTrashed(true);

    var appsheet = [];

    json.Quotes.forEach(function (quote) {

After this point, the JSON is now a Javascript Object and can be manipulated/read as such. 

[TeeSee1]

This should be UNmarked as a solution!

[Hussein_Osseily]

Edited, Thanks for your time trying . appreciated 

[scott192 · Accepted solution]

I can see that you have had a few replies to this and I know getting JSON templating correct can be difficult at first.  So my answer is to paste a JSON template that I created for a specific job.  It has Main, sub and sub sub records.  I hope you can study it and see how it all fits together.

{
    "Quotes":[<<Start: Select(QuotesV2[QuoteID],[QuoteID] = [_THISROW].[QuoteID],TRUE)>>
    {
    "QuoteFolder":"<<[FolderID]>>",
    "QuoteDocID":"<<[QuoteDocID]>>",
    "Filename":"<<Concatenate('Quote ',[TFPReference],' V',[QuoteVersion] + 1)>>",
    "QuoteRef": "<<[TFPReference]>>",
    "EnquiryRef":"<<[EnquiryID].[EnquiryRef]>>",
    "InitialInformation": "<<[InitialInformation]>>",
    "Venue": "<<[Venue]>>",   
    "QuoteFor": "<<[ContactName]>>",
    "Email": "<<[ContactEmail]>>",
    "QuoteGenerated": "<<TEXT([LastQuoteGen],'DDD MMM dd yyyy')>>",  
    "PreDiscountTotal":"<<[PreDiscTot]>>",
    "QuoteDiscount":"<<[Discount]>>",
    "DiscountValue":"<<[DiscountValue]>>",
    "QuoteTotal":"<<[QuoteTotal]>>",
    "QuoteLines":[<<Start: [Related QuoteLines]>>
      {
         "ItemName": "<<[ItemName]>>",   
         "Description": "<<[Description]>>",    
         "Amount": "<<[Amount]>>", 
         "PreDiscountCost": "<<[PreDiscountCost]>>",
         "PreDiscountTotal": "<<[PreDiscountTotal]>>",
         "Discount": "<<[Discount]>>",
         "DiscountValue": "<<[DiscountValue]>>",   
         "PostDiscountCost": "<<[PostDiscountCost]>>",        
         "TotalCost": "<<[TotalCost]>>",
         "Therapy":[<<Start: [Related Itemisation]>>
        {
            "ItemName": "<<[ItemName]>>",
            "Description": "<<[Description]>>",
            "UnitCost": "<<[UnitCost]>>",
            "Unit": "<<[Unit]>>",
            "Amount": "<<[Amount]>>",
            "Length": "<<[Length]>>",
            "Workers": "<<[Workers]>>",
            "Units": "<<[Units]>>",
            "Cost": "<<[Cost]>>",
            "RowCost": "<<[RowCost]>>",
            "TimeCost": "<<[TimeCost]>>",
            "Journeys": "<<[Journeys]>>",
            "JL": "<<[JL]>>"          
        }
        <<End>>
        ],
        "AdditionalExpenses":[<<Start: [Related AdditionalExpenses]>>
        {
               "ItemName": "<<[ItemName]>>",
               "Description": "<<[Description]>>",
               "UnitCost": "<<[UnitCost]>>",
               "Unit": "<<[Unit]>>",
               "Amount": "<<[Amount]>>",
               "Cost": "<<[Cost]>>"
        }
        <<End>>
        ],
        "TravelExpenses":
        [<<Start: [Related TravelExpenses]>>
        {
               "ItemName": "<<[ItemName]>>",
               "Description": "<<[Description]>>",
               "UnitCost": "<<[UnitCost]>>",
               "Unit": "<<[Unit]>>",
               "Journeys": "<<[Journeys]>>",        
               "Length": "<<[Length]>>",
               "Workers": "<<[Workers]>>",
               "Cost": "<<[Cost]>>"
        }
        <<End>>
        ]
      }
      <<End>>
      ]
    }
    <<End>>
    ]
}

[Hussein_Osseily]

Thanks for sharin this , this is very helpful, actually i have such a use case that i was trying to avoid due to the complexity, but now i see it doable, appreciated !

[Hussein_Osseily]

@scott192 
This worked for me perfectly. 

{
"JSON":[<<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
}
<<END>>
]
}

The other option gave an error in line 1 
Thanks a lot  

[scott192]

Yep, I can see now why the other options gave an error.  Glad it helped though 😁

[Suvrutt_Gurjar]

---

@scott192 wrote:

I can see that you have had a few replies to this and I know getting JSON templating correct can be difficult at first.  So my answer is to paste a JSON template that I created for a specific job.  It has Main, sub and sub sub records.  I hope you can study it and see how it all fits together.

---

Nice one @scott192