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.");
}