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.
//Fetch the access token by using user credentials saved on Apps Script Project
function getAccessToken() {
let userprop=PropertiesService.getScriptProperties().getProperties();
let payload = {
grant_type: 'refresh_token',
client_id: 'api-external',
refresh_token: userprop.offline_token,
}
let options = {method: 'POST', payload: payload};
let response = UrlFetchApp.fetch('https://login.cimenviro.com/auth/realms/cimenviro/protocol/openid-connect/token', options);
let data = JSON.parse(response.getContentText());
if (data.access_token) {
return data.access_token
}
else {
console.log('Error, failed to generate Access token: ' + response.getContentText());
}
}
// Get site list
function getSite(site_id) {
let accessToken = getAccessToken();
const options = {
method: 'GET',
headers: {
accept: 'application/json',
Authorization: 'Bearer ' + accessToken
}
};
try{
const response = UrlFetchApp.fetch(`https://api.cimenviro.com/sites/${site_id}`, options);
if (response.getResponseCode() === 404) {
Logger.log('Invalid Site:', site_id);
}
else {
const data = JSON.parse(response.getContentText());
return data.data.site.site_name;
}
}
catch (error) {
console.error();
return []
}
}
// Get all sites
function getSites(site_ids) {
const allData = [];
for (let i = 0; i < site_ids.length; i++) {
const site_id = site_ids[i];
const data = getSite(site_id);
if (data.length>0){
allData.push({ 'site_id': site_id, 'site_name': data });}
}
return allData;
}
// Get meter list
function getMeterlistbySite(site_id) {
let accessToken = getAccessToken();
let options = {
method: 'GET',
headers: {
'contentType': 'application/json',
'Authorization': 'Bearer ' + accessToken
}
};
try{
let response = UrlFetchApp.fetch(`https://api.cimenviro.com/sites/${site_id}/nmi/streams`, options);
if (response.getResponseCode() === 404) {
console.log('Invalid Site:', site_id);
}
else {
let data = JSON.parse(response.getContentText());
let meter_stream = data.data.nmi_streams;
let meter_number = getMeterTypelistbySite(site_id);
let meterlist = meter_stream.map(function (item)
{
let transformation = meter_number.find(function (trans) {
return trans.site_id === item.site_id && trans.nmi_id === item.nmi_id;
});
return {
...item,
'utility_type': transformation ? transformation.utility_type : '',
'meter_type': transformation ? transformation.meter_type : '',
'timezone': transformation ? transformation.timezone : ''
};
});
return meterlist
}
}
catch(error) {
}
}
// Get meter list for an array of sites
function getMeterlistbySites(site_ids) {
let allData = [];
for (let i = 0; i < site_ids.length; i++) {
let site_id = site_ids[i];
let data = getMeterlistbySite(site_id);
if (data.length>0){
allData.push(...data);}
}
return allData;
}
// Get meter type
function getMeterTypelistbySite(site_id) {
let accessToken = getAccessToken();
let options = {
method: 'GET',
headers: {
contentType: 'application/json',
Authorization: 'Bearer ' + accessToken
}
};
try{
let response = UrlFetchApp.fetch(`https://api.cimenviro.com/sites/${site_id}/nmi`, options);
let data = JSON.parse(response.getContentText());
return data.data.nmi_numbers;}
catch(error)
{ console.log('Invalid Site',error)
return []}
}
// Get meter history
function getMeterData(ts_start, ts_end, nmi_id, stream_id) {
let accessToken = getAccessToken();
let options = {
method: 'GET',
headers: {
contentType: 'application/json',
Authorization: 'Bearer ' + accessToken
}
};
let response = UrlFetchApp.fetch(`https://api.cimenviro.com/meterdata/history?nmi_ids_stream_ids=${nmi_id}-${stream_id}&start_ts=${ts_start}&end_ts=${ts_end}&respect_site_units=false&result_types=data&aggregate_period=P1M&data_type=active_net`, options);
try {
let data = JSON.parse(response.getContentText());
let site_id = data.data.meterdata_history[0].site_id;
return data.data.meterdata_history[0].data.map(function (row) {
return { ...row, nmi_id, stream_id, site_id };
});
} catch (error) {
console.error('No data found for the given nmi_ids_stream_ids.');
return [];
}
}
// Format timestamp to iso
function convertToTimeZone(in_date, timezone) {
const inputDate = new Date(in_date);
const inputTimezone = 'UTC';
const targetTimezone = timezone;
const targetDate = new Date(inputDate.toLocaleString('en-US', { timeZone: inputTimezone }));
const offset = targetDate.getTimezoneOffset() * 60 * 1000;
const convertedDate = new Date(targetDate.getTime() + offset);
const formattedDate = Utilities.formatDate(convertedDate, targetTimezone, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
return formattedDate
}
// Get meter data for an array of sites
function getMeterDatabyMeters(start_date, end_date, meterList) {
let allData = [];
for (let i = 0; i < meterList.length; i++) {
let meter = meterList[i];
let ts_start = convertToTimeZone(start_date,meter.timezone)
let ts_end = convertToTimeZone(end_date,meter.timezone)
let url=`https://ace.cimenviro.com/meters?utilityType=Electricity&consumptionTypes=Usage&aggregatePeriod=1%20day&startDate=${ts_start}&endDate=${ts_end}&siteId=${meter.site_id}&streamIds=${meter.nmi_id}-${meter.stream_id}`;
try {
let data = getMeterData(ts_start, ts_end, meter.nmi_id, meter.stream_id);
let data_ts = data.map(function (item) {
let formattedDate = Utilities.formatDate(new Date(item.ts), meter.timezone, "yyyy-MM-dd");
return {ts: formattedDate,'site_id': meter.site_id,'utility_type': meter.utility_type,'meter_number': meter.nmi_number,'meter_stream': meter.stream_id,'usage':item.data,'url':url};
});
allData.push(...data_ts);
} catch (error) {
console.error('Error extracting data for ' + meter.nmi_id + '-' + meter.stream_id, error);
let no_data = {'ts':"",
'site_id': meter.site_id,
'utility_type':meter.utility_type,
'meter_number': meter.nmi_number,
'meter_stream': meter.stream_id,
'usage':"",
'url':url
};
allData.push(no_data);
}
}
return allData;
}
// Update google sheet with extracted data
function updateSheet_meter(meterDataAllSitesName) {
if (!Array.isArray(meterDataAllSitesName) || meterDataAllSitesName.length === 0) {
Logger.log("No data to update.");
return;
}
const ss = SpreadsheetApp.getActive();
const valuesSheet = ss.getSheetByName("usage");
valuesSheet.clearContents();
const headers = ['date','site_id','site_name','utility_type' ,'meter_number', 'meter_stream', 'usage','url'];
const data = meterDataAllSitesName.map(row => [row.ts,row.site_id,row.site_name,row.utility_type,row.meter_number, row.meter_stream, row.usage,row.url]);
valuesSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
valuesSheet.getRange(2, 1, data.length, headers.length).setValues(data);
Logger.log("Data Updated Successfully.");
}
// Main function to fetch meter data for sites and export to sheets
function run() {
try {
let start_date='2023-07-01'
let end_date='2023-08-01'
let site_ids=[195,286]
let meterList = getMeterlistbySites(site_ids);
let site_list= getSites(site_ids);
let meterDataallSites = getMeterDatabyMeters(start_date, end_date, meterList);
const meterDataAllSitesName = meterDataallSites.map(item => {
const transformation = site_list.find(trans => trans.site_id === item.site_id);
return { ...item, site_name: transformation ? transformation.site_name : '' }; });
updateSheet_meter(meterDataAllSitesName);
} catch (error) {
}
}