Pagination with Lever API in Google Apps Script

I do not know JavaScript and have limited experience with Google Apps Script, but I need to connect a Google Sheet to my company's Lever ATS through the Lever Requisitions API. After a lot of trial and error, I managed to coax ChatGPT to help me write a functioning script, however it fails to address pagination, so I only get 50 lines of data. ChatGPT has been pretty useless with pagination, partly because of the length of the script, which currently consists of four functions:

1. a formatDate function to convert date values to yyyy-MM-dd hh-mm-ss datetime format
2. a getColumnIndex function which returns the column index for each value in an array of headers
3. the fetchLeverRequisitions function which is the main function of the script and actually returns data
4. getUserById function which converts certain values from ID numbers into user names.

I think the minor functions are all fine, it's the fetchLeverRequisitions function that's giving me issues. I've tried to add in a do...while loop to address pagination, but to no use - instead all I get is a row of headers with no additional data.

It may have something to do with one of the headers - customFields, which unlike every other header is an object that contains multiple values which need to be parsed out as headers in their own right, and need to have their own date formatting performed separate from that of other date values.

These customFields headers are contained in columnHeaders array, and consist of everything that follows customFields in the array.

My latest attempt is below, and if I need to add more here or make further edits to be in line with community standards, please just let me know - happy to make whatever edits needed.

Lever API documentation is here: https://hire.lever.co/developer/documentation

Code is below, with the API key removed. It successfully returns all headers, but doesn't give me any data.

function formatDate(date) {
  var formattedDate = "";
  if (date) {
    var timestamp = new Date(date);
    var year = timestamp.getFullYear();
    var month = ("0" + (timestamp.getMonth() + 1)).slice(-2);
    var day = ("0" + timestamp.getDate()).slice(-2);
    var hours = ("0" + timestamp.getHours()).slice(-2);
    var minutes = ("0" + timestamp.getMinutes()).slice(-2);
    var seconds = ("0" + timestamp.getSeconds()).slice(-2);

    formattedDate = year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds;
  }
  return formattedDate;
}

// Define the column headers
var columnHeaders = [
  "id",
  "name",
  "requisitionCode",
  "headcountTotal",
  "headcountHired",
  "backfill",
  "status",
  "team",
  "location",
  "hiringManager",
  "owner",
  "createdAt",
  "updatedAt",
  "closedAt",
  "internalNotes",
  "reqtype",
  "customFields",
  "contract",
  "reqstart",
  "reqtrigger",
  "trigdate",
  "reqassign1",
  "assigndate1",
  "reqassign2",
  "assigndate2",
  "reqassign3",
  "assigndate3",
  "result",
  "resultdate",
  "hireOwner",
  "newstart",
  "origin",
  "source",
  "exit",
  "notice",
  "departname",
  "newname",
  "tech"
];

// Get the column index based on the column header
function getColumnIndex(columnHeader) {
  for (var i = 0; i < columnHeaders.length; i++) {
    if (columnHeaders[i].toLowerCase().replace(/ /g, "") === columnHeader) {
      return i;
    }
  }
  return -1;
}

function fetchLeverRequisitions() {
  const leverAPIKey = "**************";
  const endpoint = "https://api.lever.co/v1/requisitions";
  const pageSize = 100;
  var offset = 0;
  var allRequisitions = [];
  var requisitions; 

  var headers = {
    "Authorization": "Basic " + Utilities.base64Encode(leverAPIKey),
    "Accept": "application/json"
  };

  var options = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true
  };

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents(); // Clear existing data

  // Write column headers to the sheet
  for (var i = 0; i < columnHeaders.length; i++) {
    sheet.getRange(1, i + 1).setValue(columnHeaders[i]);
  }

    do {
  var pageEndpoint = endpoint + "?limit=" + pageSize + "&offset=" + offset;
  const response = UrlFetchApp.fetch(pageEndpoint, options);

  if (response.getResponseCode() === 200) {
    const responseData = JSON.parse(response.getContentText());
    requisitions = responseData.data; // Assign the fetched requisitions

    if (requisitions.length > 0) {
      allRequisitions = allRequisitions.concat(requisitions);
      offset += pageSize;
    } else {
      break; // No more requisitions to fetch
    }
  } else {
    Logger.log("Failed to retrieve requisitions from Lever API. Response code: " + response.getResponseCode());
    break;
  }
} while (true);

