How to Pull Project-Level Data from Jira Cloud to Google Sheets

The Coupler.io team encountered many challenges. Initially, many users had been looking for a way to integrate Jira Cloud with Google Sheets. Now we have a Jira to Google Sheets importer to import their issues into spreadsheets. Some users wanted more, namely to pull issues from Jira Server. We found a bypass for them using the CSV importer. The next challenge was figuring out how to import project names, keys, categories, types, and other project-level data. Well, Challenge Completed! 

How to import Jira project-level data into Google Sheets

Install Coupler.io

Coupler.io is a Google Sheets add-on to automatically import data from third-party data sources, such as Jira, Xero, BigQuery, and many more. Install Coupler.io from the Google Workspace Marketplace. This is how it looks:

Set up a JSON Client importer

An importer is an integration that connects Google Sheets to a specific app. JSON Client Importer lets you import JSON data from HTTP APIs. It’s a powerful tool that you can use for syncing Google Sheets with different apps that have a RESTful API. In the blog post How to Import JSON to Google Sheets Without Coding, we connected a spreadsheet to Trello and Typeform without a hitch. Let’s do the same with Jira Cloud.

Open Coupler.io in the Add-ons tab, click on the +Add Importer button, and fill in the following fields:

Required fields

Title – add the name of your data source. 

Sheet Name – type in the name of your sheet.  

JSON URL – insert the URL of your Jira endpoint. Here is the JSON URL for importing project-level data:

https://{domain-name}.atlassian.net/rest/api/2/project/search

• {domain-name} – replace with your Jira domain name

Check out Jira REST APIs documentation to learn more about how to query data from Jira. 

Automatic data refresh – enable this function and choose how often you want Coupler.io to update data in your spreadsheet.

Additional fields 

HTTP Method – pick GET as the HTTP method to make requests from Jira.

HTTP headers – specify the following HTTP headers for your Jira request:

Authorization: Basic {encoded-api-token}
Content-Type: application/json

To authorize successfully, you must have permission to browse or administer projects. Read Managing project permissions in Jira to learn more.

Where do I get {encoded-api-token}? 

• Create an API token as follows:
  • Go to Account settings => Security => Create and manage API tokens (or use this direct link)
  • Click Create API token, type in the name of your token in the Label field and confirm by clicking Create
  • Copy to clipboard your new API token

• Encode your API token to Base64 format 
  • Use a dedicated tool for encoding such as, base64encode.org or base64encode.net.
  • Create a string {your-email}:{your-API token} and encode it in Base 64. For example, if you encode the string test@example.com:DOF32jA2VkmtzdAqxHyK32AB, you’ll get the following: dGVzdEBleGFtcGxlLmNvbTpET0YzMmpBMlZrbXR6ZEFxeEh5SzMyQUI= 
• Copy the encoded string and paste it into the Authorization HTTP header. You should get the following:

Authorization: Basic dGVzdEBleGFtcGxlLmNvbTpET0YzMmpBMlZrbXR6ZEFxeEh5SzMyQUI=
Content-Type: application/json

Note: We recommend you use text editors like Notepad to copy the string from and paste it into the HTTP headers field. If you copy the string from Google Sheets, it may contain invisible characters between the lines. In this case, the authorization process may be infringed.

URL query string – use the following URL query string to specify the number of items to return per page:

maxResults: 20

Read the Jira REST APIs documentation to learn more about this.  

Leave unchanged other Additional fields (Request body, Fields, Path), as well as the Settings section. Check out the Coupler.io knowledge base to learn more about these fields.  

Once you’re done, click Save and Run.

Importing Jira issues or Jira project-level data – what’s the difference?

Exporting project-level data from Jira requires that you deal with Jira Rest API. That’s why we used the JSON Client importer above. To export Jira issues from a particular project or projects, you use the Jira importer. It connects to your Jira account and pulls issues according to your search filters. If you still don’t know how to connect Jira Cloud to Google Sheets, check out our blog post dedicated to that process. 

The Jira importer works only for Jira Cloud. Because of this, Jira Software Server users can’t use it for data import. However, they can pull issues from Jira Server using a properly configured CSV importer. For more on this, read How to Import Issues from Jira Server to Google Sheets.

To wrap up

It was rather simple, wasn’t it? It took about three minutes to install Coupler.io, set up the JSON Client importer for Jira, and import project-level data into Google Sheets. You can also use the Automatic data refresh feature and your spreadsheet will sync with Jira on a set schedule – every day, for example. What you do with this data is up to you. Good luck!

Kudos to Mr. Ross Beyeler for his request, which kickstarted us to elaborate on this use case.