| Info |
|---|
Note that the instruction below is actual only for Version 1 of JSON Data Feed link. By default, the JSON Data Feed link is created and works in the second version mode. To switch between the 2 versions of JSON Data Feed link, you should replace v1 with v2 (and vice versa) in the link. |
...
Open the Apps Script extension's table window. Create a function to generate the table from the data in the Data Feed using the start trigger "when opening the document".
In the table window in the Apps Script extension, create a function for table generating from the data received from the Data Feed with the trigger "when the document is opened".
Open the Apps Script interface in Google Sheets.
...
You’ll get such a table:
...
2. Paste script here:
| Code Block |
|---|
// 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.getDate()}/${getMonth(date)}/${date.getFullYear()}`;
default: return issue.fields[field];
}
});
sheet.appendRow(
sortedStatuses.reduce((memo, { id }) => [...memo, (data[id] ? data[id] / 3600000 : '-')], issueData)
);
});
} |
| Info |
|---|
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 JSON Data Feed’s link.
...
4. Copy the generated link:
...
| Info |
|---|
To make it work properly, you should use the JSON Data Feed link v.1 |
5. And insert it in the system:
...
6. Save code.
...
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.
...
An additional step is to set the triggers to update the information in the table. To do that, you need to:
First of all, click triggers.
...
2. Click the “Add trigger” button.
...
3. From the “Choose with function to run” drop-down list select “dataFeed”.
...
4. Select necessary event source from the drop-down list (this instruction is based on the time-driven event).
...
5. Set up timer parameters by selecting type of time-based trigger. Click a Save button.
...
Load and copy the doc (it’s the template document in this use case): https://docs.google.com/spreadsheets/d/1atEE4DNdluGtQLvP8YR-Sim-oEHK-DKL_TXXNLHeYRI/edit?usp=sharing
...
This yellow warning is the necessary file of properly Data Feed work. You just need to clik the “Make a copy” button.
Then go to
Extensions and choose → Apps Script .
...
Deploy the Data Feed script as it’s shown below:
Click the Delpoy button and choose New deployment from the drop-down list.
...
Check information in the Configuration pop-up and click the Deploy button.
...
Authorize access to your data.
...
Click the Allow button.
...
Click the Done button to finish the deployment.
...
Add scheduling for updating results.
add trigger, (2 screen), 3 screen - result
...
Click the Save button.
...
Here’s the result you have got.
...
Then you should return to the document and
delete the Settings tab.
...
Press
F5 button or reload a page.
When the page is loaded, you will see a pop-up where you need to insert the
Data Feed link (the link that is generated in the add-on).
...
When you insert the link and press the
Save button, the report will be loaded. Then, you can go to the Report tab in your document and get a result.
...
| Info |
|---|
If you need help or want to ask questions, please contact SaaSJet Support or email us at support@saasjet.atlassian.net |
...