Transactions → Google Sheets

/**
 * Hi Energy AI — shared Google Apps Script client
 * Paste this into Extensions → Apps Script, then add a resource script below.
 *
 * Setup:
 * 1. File → Project properties → Script properties
 * 2. Add property HIENERGY_API_KEY = your personal API key
 *    (from https://app.hienergy.ai/api_documentation/api_key)
 * 3. Optionally override HIENERGY_API_BASE if you use a custom host
 */

var HIENERGY_API_BASE = 'https://app.hienergy.ai';

function getHiEnergyApiKey_() {
  var key = PropertiesService.getScriptProperties().getProperty('HIENERGY_API_KEY');
  if (!key) {
    throw new Error(
      'Missing Script Property HIENERGY_API_KEY. ' +
      'Open Project Settings → Script properties and add your API key.'
    );
  }
  return key;
}

function hiEnergyApiGet_(path, query) {
  var url = HIENERGY_API_BASE + path;
  var params = [];
  Object.keys(query || {}).forEach(function(key) {
    var value = query[key];
    if (value === null || value === undefined || value === '') return;
    params.push(encodeURIComponent(key) + '=' + encodeURIComponent(String(value)));
  });
  if (params.length) url += '?' + params.join('&');

  var response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      'X-Api-Key': getHiEnergyApiKey_(),
      'Accept': 'application/json'
    },
    muteHttpExceptions: true,
    followRedirects: true
  });

  var code = response.getResponseCode();
  var body = response.getContentText();
  var json = null;
  try { json = JSON.parse(body); } catch (e) {}

  if (code < 200 || code >= 300) {
    var message = (json && (json.error || json.message)) || body;
    throw new Error('Hi Energy AI API HTTP ' + code + ': ' + message);
  }
  return json;
}

function flattenJsonApiItem_(item) {
  var row = {};
  if (!item || typeof item !== 'object') return row;
  if (item.id !== undefined) row.id = item.id;
  if (item.type !== undefined) row.type = item.type;
  var attrs = item.attributes || {};
  Object.keys(attrs).forEach(function(key) {
    var value = attrs[key];
    row[key] = (value !== null && typeof value === 'object') ? JSON.stringify(value) : value;
  });
  return row;
}

function extractJsonApiCollection_(payload, preferredKeys) {
  if (!payload) return [];
  var keys = preferredKeys || ['data'];
  for (var i = 0; i < keys.length; i++) {
    var key = keys[i];
    var node = payload[key];
    if (Array.isArray(node)) return node;
    if (node && Array.isArray(node.data)) return node.data;
  }
  if (Array.isArray(payload.data)) return payload.data;
  return [];
}

function uniqueHeaders_(rows) {
  var seen = {};
  var headers = [];
  rows.forEach(function(row) {
    Object.keys(row).forEach(function(key) {
      if (!seen[key]) {
        seen[key] = true;
        headers.push(key);
      }
    });
  });
  return headers;
}

function writeObjectsToSheet_(sheetName, rows, clearSheet) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
  if (clearSheet !== false) sheet.clearContents();

  if (!rows.length) {
    sheet.getRange(1, 1).setValue('No rows returned');
    return 0;
  }

  var headers = uniqueHeaders_(rows);
  var values = [headers].concat(rows.map(function(row) {
    return headers.map(function(header) {
      var value = row[header];
      return value === undefined || value === null ? '' : value;
    });
  }));

  sheet.getRange(1, 1, values.length, headers.length).setValues(values);
  sheet.setFrozenRows(1);
  return rows.length;
}

function isoDateDaysAgo_(days) {
  var date = new Date();
  date.setDate(date.getDate() - days);
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

function isoDateToday_() {
  return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

/**
 * Import affiliate transactions into Google Sheets.
 * Requires the shared Hi Energy client helpers in this Apps Script project.
 *
 * Defaults to the last 30 days. Adjust START_DATE / END_DATE / STATUS as needed.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Hi Energy AI')
    .addItem('Import transactions', 'importHiEnergyTransactions')
    .addToUi();
}

function importHiEnergyTransactions() {
  var START_DATE = isoDateDaysAgo_(30);
  var END_DATE = isoDateToday_();
  var STATUS = ''; // e.g. 'approved', 'pending', 'paid', 'corrected'

  var page = 1;
  var perPage = 100;
  var allRows = [];
  var maxPages = 50;

  while (page <= maxPages) {
    var payload = hiEnergyApiGet_('/api/v1/transactions', {
      start_date: START_DATE,
      end_date: END_DATE,
      status: STATUS,
      page: page,
      per_page: perPage,
      include_total: false
    });

    var records = extractJsonApiCollection_(payload, ['transactions', 'data']);
    if (!records.length) break;

    records.forEach(function(item) {
      allRows.push(flattenJsonApiItem_(item));
    });

    if (records.length < perPage) break;
    page += 1;
  }

  var count = writeObjectsToSheet_('Transactions', allRows, true);
  SpreadsheetApp.getActiveSpreadsheet().toast('Imported ' + count + ' transactions', 'Hi Energy AI', 5);
}