Can't import Jira ticket in Google Sheet

[Sandy Hsieh]

Hi there

I’d like to ask if anyone has encountered this issue: previously, I used the following Google App Script with a Jira API token to successfully import specific Jira content into a Sheet automatically. However, recently, when running it, I started getting the error message below. I also checked with ChatGPT, severals fix still shows error 😞

Could anyone help me understand the reason and advise how to adjust the script so the data can be imported into the Sheet smoothly again? 🙏🏻

❌ Error fetching Jira issues: DNS error: https://Jira-domain/rest/api/3/search?jql=assignee%20IN%20(5d36a43330951f0c8bd66636%2C712020%3A95c3dcdd-19e7-462b-aa90-bbeff3ae8676%2C712020%3A4dddcd09-3250-4a73-a721-f9e03f26c5e8%2C712020%3A3bc49f56-12aa-4ed9-9dbe-66a92738089e%2C712020%3A760e199f-ee91-4769-bd5c-22db498785cb%2C712020%3A6f773e21-3f1a-4e3f-8660-882784389d00%2C712020%3Af8039fab-91f0-4316-a017-b47a68d1d07c%2C712020%3A83979f4e-28e0-457a-913c-95f21f9c1242%2C62d4d05c657fc166e25f4b01%2C712020%3A993bcb39-64e8-41a7-9b17-9b19ec058e31%2C712...

-------------------------------------------------
Original google app script

function storeJiraToken() {
const token = 'XXX';
PropertiesService.getScriptProperties().setProperty('JIRA_TOKEN', token);
Logger.log("✅ Jira token has been saved.");
}

function importJiraTickets() {
const email = 'Personal email';
const domain = 'Jira-domain';
const jql = 'assignee IN (5d36a43330951f0c8bd66636,712020:95c3dcdd-19e7-462b-aa90-bbeff3ae8676,712020:4dddcd09-3250-4a73-a721-f9e03f26c5e8,712020:3bc49f56-12aa-4ed9-9dbe-66a92738089e,712020:760e199f-ee91-4769-bd5c-22db498785cb,712020:6f773e21-3f1a-4e3f-8660-882784389d00,712020:f8039fab-91f0-4316-a017-b47a68d1d07c,712020:83979f4e-28e0-457a-913c-95f21f9c1242,62d4d05c657fc166e25f4b01,712020:993bcb39-64e8-41a7-9b17-9b19ec058e31,712020:587dfd30-a9a8-4999-9c12-ae0bb627ad1b,712020:89a72696-9d4c-4dc0-bf63-13d133496b32,712020:f89bc644-342b-49d0-b8ff-d5aed689479c) AND statusCategory IN ("To Do", "In Progress") AND updated >= -30d ORDER BY created DESC';

const apiToken = PropertiesService.getScriptProperties().getProperty('JIRA_TOKEN');
if (!apiToken) {
Logger.log("❌ No Jira token found. Please run storeJiraToken() first.");
return;
}

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AllDesigners30DaysTicket");
if (!sheet) {
Logger.log("❌ Sheet 'AllDesigners30DaysTicket' not found.");
return;
}

sheet.clearContents();
sheet.appendRow(["Project", "Key", "Summary", "statusCategory", "Assignee", "Created", "Start Date", "Updated", "Due Date", "Priority"]);

let startAt = 0;
const maxResults = 100;
let total = 0;

do {
const url = `https://${domain}/rest/api/3/search?jql=${encodeURIComponent(jql)}&startAt=${startAt}&maxResults=${maxResults}`;
const headers = {
"Authorization": "Basic " + Utilities.base64Encode(email + ":" + apiToken),
"Accept": "application/json"
};

try {
const response = UrlFetchApp.fetch(url, { "headers": headers });
const data = JSON.parse(response.getContentText());

if (startAt === 0) {
total = data.total;
Logger.log(`🔄 Fetching ${total} issues from Jira...`);
}

const issues = data.issues;
issues.forEach(issue => {
const key = issue.key;
const [projectCode] = key.split("-");
sheet.appendRow([
projectCode,
key,
issue.fields.summary,
issue.fields.statusCategory.name,
issue.fields.assignee ? issue.fields.assignee.displayName : "Unassigned",
issue.fields.created ? issue.fields.created.substring(0, 10) : "",
issue.fields.customfield_10015 ? issue.fields.customfield_10015.substring(0, 10) : "",
issue.fields.updated ? issue.fields.updated.substring(0, 10) : "",
issue.fields.duedate ? issue.fields.duedate.substring(0, 10) : "",
issue.fields.priority ? issue.fields.priority.name : "None"
]);
});

startAt += issues.length;
} catch (e) {
Logger.log("❌ Error fetching Jira issues: " + e.message);
break;
}
} while (startAt < total);

Logger.log("✅ Jira import complete.");
}

[Trudy Claspill]

Hello @Sandy Hsieh 

The API endpoint you are using has recently been deprecated/deactivated.

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-get

You need to use the replacement endpoint:

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-jql-get

Rather than

/search?jql=

...you need to use

/search/jql?jql=

[Sandy Hsieh]

Understood. Thanks for the information. 

[Trudy Claspill]

You're welcome.

Please let us know if making that change resolves your issue.

If it does, please consider clicking the Accept Answer button to mark you Question as Solved. That helps others searching the forums find posts with validated answers.

If the suggestion doesn't solve your issue, please let us know so that we can ask more questions about your scenario and offer alternate suggestions.