Background:

While the easiest way to connect Google Sheets to Jira is with the Jira Cloud add-on for Google Sheets, for niche use cases where you want more control, it may be worth setting up a custom app in the developer console so that you can access the Jira API using OAuth 2.0 (3LO) authentication from within a Google App Script.

How-to:

1. Start by navigating to the Developer Console
2. Click Create → Oauth 2.0 integration
3. Give the new app a name, read and agree to the developer terms then click Create

   
   

   

4. Select Authorization from the menu → next to Oauth 2.0 (3LO) click Add
5. Pause here and open a new browser tab
6. Create a new Google Sheet and give it a recognizable name

7. In the menu pane, select Extensions → Apps Script and give the Apps Script a recognizable name

   

8. In the left-side menu, click Libraries to add a new library

9. Use the Script ID below to load the Google's OAuth 2.0 Library:

   Script ID: apps-script-oauth2

   1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

10. Leave the rest of the options as default and click Add

    
    

    

11. Now, click the gear icon in the left-side menu to navigate to the App project settings; locate your Script ID and take note of it for later.

    

Switch back to your Atlassian Developer Console

1. If you navigated away from the Authorization screen, go to Console → My Apps → name_of_your_app → Authorization

2. Enter the following for the Callback URL (where SCRIPT_ID is replaced with the script ID you took note of in step 12 above):

   コールバック URL

   https://script.google.com/macros/d/SCRIPT_ID/usercallback

   

3. Click Save Changes
4. Next, navigate to the Permissions section using the left-side menu
5. Next to where it says Jira API, click Add
6. Wait a moment and the button should change to Configure, then click Configure → Edit Scopes

7. Click View user profiles (read:jira-user)

    The read:jira-user permission is needed for the example in this article but for your own app you'll want to add whichever permission(s) your app will need.

8. [保存] をクリックします。

9. Next, navigate to the Settings section and take note of your Authentication details (Client ID & Secret)

   

Switch back to your Google App Script

1. Copy the Jira Example Script in Google's Repository and paste it into your own Google App Script console

2. In the script you just pasted, replace the ellipses (...) in the following variables with the values you saved from your atlassian developer console app in step 9 above

   Variables

   var CLIENT_ID = '...';
   var CLIENT_SECRET = '...';

3. Now select the run() function from the drop down and click the Run button in the app script editor.

   

4. A modal box will pop up asking for Authorization. Click Review permissions

   
   

   

5. A new browser pop-up window will appear asking you to select an Atlassian account to sign into

   

6. Select your account

7. Review the permissions then click Allow

   

8. You may get a message like the following in your Google App Script console:

   

9. Copy the URL from the console and paste it into a new browser tab
10. From the dropdown, select the Atlassian Site you are trying to connect to and click Accept

    
    

    

11. Close the window, return to the App Script editor, and run the script again

12. If successful you should see JSON successfully returned by the API request