Generating tables for Google Docs using shell scripts
For reporting reasons, I regularly have to create a certain table in Google Docs. The table contains incidents from the previous week, with color coded cells for the severity of the incident and whether it was resolved or not. It looks something like this.
A typical table describing incidents
The issues come from a JIRA project, and the task is then to convert the JIRA data into such a table. It doesn’t take very long, but it is still annoying to do this manually. I already created a shell script to get the data out of JIRA (using a script similar to the one I described on Improving shell workflows with fzf), but it would only output text data.
When I looked into ways to fully automate this, my first approach was to do this programmatically via the Google Docs API. But this would probably take an unreasonable amount of effort; after all, creating the tables manually doesn’t take that long, and I only have to do it about once a month. Still, it was annoying enough that I could justify to myself to spend at least some time trying to automate it. I tried to get an LLM to do this for me, and while this didn’t work out, I at least learned that I can create such a table by copy-pasting an HTML table!
To see how this works, it’s best to start with a smaller example, which nevertheless covers the important parts that we are interested in.
A smaller example
After copying it to the clipboard, we can examine the clipboard contents with xclip, using
xclip -selection clipboard -out -target TARGETS
The clipboard supports multiple targets, specified by their MIME type. This is used to copying with formatting or without, or for copying non-text contents like images. With the special TARGETS target, xclip shows all targets that were set when copying the data. In our case, the output is
TIMESTAMP
TARGETS
SAVE_TARGETS
MULTIPLE
STRING
UTF8_STRING
TEXT
chromium/x-web-custom-data
text/html
text/plain
text/plain;charset=utf-8
We see four MIME type targets. The last two contain the plain text versions of the table. The chromium/x-web-custom-data is a special Chromium target, which internally contains another MIME type application/x-vnd.google-docs-document-slice-clip+wrapped which can contain more information than is expressible via HTML. But the format doesn’t seem easy to understand, so we will stick to HTML.
Looking at the contents of the text/html target via
xclip -selection clipboard -out -target text/html | xmllint --format --html -
shows
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta charset="utf-8">
</head>
<body><div dir="ltr" style="margin-left:115.5pt;" align="left" id="docs-internal-guid-2019cc62-7fff-1679-b246-6204939c8215"><table style="border:none;border-collapse:collapse;">
<colgroup>
<col width="79">
<col width="151">
</colgroup>
<tbody><tr style="height:25.5pt">
<td style="border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;"><p dir="ltr" style="line-height:1.44;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">bold</span></p></td>
<td style="border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:#d9ead3;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;"><p dir="ltr" style="line-height:1.44;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">green background</span></p></td>
</tr></tbody>
</table></div></body>
</html>
The style is a bit verbose, since all styles are inlined, but we can see a table tag with a colgroup specifying the column width, followed by a tr with two columns. For the first td, we see the border specifications, followed by alignment, padding, and overflow directions. Inside is a p with a nested span, and inside is the actual text “bold”. The span tag contains the formatting of the text (font-weight: 700). The second td contains all the style information as the first one, plus an additional background-color:#d9ead3; to specify the green background color.
And that’s all we need. With this, we can create a shell script that takes a list of JIRA issues and converts them to an HTML table.
The final part is then to take the HTML table and get it into the clipboard with the correct target so that it can then be pasted into a Google doc. We can again do this with xclip.
echo <html-table> | xclip -selection clipboard -in -target text/html
Tying it all together, here is a script that creates the table at the beginning of the post from some JSON input.
#!/bin/bash
header=$(cat << EOF
<table style="border:none;border-collapse:collapse;">
<colgroup>
<col width="106">
<col width="81">
<col width="181">
<col width="80">
</colgroup>
<tbody>
<tr style="height:0pt">
<td style="border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:#d9d9d9;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">Ticket id</span></p></td>
<td style="border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:#d9d9d9;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">Severity</span></p></td>
<td style="border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:#d9d9d9;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">Description</span></p></td>
<td style="border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:#d9d9d9;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">Status</span></p></td>
</tr>
EOF
)
footer=$(cat << EOF
</tbody>
</table>
EOF
)
row_template=$(cat << EOF
<tr style='height:0pt'>
<td style='border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;'><p dir='ltr' style='line-height:1.2;margin-top:0pt;margin-bottom:0pt;'><span style='font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;'>\(.id)</span></p></td>
<td style='border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:\(if .severity == "critical" then "#f4cccc" elif .severity == "major" then "#fce5cd" else "#fff2cc" end);padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;'><p dir='ltr' style='line-height:1.2;margin-top:0pt;margin-bottom:0pt;'><span style='font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;'>\(.severity)</span></p></td>
<td style='border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;'><p dir='ltr' style='line-height:1.2;margin-top:0pt;margin-bottom:0pt;'><span style='font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;'>\(.description)</span></p></td>
<td style='border-left:solid #000000 1pt;border-right:solid #000000 1pt;border-bottom:solid #000000 1pt;border-top:solid #000000 1pt;vertical-align:top;background-color:\(if .status == "resolved" then "#d9ead3" else "#fff2cc" end);padding:5pt 5pt 5pt 5pt;overflow:hidden;overflow-wrap:break-word;'><p dir='ltr' style='line-height:1.2;margin-top:0pt;margin-bottom:0pt;'><span style='font-size:11pt;font-family:Arial,sans-serif;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;'>\(.status)</span></p></td>
</tr>
EOF
)
# in reality, this data will come from some API
data=$(cat << EOF
{
"issues": [
{
"id": "ticket 1",
"severity": "critical",
"description": "Severe outage",
"status": "resolved"
},
{
"id": "ticket 2",
"severity": "major",
"description": "Production issue",
"status": "open"
},
{
"id": "ticket 3",
"severity": "minor",
"description": "Customer bug",
"status": "resolved"
}
]
}
EOF
)
contents=$(echo "$data" | jq --raw-output ".issues[] | \"$row_template\"")
echo "$header$contents$footer" | xclip -selection clipboard -in -target text/html
It is probably not the prettiest script (the verbose style directives make it fairly hard to read), but it gets the job done, and it frees me from creating those tables by hand.
A final word on compatibility. Most of the script is operating-system independent, except for the final part: copying the table to the clipboard. Doing this in Linux is easy enough with xclip, but I couldn’t find a way to do this on Mac. I probably spent longer trying to make this work on Mac than it took me to create the script in the first place. As a work-around, you can save the output to an HTML file, open it in a browser, and manually select and copy it from there.
—Written by Sebastian Jambor. Follow me on Mastodon @crepels@mastodon.social for updates on new blog posts.