Export portfolio actions to CSV

In this guide, we will show you how to extract actions for multiple sites from the CIM API and export it to a CSV file. This could be handy if you're looking to analyze this data in third-party spreadsheet or BI tools or if you need to generate reports for offline usage.

1. Install Dependencies

Before we start, make sure to install the necessary libraries. We will use axios for HTTP requests and json2csv to convert JSON data to CSV.

Open your terminal and initialize a new project:

npm init -y

Install these libraries by running the following commands:

npm install --save axios json2csv

2. Authentication

To follow this guide, you will need to have your CIM API Refresh Token. If you are on the Enterprise Plan you can request this by contacting CIM support at support@cim.io.

Here is a function which fetches an Access Token using your Refresh Token.

const axios = require('axios');

// Refresh token
const REFRESH_TOKEN = 'YOUR_REFRESH_TOKEN'; // replace with your actual refresh token
const CLIENT_ID = 'api-external';

// OAuth 2.0 endpoint and client details
const URL = 'https://login.cimenviro.com/auth/realms/cimenviro/protocol/openid-connect/token'

// Function to get new access token using refresh token
let getAccessToken = async () => {
  const options = {
    method: 'POST',
    url: URL,
    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
    data: `grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}&client_id=${CLIENT_ID}`,
  };

  const response = await axios.request(options);
  return response.data.access_token;
}

3. Fetching Meter Data and Exporting to CSV

Here is a script to fetch action data across multiple sites from the CIM API and write it to a new CSV file.

In this section, we will utilize the getAccessToken function we defined in the Authentication section above. This function fetches an access token which is then used to authenticate our requests to fetch action data.

Create a new file in your folder named main.js and copy and paste the script below. Replace YOUR_REFRESH_TOKEN with the the Refresh Token you received from CIM.

// main.js

const axios = require('axios');
const json2csv = require('json2csv').parse;
const fs = require('fs');

// Refresh token
const REFRESH_TOKEN = 'YOUR_REFRESH_TOKEN'; // replace with your actual refresh token
const CLIENT_ID = 'api-external';

// API URL Endpoint for actions
const API_ENDPOINT = 'https://api.cimenviro.com/tickets/tickets/'
const EQUIPMENT_ENDPOINT = 'https://api.cimenviro.com/sites/'

// Array of site_ids
const site_ids = [195]; // replace with your array of site_ids


// OAuth 2.0 endpoint and client details
const URL = 'https://login.cimenviro.com/auth/realms/cimenviro/protocol/openid-connect/token'; 

// Function to get new access token using refresh token
let getAccessToken = async () => {
  const options = {
    method: 'POST',
    url: URL,
    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
    data: `grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}&client_id=${CLIENT_ID}`,
  };

  const response = await axios.request(options);
  return response.data.access_token;
}

// Get meter history for a single site
let getActionDataBySite = async (site_id, accessToken) => {
  const options = {
    method: 'GET',
    url: API_ENDPOINT,
    params: {
      site_id: site_id,
      type: 'escalated',
      start_index: 0,
      limit:1000
    },
    headers: {
      accept: 'application/json',
      Authorization: `Bearer ${accessToken}`
    }
  };

  const response = await axios.request(options);
  return response.data.data.tickets.map(row => ({...row, site_id})); // add site_id to each row      
}


// Get action data for an array of sites
const getActionDataForSites = async (site_ids, accessToken) => {
  const promises = site_ids.map(site_id => getActionDataBySite(site_id, accessToken));
  const allData = await Promise.all(promises);
  return allData.flat();
};




// Get actons for a single site
let getEquipmentDataBySite = async (site_id, accessToken) => {
  const options = {
    method: 'GET',
    url: `${EQUIPMENT_ENDPOINT}${site_id}/equipment`,
    params: {
    },
    headers: {
      accept: 'application/json',
      Authorization: `Bearer ${accessToken}`
    }
  };

  const response = await axios.request(options);
  return response.data.data.equipment.map(row => ({'equipment_id':row.equipment_id, 'equipment_name':row.name}))     
}


// Get actions for an array of sites
const getEquipmentDataForSites = async (site_ids, accessToken) => {
  const promises = site_ids.map(site_id => getEquipmentDataBySite(site_id, accessToken));
  const allData = await Promise.all(promises);

  return allData.flat();
};

// Convert data to CSV format
const convertToCSV = (data) => {
  const fields = ['site_id','summary','assignee_ids','description','benefit_id','status_id','resolved_at','updated_at','priority_id','equipment_name','solution','created_at'];
  const csv = json2csv(data, { fields });

  return csv;
};

// Export CSV data into a new file
const exportToCSV = (csv) => {
  //const timestamp = new Date().toISOString();
  const filename = `action_data.csv`;
  fs.writeFileSync(filename, csv);
  console.log(`CSV file '${filename}' has been created successfully.`);
};


// Replace equipment_ids with equipment_name
const replaceIdwithName=(actionData,equipmentData) => {
const mapping = {};
equipmentData.forEach((item) => {
  if (item.equipment_id) {
    mapping[item.equipment_id.toString()] = item.equipment_name;
  }
});

// Replace equipment_ids with corresponding names in array actionData
const result = actionData.map((item) => {
  if (item.equipment_ids && Array.isArray(item.equipment_ids)) {
    const updatedIds = item.equipment_ids.map((id) => mapping[id.toString()] || id);
    return { ...item, 'equipment_name': updatedIds };
  } 
  return item;
});

return result;
};


// Main function to fetch meter data for sites and export to CSV
const run = async () => {
  try {
    const accessToken = await getAccessToken();
    const actionData = await getActionDataForSites(site_ids, accessToken);
    const equipmentData = await getEquipmentDataForSites(site_ids, accessToken);
    const mappedData= await replaceIdwithName(actionData,equipmentData)
    const csv = convertToCSV(mappedData);
    exportToCSV(csv);
  } catch (error) {
    console.error(error);
  }
};

run();

In your terminal execute the script by running node main.js