Setting Up a Custom Developer Console App to Connect Google Sheets to JSW Cloud API using OAuth 2.0 (3LO)

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform Notice: Cloud Only - This article only applies to Atlassian products on the cloud platform.

Background:

While the easiest way to connect Google Sheets to Jira Software 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 CreateOauth 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 ExtensionsApps 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):

    Callback 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 ConfigureEdit 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. Click Save
  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



Last modified on Sep 9, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.