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

[Pablo Canto]

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 🚀');

}

[Tuncay Senturk]

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.

[Pablo Canto]

first of all, thanks for your reply! I don't think that's the problem since I'm just getting 20 tickets if I order them asc or around 550 if I order them desc in date of creation. I've tried to change that 1000 number and even with a higher number, it doesn't bring me all the Jiras I expected

[Tuncay Senturk]

I see!

Can you please replace that function with the one below and try? See the logs on what's going on.

function fetchJiraIssues() {
   let startAt = 0;
   let allIssues = [];
   let totalResults = null;

   while (true) {
      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'
        }
      };

     try {
       const response = UrlFetchApp.fetch(url, options);
       const data = JSON.parse(response.getContentText());

       if (totalResults === null) {
         totalResults = data.total; 
         Logger.log(`Total issues available: ${totalResults}`);
       }

       if (!data.issues || data.issues.length === 0) break;

         allIssues = allIssues.concat(data.issues);
         startAt += maxResultsPerPage;

         if (startAt >= totalResults) break;

       } catch (e) {
         Logger.log("Error fetching Jira issues: " + e.message);
         break;
      }
   }

   return allIssues;
}