This article explains how to retrieve data from an Excel file stored on SharePoint by using the SharePoint API in combination with third-party Atlassian marketplace apps or some type of custom scripts. This integration can be highly beneficial, enabling your organization to leverage external data directly within Jira, streamlining workflows and decision-making.
Third-party apps
Two popular apps available on the Atlassian Marketplace that allow you to use external data in Jira custom fields are Elements Connect and External Data for Jira Fields. In this article, we will use External Data for Jira Fields as an example to demonstrate how to set up this integration with SharePoint.
Register new app in Azure
The first step is to register a new app in Azure. Fortunately, the External Data for Jira Fields app provides detailed documentation that makes this process straightforward. For more information, refer to their documentation, and you can also consult Microsoft’s quickstart guide on registering an app. Make sure to save your Client ID, Client Secret and Tenat ID.
NOTE: If you choose Sites.Selected for API permissions instead of Sites.Read.All, ensure that you grant at least View permissions to your new app for the selected sites. This step is crucial to avoid permission issues when accessing the required data.
Authentication
To interact with the Microsoft Graph API, you will need an authorization token, which can be obtained by making a request to the following endpoint:
https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/tokenThe Tenant ID (which was generated when registering the app in Azure) is required in this endpoint, along with other key values such as the Client ID, Client Secret, and Scope. All of these steps are well-explained in the External Data for Jira Fields app documentation.
Below is a simple Python code snippet demonstrating how to retrieve an authorization token:
tenat_id = "your_tenat_id"
client_id = "your_client_id"
client_secret = "your_client_secret"
# Authentication URL
auth_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
# Set the scope to access Microsoft Graph
scope = ["https://graph.microsoft.com/.default"]
# Authentication request
response = requests.post(auth_url, data={
'grant_type': 'client_credentials',
'client_id': client_id,
'client_secret': client_secret,
'scope': ' '.join(scope),
})
token = response.json().get("access_token")
print(f"Access token is: {token}")When configuring authentication in the app, you can click the Authenticate button to verify if the authentication is successful.
If you want to use the token in custom scripts, you can include it with the Bearer scheme as shown in this Python example:
headers = {
'Authorization': f'Bearer {token}',
'Content-Type': 'application/json',
}Configure Data Source
Once authentication is successful, you can start configuring the URL to retrieve data from the Excel file. This requires calling a few endpoints to collect the necessary data before constructing the final endpoint. You can use Python to make these API calls or a tool like Postman. Once you’ve constructed the final endpoint, simply enter it into the URL field as the data source in the External Data for Jira Fields app.
NOTE: The first two API calls are documented in the External Data for Jira Fields documentation.
- Retrieve the Site ID: To get the ID of the SharePoint site where your file is stored, use the following endpoint:
https://graph.microsoft.com/v1.0/sites/{host-name}:/{server-relative-path}In the JSON response, look for the id field and save this value. This is your Site ID.
- Retrieve the Drive (Document Library) ID: After you have the Site ID, retrieve the drive (document library) associated with your site by calling:
https://graph.microsoft.com/v1.0/sites/{site-id}/drivesYou will again receive an id in the response. Save this as your Drive ID.
- Retrieve the File ID for the Excel File: To get the File ID for your Excel file, use the following endpoint:
https://graph.microsoft.com/v1.0/drives/{drive_id}/root/childrenLook for the file by its name in the response and save its corresponding id.
- Retrieve the Worksheet ID: With the File ID from the previous step, retrieve the worksheet(s) in the file:
https://graph.microsoft.com/v1.0/sites/{site_id}/drive/items/{file_id}/workbook/worksheetsThis will return the worksheet names and their IDs. Identify the worksheet containing the data you need and save its id.
- Construct the Final Endpoint: After collecting the Site ID, File ID, and Worksheet ID, you can now construct the final endpoint to use as your data source. To retrieve all used cells in the worksheet (i.e., the range of cells that contain data), use the following:
https://graph.microsoft.com/v1.0/sites/{site_id}/drive/items/{file_id}/workbook/worksheets/{worksheet_id}/usedRangeThis URL will fetch the data from the Excel file, and you can now use it as the data source for External Data for Jira Fields app of some custom scripts.
Configure custom fields
When using third-party apps, it’s essential to configure custom fields that pull data from your external data source. A common approach is to create a Select List field, where the options are populated directly from an Excel file. This setup allows you to seamlessly integrate external data into Jira, improving how your team interacts with and selects relevant information.
When configuring the Field Options – List type in the External Data for Jira Fields app, ensure you link it to your Jira custom field of type Select List (either single or multiple choice).
To define the available options in your custom field, use a JSON path to specify which Excel column will be used. For example, if you want the values from column A to populate the custom field options, your JSON path would be:
$.text[*][0]To change to a different column, simply adjust the index value accordingly.
NOTE: This process is also thoroughly explained in the app’s documentation.
Reach out!
I hope this article was helpful to you! If you have any additional questions, want to dive deeper into the topic, or have ideas for improvement, I’d love to hear from you.
You can find links to my LinkedIn profile and email at the bottom of the page, or feel free to reach out via the Contact page.
