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.