Automatically Connect Jira Tickets to Google Sheets

Connect systems with Reshuffle.

If you’re using Jira, you know how it is to juggle dozens, if not hundreds, of tickets. Notifications pile up and tracking every new ticket gets complex, to say the least. Generating reports, and providing ongoing visibility for people who do not use Jira every day is also a big pain.

Well, if your company is using Google Sheets for reports and tracking, you can integrate Jira to it and set it up so that anytime a new Jira ticket is created, it will automatically update a Google spreadsheet. Now, all your Jira tickets will be in one easy to view spreadsheet and you can manage them in one place!

How? Simply use Reshuffle connectors to easily create the integration.

In this article, you will learn how to build a Jira to Google spreadsheet integration that lets people keep track of Jira tickets using an interface they’re already familiar with.

How to Build It

Reshuffle makes it easy to build integrations that complete complex tasks, so you can deliver unique experiences and outcomes for the business and for customers.

Reshuffle is an open source, lightweight, and event-driven framework that helps you integrate services — these integrations and workflows are created inside a Reshuffle App. The objects that let you interact with these services are called connectors.

In this example, you'll see a simple way to create a two way sync between Jira and Google Sheets. New issues created on Jira will add a new row to the spreadsheet containing the basic fields from the Jira issue. In turn, updates from the Google spreadsheet will also update the issue on Jira's end.

See links for full documentation:

• Atlassian Jira Connector
• Google Sheets Connector

Let's get started by instantiating a Reshuffle app and the two connectors listed above.

const app = new Reshuffle()
// Jira Config
const jira = new JiraConnector(app, {
  host: process.env.JIRA_HOST,
  protocol: process.env.JIRA_PROTOCOL, // 'http' or 'https'
  username: process.env.JIRA_USERNAME, // username or email
  password: process.env.JIRA_TOKEN,
  baseURL: process.env.RUNTIME_BASE_URL,
})

// Google Sheets Config
const googleSheets = new GoogleSheetsConnector(app, {
  credentials: {
    client_email: process.env.GOOGLE_CLIENT_EMAIL,
    private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
  },
  sheetsId: process.env.GOOGLE_SHEET_ID,
})

Now that we have the connectors configured, we need to define the event we want to listen to, using the connectors on() method. For Jira, we will be listening to any new issues created. We will extract the issue's id, summary, description and status. We will then use the Google Sheets Connector to add these fields to our spreadsheet.

jira.on({ jiraEvent: 'jira:issue_created' }, async (event, app) => {
  //Extract Jira fields from event
  const {
    id,
    fields: {
      summary,
      description,
      status: { name },
    },
  } = event.issue

  //Use google sheets connector to add these fields as a row in the spreadsheet
  const sheetId = 0
  const values = [
    id,
    summary,
    description,
    name,
    event.issue.fields.assignee
      ? event.issue.fields.assignee.displayName
      : 'Not Assigned',
  ]
  await googleSheets.addRow(sheetId, values)
})

The next step is turning this example into a two way sync. To achieve that, we will set up a second event to listen to with the Google Sheets Connector. The connector will listen to changes made to the spreadsheet and allow us to process them.

Notice the empty {}, no need to give it options but see documentation for all that are available.

Inside of the handler, we will only process the changes made to an existing row. We can extract the previous and current row inputs to compare later on.

googleSheets.on({}, async (event, app) => {
  if (event.worksheetsChanged[0] && event.worksheetsChanged[0].rowsChanged[0]) {
    const { curr, prev } = event.worksheetsChanged[0].rowsChanged[0]

    //additional code to follow
  }
})

To keep track of the changes, we’re using an object to store the summary and description fields. Updating the status field will require more work, so we will need to find the transition IDs using a separate Jira connector action.

// continuing from the code above
let updates = { fields: {} }
// Find transition Ids
let transitionId = {}
const { transitions } = await jira.sdk().listTransitions(curr['Issue ID'])
for (const status of transitions) {
  if (!transitionId[status.name]) {
    transitionId[status.name] = status.id
  }
}
// additional code to follow

We can now iterate through the inputs and check if anything has changed by comparing the values.

for (const key in prev) {
  //skipping assignee and issue id fields in this example
  if (prev[key] !== curr[key]) {
    if (key === 'Assignee' || key === 'Issue ID') {
      continue
    }
    if (curr[key] in transitionId) {
      transitionId = { id: transitionId[curr[key]] }
      await jira.sdk().transitionIssue(curr['Issue ID'], {
        transition: {
          id: transitionId.id,
        },
      })
    } else {
      updates = {
        fields: { ...updates.fields, [[key.toLowerCase()]]: curr[key] },
      }
    }
  }
}
// updates description/summary field
await jira.sdk().updateIssue(curr['Issue ID'], updates)

Lastly, let's initiate the integration by starting the Reshuffle App:

app.start()

See how easy it is? You can do this so quickly and really make it much easier to track new Jira tickets in Google Sheets bidirectionally. Check out the video demo to see it in action!

Now, Make it Happen

As your developers and project management teams experience the ease of working with integrated applications, we encourage you to consider where else integrating workflows would benefit your teams. With so many different tools, the more you can consolidate them into one common interface, the easier people can get work done.

Reshuffle is continually listening to what our customers need and desire. Don't see a Connector to a service you'd like to integrate? Send a tweet to @ReshuffleHQ to let us know which Connector you'd like us to develop next.