pythonpandasgoogle-cloud-platformwebhooksgoogle-chat

Send perfectly aligned tabular text message in Google Chat Space using webhook

I have created a Google chat space. I have a script which does some calculation and the final data is in pandas Dataframe. I want to send the data in this Dataframe as Daily alerts in tabular form in the chat space. What have I tried

# sample pandas Dataframe to be sent as alert
df = 
    SYMBOL      LAST        TIMESTAMP
0   20MICRONS   102.15      19-AUG-2022
1   21STCENMGM  27.50       19-AUG-2022
2   3IINFOLTD   45.90       19-AUG-2022
3   3MINDIA     22859.80    19-AUG-2022
4   3PLAND      17.45       19-AUG-2022
5   5PAISA      281.25      19-AUG-2022
6   63MOONS     188.50      19-AUG-2022
7   A2ZINFRA    12.80       19-AUG-2022
8   AAKASH      12.80       19-AUG-2022
9   AAREYDRUGS  35.20       19-AUG-2022

Using tabulate python package (which is used to print tabular data in nicely formatted tables) to create a table string

records = df.to_dict(orient="list")
tabular_string = tabulate(records, headers="keys", tablefmt="github")
print(tabular_string)

#Output
| SYMBOL     |     LAST | TIMESTAMP   |
|------------|----------|-------------|
| 20MICRONS  |   102.15 | 19-AUG-2022 |
| 21STCENMGM |    27.5  | 19-AUG-2022 |
| 3IINFOLTD  |    45.9  | 19-AUG-2022 |
| 3MINDIA    | 22859.8  | 19-AUG-2022 |
| 3PLAND     |    17.45 | 19-AUG-2022 |
| 5PAISA     |   281.25 | 19-AUG-2022 |
| 63MOONS    |   188.5  | 19-AUG-2022 |
| A2ZINFRA   |    12.8  | 19-AUG-2022 |
| AAKASH     |    12.8  | 19-AUG-2022 |
| AAREYDRUGS |    35.2  | 19-AUG-2022 |

Creating message and posting it on webhook url

webhook_url = "https://chat.googleapis.com/..."

message = {"text": tabular_string}
headers = {'Content-Type': "application/json"}
response = requests.post(webhook_url, data=json.dumps(message), headers=headers)

In Chat Space the message is completely misaligned

enter image description here

Whereas the same message if sent to Microsoft Teams channel the table is perfectly aligned.

Solution

  • The issue is that Google Chat uses a proportional font by default, and for this type of line drawing you need a monospaced font.

    You can't control the entire chat's font or whatever the user has selected in their browser, but Google Chat supports some markdown formatting so you can wrap your table between three backquotes (```) like we do here in StackOverflow to create a code block. This is mentioned in their documentation.

    Edit: I built a sample based on Google's webhooks guide with the pandas Dataframe and the tabulate library as described in the OP. Transforming the list into a Dataframe and then back into a list is a little redundant since the data variable can be just plugged in directly to tabulate, but I wanted to reproduce it as close as possible. The most important part is that the table generated by tabulate has the right format anyway.

    from json import dumps
import pandas
from tabulate import tabulate
from httplib2 import Http

def main():
    data = {
        "SYMBOL":["20MICRONS","21STCENMGM","3IINFOLTD","3MINDIA","3PLAND","5PAISA","63MOONS","A2ZINFRA","AAKASH","AAREYDRUGS"],
        "LAST":[102.15,27.5,45.9,22859.8,17.45,281.25,188.5,12.8,12.8,35.2],
        "TIMESTAMP":["19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022","19-AUG-2022",]
    }
    
    df = pandas.DataFrame(data)
    records = df.to_dict(orient="list")
    tabular_string = tabulate(records, headers="keys", tablefmt="github")

    url = "https://chat.googleapis.com/v1/..."
    bot_message = {
        'text': f"```{tabular_string}```"}
    message_headers = {'Content-Type': 'application/json; charset=UTF-8'}
    http_obj = Http()
    response = http_obj.request(
        uri=url,
        method='POST',
        headers=message_headers,
        body=dumps(bot_message),
    )

if __name__ == '__main__':
    main()

    This posted the message successfully to the space:

    enter image description here