JSON Expression in Automation - Insert Image from AppSheet

[mykrobinson]

Me again 🙂

I am trying to modify the body of a webhook that sends a chat message to Google Chat when an automation is triggered. The automation in question is for when a patient is marked as deceased. I edited it today to try extract a photo of the patient from AppSheet and embed it in the chat message. The current JSON is:

{
    "text": "Rest in peace, *<<[Patient Name]>>* , 
 born  <<[DOB]>>, passed on <<[Date of Death]>>. \n\n Recorded by *<<[Recorded By]>>* on *<<[Record Timestamp]>>*
\n<<[_patientIdentifier].[Photo]>>"
}

It works, however it actually shows the text of the URL where the image is located. How do I get this to display the image but not have it linked?

Would I just add another section like:

{
              "image": { "imageUrl": "<<[_patientIdentifer].[Photo]>>" }
            }

[mykrobinson]

Documenting this as i try in case anyone else ever has the same question. Adding the Image portion above didn't work and yields the following result in the automation monitor

Handle updated deceased record
Created TimeStamp
7/15/2022 3:24:50 PM
Status
Error
Current Step
Handle updated deceased record-returnStep
Error Message
Error encountered in step with name [Deceased Chat Processor]: Error: Failed to parse JSON due to After parsing a value an unexpected character was encountered: ". Path 'text', line 1, position 143.. Invalid JSON value starts with: "image": {"imageUrl": "https://www.appsheet.com/image/getimageurl?appName=PatientPopulation-5620182&
Execution Time (In seconds)
6

[Marc_Dillon]

What format does Google Chat expect an image to come in?

[mykrobinson]

sorry for the delay, been a long weekend...

Looks to me like it takes JSON to format the messages. Anything I do in text, including adding column data from AppSheet, seems to work just fine. And I am unsure if the error I am getting is coming back from Google Chat or if AppSheet is giving the error message because AppSheet itself is failing to parse JSON.

I'm using this somewhat as my guide for the JSON formatting

https://developers.google.com/chat/api/guides/message-formats/cards 

with this being what got me started initially with sending Google Chat messages from AppSheet:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Integration-with-Google-Chat-How-Actually-it-was...


@Koichi_Tsuji if you're around, perhaps you have some insight on the topic?

Thanks

[mykrobinson]

UPDATE: I was able to push a "card" to Google Chat from AppSheet by using this template nad editing the image and URL

{
  "cards": [
    {
      "sections": [
        {
          "widgets": [
            {
              "image": {
                "imageUrl": "https://thekidneyexperts.com/wp-content/uploads/2021/11/The-Kidney-Expert.jpg",
                "onClick": {
                  "openLink": {
                    "url": "https://thekidneyexperts.com"
                  }
                }
              }
            }
          ]
        }
      ]
    }
  ]
}

 so i think i need to explore this further and format the output as a "card." I'll try again and see if I can extract some column data from AppSheet and include the image

[mykrobinson]

UPDATE: We're getting there. Trying to take this in baby steps and finding that my biggest issue is the fact that the Body section in the "Call a Webhook" part of AppSheet does not do a great job at all of helping with indentation and making sure the correct number of opening and closing brackets, braces, etc, are intact. 

I was able through much trial and error to get it to push a text widget in Google Chat card format. I tried to take it a step further and add a photo but am running into a syntax error that may take some time to figure out.

{
"cards": [
{
"sections": [
{
"widgets": [
{
"textParagraph":{
"text": "<b><<[Full Name]>></b>"
}
}
]
"widgets": [
{
"image": {
"imageUrl": "<<[Photo]>>"
}
}
]
}
]
}
]
}

so i did get the name to pull from AppSheet and push, both with and without text formatting (bold in the second attempt), I just need to find a sane way to craft my script per Google's documentation, but in a way that is easier to spot mistakes without just trying with makeshift automations based on changing values on my test patient, Demi Lovato 🙂

[mykrobinson]

Thinking out loud, but i wonder if this would best be done via Apps Script 🤔 But that brings the question, can Apps Script reach back into AppSheet and pull data the way i need? I know its all in spreadsheets at the end of the day, but i do quite like the way I can use AppSheet columns and expressions in JSON, and the webhook method seems to be the best of both worlds, minus the jankky syntax of Google Chat Cards syntax

marking this page for future reference:

https://developers.google.com/chat/api/guides/message-formats/cards