ALIS API v2: Paged Exports (Google Workspace)

The ALIS API is a data tool that empowers your team to access hundreds of data points captured in ALIS for your own discrete reporting needs. This guide outlines a powerful method for accessing data via the ALIS API (v2), tailored to Google Sheets.

For more information on ALIS API Exports, refer to this guide: ALIS API Reference Guide. 

About Paged Exports (Google Workspace)

In addition to pulling data from our paged export API endpoints through the Microsoft Suite, users can leverage Google Workspace. This process is similar to querying in Excel and Power BI, except the code required is different.

• Google Workspace (Sheets): This option is ideal if your organization utilizes Google Sheets instead of Microsoft Excel. It requires a specific script (Google Apps Script) to pull data from the API and populate your sheet.

• The Google Apps Script uses JavaScript (Apps Script) to connect Google Sheets to the ALIS API.

Google Workspace (Sheets)

Step 1: Open the Script Editor

1. Open a blank Google Sheet.

2. In the top menu, click Extensions > Apps Script.

3. This opens a new tab with a fresh project in the Google Apps Script editor.

Step 2: Insert the Script 

Once inside the new project, delete any existing code in the Code.gs editor and paste the template below.

Note: The example below is a template specifically for calling the Incidents endpoint. You can adapt the headers and mapping for other endpoints as needed. For other example templates you can copy based on specific ALIS API endpoints, refer to this ALIS API v2: Google Workspace Templates (to copy) guide. 

function fetchAlisIncidentsAllFields() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  // --- CONFIGURATION ---
  const username = 'YOUR_USERNAME@COMPANYTEXTKEY'; // Update this with your username and company text key
  const password = 'YOUR_PASSWORD'; // Update this with your API access password
  const baseUrl = 'https://api.alisonline.com/v2/export/residents/incidents'; // Update this for the endpoint you are calling
  
  // Encode credentials for Basic Auth
  const authHeader = 'Basic ' + Utilities.base64Encode(username + ':' + password);
  
  let allRows = [];
  let pageNumber = 1; 
  let hasNextPage = true;

  // --- 1. DEFINE HEADERS (All 21 fields) ---
  const headers = [
    "Community ID", 
    "Community Name", 
    "Resident ID", 
    "Resident Name", 
    "Room Number", 
    "Resident Status", 
    "Product Type", 
    "Classification", 
    "Incident ID", 
    "Status", 
    "Is Complete", 
    "Created By Name", 
    "Created By ID", 
    "Incident Date/Time", 
    "Incident Type", 
    "Incident Location", 
    "Summary", 
    "Completed Forms", 
    "Completed Tasks", 
    "Incomplete Forms", 
    "Incomplete Tasks"
  ];
  
  // Add headers as the first row
  allRows.push(headers);

  // --- 2. LOOP THROUGH PAGES ---
  while (hasNextPage) {
    const url = baseUrl + "?pageNumber=" + pageNumber + "&pageSize=100"; // Page size can be set up to 10,000
    
    try {
      const response = UrlFetchApp.fetch(url, {
        "method": "get",
        "headers": { "Authorization": authHeader },
        "muteHttpExceptions": true
      });
      
      // Stop if API returns an error
      if (response.getResponseCode() !== 200) {
        Logger.log("Error on page " + pageNumber + ": " + response.getContentText());
        break;
      }

      const json = JSON.parse(response.getContentText());
      const items = json.items;

      // --- 3. PROCESS DATA (Map all fields) ---
      if (items && items.length > 0) {
        items.forEach(item => {
          allRows.push([
            item.communityId,
            item.communityName,
            item.residentId,
            item.residentFullName,
            item.roomNumber,
            item.residentStatus,
            item.productType,
            item.classification,
            item.incidentId,
            item.status,
            item.isComplete,       // Boolean (will show as TRUE/FALSE)
            item.createdBy,
            item.createdById,
            item.incidentDateTime,
            item.incidentType,
            item.incidentLocation,
            item.incidentSummary,
            item.completedForms,
            item.completedTasks,
            item.incompleteForms,
            item.incompleteTasks
          ]);
        });
      }

      // --- 4. CHECK PAGINATION ---
      hasNextPage = json.hasNextPage;
      pageNumber++;
      
      // Small pause to prevent rate limiting
      Utilities.sleep(200);

    } catch (e) {
      Logger.log("Script failed: " + e.toString());
      hasNextPage = false;
    }
  }

  // --- 5. WRITE TO SHEET ---
  if (allRows.length > 0) {
    sheet.clear();
    // Write all data to the sheet in one operation
    sheet.getRange(1, 1, allRows.length, allRows[0].length).setValues(allRows);
  }
}

Step 3: Configure Your Credentials

Before running the script, you must update the specific lines in the "Configuration" section of the code (lines 6-8):

1. username: Replace 'YOUR_USERNAME@COMPANYTEXTKEY' with your actual API username.

2. password: Replace 'YOUR_PASSWORD' with your API password. 

3. baseUrl: Ensure this matches the endpoint you wish to call. 

Step 4: Save and Run

1. Click the Save icon (floppy disk) in the toolbar to save your project.

2. Click the Run button in the toolbar.

Step 5: Authorization

The first time you run the script, Google will ask for permission to access your spreadsheet and external services.

1. An "Authorization Required" pop-up will appear. Click Review Permissions.

2. Follow the instructions to sign in to your Google account. Click the Select All checkbox.

3. Scroll down and click Continue.

Step 6: View Data

1. Back on the Apps Script editor, click Run again. 

2. Check the Execution Log at the bottom of the screen to ensure the script completed without errors. 

3. Return to your Google Sheet tab. You should now see your ALIS data populated in rows and columns.

Related Articles:

• ALIS API v1: Standard Excel Exports
• ALIS API Reference Guide
• ALIS API v2: Templates to copy (Microsoft Suite)
• ALIS API v2: Templates to copy (Google Workspace)

For more assistance with ALIS API Exports,

Contact our ALIS Customer Success Team:

888.404.ALIS (2547) or support@go-alis.com