Extract meter sync rate 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

//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 []
 }
}

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=PT1H&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 in 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'T'HH:mm:ss.SSS'Z'");
        return { ...item, ts: formattedDate };
      });
      let data_gaps = getMeterDataGapsAgg(data_ts, meter.nmi_id, meter.stream_id, meter.nmi_number, meter.timezone,url);
      allData.push(...data_gaps);
    } catch (error) {
      console.error('Error extracting data for ' + meter.nmi_id + '-' + meter.stream_id, error);

      let no_data = {
        'site_id': meter.site_id,
        'meter_number': meter.nmi_number,
        'meter_stream': meter.stream_id,
        'data_end_date': "",
        'data_start_date': "",
        'total_hours': "",
        'missing_hours': "",
        'meter_completeness': 0,
        'url':url
      };
      allData.push(no_data);
    }
  }
  return allData;
}

// Get meter gaps aggregated per meter stream
function getMeterDataGapsAgg(meterData, nmi_id, stream_id, nmi_number, tz,url) {
  let data = [...meterData];

  let maxTs = data.reduce(function (max, item) {
    let currentTs = new Date(item.ts);
    return currentTs > max ? currentTs : max;
  }, new Date(data[0].ts));

  let minTs = data.reduce(function (min, item) {
    let currentTs = new Date(item.ts);
    return currentTs < min ? currentTs : min;
  }, new Date(data[0].ts));

  let totHours = Math.round((maxTs - minTs) / (1000 * 60 * 60));

  let shiftedData = data.map(function (item, index, array) {
    if (index === 0) {
      return item;
    }

    let currentTs = new Date(item.ts);
    let prevTs = new Date(array[index - 1].ts);
    let diffInHours = Math.round((currentTs - prevTs) / (1000 * 60 * 60));

    return { ...item, ts_start: array[index - 1].ts, ts_diff: diffInHours };
  });

  let filteredData = shiftedData.filter(function (item) {
    return item.ts_diff > 1;
  });
  let updatedData = filteredData.map(function ({ ts, ...rest }) {
    return { ts_end: ts, ...rest };
  });
  let sumTsDiff = updatedData.reduce(function (sum, item) {
    return sum + item.ts_diff;
  }, 0);

  let site_id = meterData[0].site_id;
  let gap_summary =

 {
    'site_id': site_id,
    'meter_number': nmi_number,
    'meter_stream': stream_id,
    'data_end_date': Utilities.formatDate(maxTs, tz, 'dd-MM-yyyy'),
    'data_start_date': Utilities.formatDate(minTs, tz, 'dd-MM-yyyy'),
    'total_hours': totHours,
    'missing_hours': sumTsDiff,
    'meter_completeness': (1 - sumTsDiff / totHours),
    'url':url
  };
  return [gap_summary];
}

// Update google sheet with extracted  data
function updateSheet_meter_syncrate(meterDataGapsallSites) {
  if (!Array.isArray(meterDataGapsallSites) || meterDataGapsallSites.length === 0) {
    Logger.log("No data to update.");
    return;
  }

  const ss = SpreadsheetApp.getActive();
  const valuesSheet = ss.getSheetByName("sync_rate");
  valuesSheet.clearContents();

  const headers = ['site_id','site_name' ,'meter_number', 'meter_stream', 'data_start_date', 'data_end_date', 'total_hours', 'missing_hours', 'meter_completeness','url'];
  const data = meterDataGapsallSites.map(row => [row.site_id,row.site_name ,row.meter_number, row.meter_stream, row.data_start_date, row.data_end_date, row.total_hours, row.missing_hours, row.meter_completeness,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' // inclusive start date
    let end_date='2023-08-01' // exclusive end date
    let site_ids=[195,286]
    let meterList = getMeterlistbySites(site_ids); 
    let site_list=  getSites(site_ids);    
    let meterDataGapsallSites = getMeterDatabyMeters(start_date, end_date, meterList);
    const meterDataGapsallSitesName = meterDataGapsallSites.map(item => {
    const transformation = site_list.find(trans => trans.site_id === item.site_id);  
      return { ...item, site_name: transformation ? transformation.site_name : '' }; });

    updateSheet_meter_syncrate(meterDataGapsallSitesName);
    
  } catch (error) {

  }
}