How to integrate data into Google Spreadsheet?

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. 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.

That’s all! The trigger is added to the list. You can add as many triggersas you need.

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! >>>