How to integrate data into Power BI?

Open the App Script extension's table window. Create a function to generate the table from the data in the Data Feed using the "when opening the document" start trigger.

In the table window of the Apps Script extension, create a function for table generating from the data received from the Data Feed with the "when the document is opened" trigger. 

  1. Open the Apps Script interface in the Google Sheets.

    You’ll get such a table:

  2. Paste this script here:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 // App Script const DATA_FEED_URL = '<insert_url>'; const COLUMNS = [{ label: 'Issue Key', field: 'key' },{ label: 'Created', field: 'created' },{ label: 'Summary', field: 'summary' },{ label: 'Project', field: 'project' },{ label: 'Assignee', field: 'assignee' }]; const getMonth = (date) => date.toLocaleString('default', { month: 'short' }); function dataFeed() { const response = UrlFetchApp.fetch(DATA_FEED_URL); const json = response.getContentText(); const { result, statuses, issues } = JSON.parse(json); const sheet = SpreadsheetApp.getActiveSheet(); sheet.clear(); const sortedStatuses = statuses.sort(({ id: a}, { id: b }) => a > b ? 1 : a != b ? -1 : 0); const labels = COLUMNS.map(({ label }) => label); const header = sortedStatuses.reduce((memo, { label }) => [...memo, label], labels); sheet.appendRow(header); Object.entries(result) .forEach(([issueKey, data]) => { const issue = issues.find(({ key }) => key === issueKey); const issueData = COLUMNS.map(({ field }) => { switch (field) { case 'key': return issue.key; case 'project': return issue.fields.project.name; case 'assignee': return issue.fields.assignee ?? 'Unassigned'; case 'created': const date = new Date(issue.fields.created); return `${date.getDay()}/${getMonth(date)}/${date.getFullYear()}`; default: return issue.fields[field]; } }); sheet.appendRow( sortedStatuses.reduce((memo, { id }) => [...memo, (data[id] ? data[id] / 3600000 : '-')], issueData) ); }); }

Note. When opening/renewing the document, the script generates a similar table with current data.

3. When you have pasted a copied code, you need to insert a Data Feed’s link.

4. Copy the generated link of your report in the add-on:

5. And insert it in the system:

6. Save the project:

7. In the drop-down menu, select dataFeed:

8. Run the code.

That’s all! This is how the necessary data will be displayed in your Google Spreadsheets.

 

How do I set up regular data updates?

An additional step is to set triggers to update the information in the table.

Go to Triggers and click Add Trigger.

Select dataFeed in the drop-down menu as a function to run, and select Timedriven as an event source.

 

How to integrate data into Power BI?

Copy the Google spreadsheet URL with data and go to the Power BI.

1. Click Get data and select More:

2. Find the Google Sheets in the list and click Connect.

Paste the Google Spreadsheet URL with data that you have copied at the beginning.

Select the necessary Sheet and click Load:

That's all! Now you can build reports in Power BI using data from the Time in Status for Jira Cloud add-on.

 

If you need help or want to ask questions, please contact SaaSJet Support or email us at support@saasjet.atlassian.net

Haven't used this add-on yet? Try it now! >>>