I'm trying to fetch the Jira tickets from a project using a Google Sheets script, but it's only bringing in tickets with a gap of about a month and a half. No matter how I sort or filter by date, it stops after around 45-50 days between the first and last ticket it retrieves, regardless of the number of tickets fetched. What could be causing this?
function getJiraData() {
const jiraUser = 'myemail'; // Tu email
const apiToken = 'my token'; // Tu API Token
const maxResultsPerPage = 90;
const totalResults = 1000;
let bulkIssues = [];
let cdlBulkIssues = [];
let bpBulkIssues = [];
// 🔹 Asegurar que today está en UTC (00:00:00)
const today = new Date();
today.setUTCHours(0, 0, 0, 0);
const futureDate = new Date();
futureDate.setUTCDate(today.getUTCDate() + 30);
futureDate.setUTCHours(23, 59, 59, 999); // 🔹 Último segundo del día 30
function fetchJiraIssues() {
let startAt = 0;
let allIssues = [];
while (startAt < totalResults) {
const url = `${jiraDomain}/rest/api/2/search?jql=project=CRM ORDER BY created DESC&maxResults=${maxResultsPerPage}&startAt=${startAt}`;
const options = {
method: 'get',
headers: {
Authorization: 'Basic ' + Utilities.base64Encode(jiraUser + ':' + apiToken),
'Accept': 'application/json'
}
};
const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText());
if (data.issues.length === 0) break;
allIssues = allIssues.concat(data.issues);
startAt += maxResultsPerPage;
}
return allIssues;
}
const allIssues = fetchJiraIssues();
const ss = SpreadsheetApp.getActiveSpreadsheet();
let bulkSheet = ss.getSheetByName("JIRA_1") || ss.insertSheet("JIRA_1");
let cdlBulkSheet = ss.getSheetByName("JIRA_2") || ss.insertSheet("JIRA_2");
let bpBulkSheet = ss.getSheetByName("JIRA_3") || ss.insertSheet("JIRA_3");
bulkSheet.clear();
cdlBulkSheet.clear();
bpBulkSheet.clear();
const headers = [
'Campo 1', 'Campo 2', 'Campo 3', 'Campo 4', 'Campo 5', 'Campo 6', 'Campo 7',
'Campo 8', 'Campo 9', 'Campo 10', 'Campo 11', 'Campo 12'
];
bulkSheet.appendRow(headers);
cdlBulkSheet.appendRow(headers);
bpBulkSheet.appendRow(['Campo 1', 'Campo 2', 'Campo 3', etc...]);
allIssues.forEach(issue => {
const key = issue.key;
const status = issue.fields.status.name;
const summary = issue.fields.summary;
const assignee = issue.fields.assignee ? issue.fields.assignee.displayName : 'No asignado';
const created = issue.fields.created;
const updated = issue.fields.updated;
const planID = issue.fields.customfield_11221 ? "'" + issue.fields.customfield_11221.toString() : 'No definido';
let city = 'No especificado';
if (issue.fields.hasOwnProperty("customfield_11219") && issue.fields.customfield_11219 !== null) {
if (Array.isArray(issue.fields.customfield_11219)) {
// 🔹 Si es una lista, concatenamos los valores separados por ", "
city = issue.fields.customfield_11219.map(item => item.value).join(", ");
} else {
// 🔹 Si es un solo valor, lo guardamos directamente
city = issue.fields.customfield_11219.value;
}
}
Logger.log("Cities (corregidas y concatenadas): " + city);
const dueDate = issue.fields.duedate || 'No especificado';
let jiraDueDate = null;
if (dueDate !== 'No especificado') {
jiraDueDate = new Date(dueDate);
jiraDueDate.setUTCHours(0, 0, 0, 0); // 🔹 Convertir a UTC 00:00:00
}
const isPastSendDate = jiraDueDate && jiraDueDate < today;
const isFutureSendDate = jiraDueDate && jiraDueDate >= today;
const launchID = issue.fields.customfield_11832 || 'No especificado';
let jiraMadre = 'No tiene';
if (issue.fields.issuelinks) {
issue.fields.issuelinks.forEach(link => {
if (link.outwardIssue) {
jiraMadre = link.outwardIssue.key;
}
});
}
let includeInBulk = !(assignee === "Carmen" || status === "Finalizada" && (isPastSendDate || dueDate === 'No especificado') || isPastSendDate || status === "Rechazado");
let includeInCdlBulk = ["Leticia", "Carmen"].includes(assignee) && isFutureSendDate && status !== "Finalizada" && status !== "Rechazado";
let includeInBpBulk = assignee === "Leticia" && isFutureSendDate;
if (includeInBulk && bulkIssues.length < totalResults) {
bulkIssues.push(['Campo 1', 'Campo 2', 'Campo 3', 'Campo 4', 'Campo 5', 'Campo 6', 'Campo 7', 'Campo 8', 'Campo 9', 'Campo 10', 'Campo 11', 'Campo 12']);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.