I'm not getting all the tickets from the Jira api

Pablo Canto
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
March 5, 2025

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?

The script follows this structure (forget about the names) Thanks a lot!!

 

 

function getJiraData() {
  const jiraDomain = 'https://mycompany.atlassian.net'; // Tu dominio de Jira
  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;
        }
      });
    }

    const jiraLink = `https://feverup.atlassian.net/browse/${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']);
    }

 

    if (includeInCdlBulk && cdlBulkIssues.length < totalResults) {
      cdlBulkIssues.push([key, status, summary, assignee, created, updated, planID, city, dueDate, launchID, jiraMadre, jiraLink]);
    }
  });

 

  if (bulk_1Issues.length > 0) {
    bulk_1Sheet.getRange(2, 1, bulk_1Issues.length, bulk_1Issues[0].length).setValues(bulk_1Issues);
  }
  if (Bulk_2Issues.length > 0) {
    Bulk_2Sheet.getRange(2, 1, Bulk_2Issues.length, Bulk_2Issues[0].length).setValues(Bulk_2Issues);
  }

 

  Logger.log('Datos de Jira volcados en JIRA_1 y JIRA_2 con éxito 🚀');
}

1 answer

0 votes
Tuncay Senturk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 5, 2025

Hi @Pablo Canto 

Please correct me if I am missing anything but in your code totalResults variable being set to 1000, which could be limiting the number of issues retrieved. Your script iterates until startAt < totalResults (which is 1000), as of my understanding. 

If your project has more than 1000 issues, the script will stop fetching after reaching this limit, even though older issues exist. The gap of ~45-50 days could be because the first 1000 most recent issues fit within that range.

Remove or increase totalResults and test again if possible.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events