// Write requisition data to the sheet
for (var i = 0; i < allRequisitions.length; i++) {
  var requisition = allRequisitions[i];
  for (var j = 0; j < columnHeaders.length; j++) {
    var columnHeader = columnHeaders[j];
    var value;

    if (columnHeader === "customFields") {
      var customFields = requisition[columnHeader];
      if (customFields) {
        for (var field in customFields) {
          var customFieldHeader = field.toLowerCase().replace(/ /g, "");
          var fieldValue = customFields[field];
          var customFieldIndex = getColumnIndex(customFieldHeader);
          if (customFieldIndex !== -1) {
            if (fieldValue instanceof Date) {
              value = formatDate(fieldValue);
            } else {
              value = fieldValue;
            }
            sheet.getRange(i + 2, customFieldIndex + 1).setValue(value);
          }
        }
      }
    } else if (columnHeader === "createdAt" || columnHeader === "updatedAt" || columnHeader === "closedAt") {
      value = formatDate(requisition[columnHeader]);
      sheet.getRange(i + 2, j + 1).setValue(value);
    } else if (columnHeader === "reqstart" || columnHeader === "trigdate" || columnHeader === "assigndate1" || columnHeader === "assigndate2" || columnHeader === "assigndate3" || columnHeader === "resultdate" || columnHeader === "newstart") {
      var customFieldHeader = columnHeader.toLowerCase().replace(/ /g, "");
      var customFieldIndex = getColumnIndex(customFieldHeader);
      if (customFieldIndex !== -1) {
        value = formatDate(requisition["customFields"][customFieldHeader]);
        sheet.getRange(i + 2, customFieldIndex + 1).setValue(value);
      }
    } else if (columnHeader === "hiringManager" || columnHeader === "owner") {
      value = getUserById(requisition[columnHeader]);
      sheet.getRange(i + 2, j + 1).setValue(value);
    } else {
      value = requisition[columnHeader];
      sheet.getRange(i + 2, j + 1).setValue(value);
    }
  }
}
}

function getUserById(userId) {
  const leverAPIKey = "**************";
  const userEndpoint = "https://api.lever.co/v1/users/" + userId;
  const headers = {
    "Authorization": "Basic " + Utilities.base64Encode(leverAPIKey),
    "Accept": "application/json"
  };
  var options = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true
  };
  var response = UrlFetchApp.fetch(userEndpoint, options);

  if (response.getResponseCode() === 200) {
    var responseData = JSON.parse(response.getContentText());
    return responseData.data.name; // Access the name property
  } else {
    Logger.log("Failed to retrieve user data for user with ID: " + userId);
    return null;
  }
}

Suggestion

> NOTE: This answer is intended to serve as a starting point or reference for your project. It's important to note that the community members do not provide coding services.

Based on your question, regardless of your hunch about the customFields headers, I understand that your main problem is with your fetchLeverRequisitions function's pagination handling. You cannot iterate to the next page of the returned API JSON result.

Not an expert in the Lever API, however when reviewing the Lever Developer documentation for Pagination, you need to use the parameter "offset" to retrieve the next page in another API request. This "offset" is a token value.

To address this issue, you may need to refactor your script and try these steps:

1. Capture the first set of result data from your API call.
2. Retrieve the token value and "hasNext" value from the response.
3. If the "hasNext" value is true, proceed to the next step.
4. Make a new API request, this time including the offset parameter with the value obtained from the current result (offset token value).
5. Repeat step 2 for the new result.
6. If the "hasNext" value of the current result is true, repeat step 4.
7. If the "hasNext" value of the current result is false, end the "do-while" loop.

Sample fetchLeverRequisitions Tweaked Script

const leverAPIKey = "■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■";
function fetchLeverRequisitions() {
var initialResult = initialAPICall();
var has_next = initialResult.hasNext;
var next_offset_token = initialResult.next;
var count = 1; //To count pages number for readability
//Get first data
//Sample console log for review
console.log(`Page #1 viewed with ${initialResult.data.length} number of data.\nSample first data ID: ${initialResult.data[0].id}`);
//console.log(initialResult);
do {
var current_data = loopedAPICall(next_offset_token);
count = count + 1;
//Get next data
//Sample console log for review
console.log(`Page #${count} viewed with ${current_data.data.length} number of data.\nSample first data ID: ${current_data.data[0].id}`);
//console.log(current_data)
has_next = current_data.hasNext;
next_offset_token = current_data.next;
} while (has_next)
}
//This runs the first page of the API result
function initialAPICall() {
var url = 'https://api.lever.co/v1/requisitions?offset=';
var headers = {
'Authorization': `Basic ${Utilities.base64Encode(leverAPIKey)}`,
"Accept": "application/json"
};
var options = {
"method": "GET",
"headers": headers,
"muteHttpExceptions": true
};
var request = UrlFetchApp.fetch(url, options);
var response = JSON.parse(request.getContentText());
return { 'hasNext': response.hasNext, 'next': response.next, 'data': response.data };
}
//This runs in the do while loop until the last page via 'offset' token parameter
function loopedAPICall(offsetToken) {
var url = 'https://api.lever.co/v1/requisitions?offset=' + offsetToken;
var headers = {
'Authorization': `Basic ${Utilities.base64Encode(leverAPIKey)}`,
"Accept": "application/json"
};
var options = {
"method": "GET",
"headers": headers,
"muteHttpExceptions": true
};
var request = UrlFetchApp.fetch(url, options);
var response = JSON.parse(request.getContentText());
return { 'hasNext': response.hasNext, 'next': response.next, 'data': response.data };
}

I hope this helps you in resolving the issue with iterating through the pages of the API JSON result. If ever you think your question has been misinterpreted, please update your question and include examples of your data and desired results.