API - Google Apps Script

Try this Google Apps script to pull all room location data. I recommend creating a View in Incident IQ, and checking the API documentation on how to query the https://wusd-org.incidentiq.com/api/v1.0/views/{View_ID} endpoint so that you’re not trying to pull ALL ticket data. You will need to input your own API_KEY value.

function callApiAndPopulateSheet() {
  const API_KEY = '';
  const URL = 'https://wusd-org.incidentiq.com/api/v1.0/locations/rooms?p=0&$s=5000'; // Replace with your endpoint

  // 1. Set up the request options (Headers are usually where the API key goes)
  const options = {
    "method": "GET",
    "headers": {
      "Authorization": "Bearer " + API_KEY,
      "Content-Type": "application/json"
    },
    "muteHttpExceptions": true
  };

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

      // Access the specific array in your response
      const data = json.Items; 

      if (!data || !Array.isArray(data)) {
        throw new Error("Property 'Items' not found or is not an array");
      }

      const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      const headers = Object.keys(data[0]); 
      const rows = data.map(item => headers.map(header => item[header]));

      sheet.clear();
      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
      sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

      SpreadsheetApp.getUi().alert("Imported " + rows.length + " rows successfully!");
      // Adds "Last Updated: [Date/Time]" to cell Z1 (or any empty cell)
      sheet.getRange("Z1").setValue("Last Updated: " + new Date().toLocaleString());

    } catch (e) {
      Logger.log(e.toString());
      SpreadsheetApp.getUi().alert("Error: " + e.message);
    }
  }

How to set up an Hourly Trigger

1. In the Apps Script editor, look at the left-hand sidebar and click the Triggers icon (it looks like a clock 🕒).

2. Click the + Add Trigger button in the bottom right corner.

3. Configure the settings as follows:

   • Choose which function to run: callApiAndPopulateSheet

   • Choose which deployment should run: Head

   • Select event source: Time-driven

   • Select type of time based trigger: Hour timer

   • Select hour interval: Every hour (or your preferred frequency)

4. Click Save.

​@DWallis 434c1d brooklyn Thank you for submitting your question to our community! 😄 

I looked into this during my call with the support team today! ​@LCampbell I am glad you have a solution to share, it looks great if it works for you! 😄

Let me know if it is what you were looking for. 

Something to be mindful of - if you’re a large district with a boatload of tickets, there are limitations. Apps Script has a maximum runtime of 30 minutes for paid Google Workspace accounts, which seems like a long time, but parsing through and writing hundreds of thousands of lines to a spreadsheet can take a awhile, especially if you’re digging into things like the associated asset, timeline, etc. Just keep that in mind.

One thing we’ve done for small reports is use the Apps Script project as an endpoint and setup a webhook in iiQ, that way you’re writing them to the Google Sheet as they come in, or get updated/completed. Just a thought.

This is a great point. I’d personally do this in a docker container / VM on-prem with a Python script, so that the computation is done in-house. The script(s) could run as long as needed before making API calls to Google.

​@DWallis 434c1d brooklyn asked for the solution in Google Apps Script, so that’s what I provided.