Guide: Google Sheets to Jira issues

This guide demonstrates the use of Yetibot to consume a Google Sheet as a CSV and create Jira issues from the rows.

CSV consumption

We'll use this Yetibot tasks Google Sheet. To get the sheet in CSV format, just change the end of the URL from edit?usp=sharing to export?format=csv:

!curl https://docs.google.com/spreadsheets/d/1JIp3AjmPIA7T2aJsXPDaz7KxyxJLJGbgkR6r_dpvu_E/export?format=csv

We can consume that URL with the csv command.

!help csv

!csv https://docs.google.com/spreadsheets/d/1JIp3AjmPIA7T2aJsXPDaz7KxyxJLJGbgkR6r_dpvu_E/export?format=csv

Let's alias that so it's a little more readable:

!alias yetitasks = "csv https://docs.google.com/spreadsheets/d/1JIp3AjmPIA7T2aJsXPDaz7KxyxJLJGbgkR6r_dpvu_E/export?format=csv"

Then make sure it works as expected:

!yetitasks

Towards Jira

Next, we need to figure out how to pipe that to jira.

We can take advantage of the render command, which lets us render templates against a data structure, like the above.

!help render

For example, if we only want to look at the Summary column:

!yetitasks | render {{Summary}}

Let's look at jira create docs to see how we can construct it dynamically:

!help jira | grep create

So at a minimum, it'd look like:

!yetitasks | render jira create {{Summary}}

But we can also take advantage of the other columns:

!yetitasks | render jira create {{Summary}} -d {{Description}} -t {{Estimate}} -c {{Component}}

Finally, to actually run those commands, we can use the cmd command:

!help cmd

tldr

Put it all together:

!yetitasks | render jira create {{Summary}} -d {{Description}} -t {{Estimate}} -c {{Component}} | xargs cmd

🔥

To clean up we can use jira jql to query issues created in the last hour:

!jira jql created > -1h

And delete them:

!jira jql created > -1h | xargs jira parse | xargs jira delete

🔥

Power

Hopefully this guide has illustrated the power of | pipes, xargs, render and cmd. They are foundational pieces of Yetibot's expressive pipeline capabilities.

Have ideas for other guides? Post a comment below!