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:
// 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)
);
});
}
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:
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.
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! >>>