Schedule export meter data to Google Sheets

1. Create a new google sheet

Create a new google sheet and name your new file. Click on Extensions and select Apps Script

2. Add your Refresh Token

Click on Project Settings. Scroll to the bottom and add a new Script property. Name the Property refresh_token and paste in your Refresh Token received from CIM Support.

3. Copy script

Click on Editor and copy and paste the below script. Save and then Click Run. Review and accept permissions and then click Run again.

JavaScript

var site_ids = [3, 148]; // replace with your array of site_ids
var start_date = '2023-01-01';
var end_date = '2023-06-01';
var aggregate_period = 'P1M';

function main() {
  var REFRESH_TOKEN = PropertiesService.getScriptProperties().getProperty('refresh_token'); // replace with your actual refresh token
  var CLIENT_ID = 'api-external';
  var API_ENDPOINT = '<<URL_API>>'
  var URL = '<<URL_AUTH>>'; 

  var accessToken = getAccessToken(URL, REFRESH_TOKEN, CLIENT_ID);
  var meterDataForSites = getMeterDataForSites(site_ids, accessToken, API_ENDPOINT, start_date, end_date, aggregate_period);
  var csvData = convertToCSV(meterDataForSites);
  writeToSheet(csvData);
}

function getAccessToken(URL, REFRESH_TOKEN, CLIENT_ID) {
  var options = {
    method: 'post',
    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
    payload: `grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}&client_id=${CLIENT_ID}`
  };
  var response = UrlFetchApp.fetch(URL, options);
  var data = JSON.parse(response.getContentText());
  return data.access_token;
}

function getMeterDataBySite(site_id, accessToken, API_ENDPOINT, start_date, end_date, aggregate_period) {
  var options = {
    method: 'get',
    headers: {
      'Accept': 'application/json',
      'Authorization': `Bearer ${accessToken}`,
      'ngrok-skip-browser-warning': true
    },
    muteHttpExceptions: true
  };
  var url = `${API_ENDPOINT}/sites/${site_id}/meters?utility_type=Electricity&reportable=true&group_by=site&aggregate_period=${aggregate_period}&start_date=${start_date}&end_date=${end_date}`;
  var response = UrlFetchApp.fetch(url, options);
  
  var data = JSON.parse(response.getContentText())
  var data = JSON.parse(response.getContentText()).data.meterdata_history;
  for(var i in data) {
    data[i].site_id = site_id; // Add site_id to each row
  }
  return data;
}

function getMeterDataForSites(site_ids, accessToken, API_ENDPOINT, start_date, end_date, aggregate_period) {
  var allData = [];
  for(var i in site_ids) {
    var meterDataBySite = getMeterDataBySite(site_ids[i], accessToken, API_ENDPOINT, start_date, end_date, aggregate_period);
    allData = allData.concat(meterDataBySite);
  }
  return allData;
}

function convertToCSV(jsonArray) {
  var keys = Object.keys(jsonArray[0]);
  var lines = jsonArray.map(function(obj) {
    return keys.map(function(key) {
      return obj[key];
    }).join(",");
  });
  return [keys.join(",")].concat(lines).join("\n");
}

function writeToSheet(csvData) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSpreadsheet.getSheetByName("Export");
  if(!sheet) {
    sheet = activeSpreadsheet.insertSheet("Export");
  }
  sheet.clear();
  var csvDataArray = Utilities.parseCsv(csvData);
  sheet.getRange(1, 1, csvDataArray.length, csvDataArray[0].length).setValues(csvDataArray);
}