🖥️Google app script

Add Helio transactions to your Google Sheets using the Helio API

Note: You need to generate an API key in the Helio dashboard for Google sheet integration. Alternatively, you can manually export your Helio transactions as a .csv file from the Helio dashboard.

Script version: v0.2.6

Please test with a new Google Sheet when upgrading the script to avoid data duplication due to new columns.

Get started

• Create a Google Sheet

  • Open a new sheet.

  • Go to Extensions -> App Script.

• Add the script

  • In the text editor, pictured below, connect your Google account if prompted.

Google Apps Script Text Editor

• Copy and paste the provided script code, replacing the starter myFunction.

/*
HELIO SHEETS INTEGRATION v0.2.6
AUTHOR: [e-mail]
HOW TO USE:
Add your...
HELIO_API_KEY (Get this from the Helio Dash | Settings | API | Secret API Key)
PUBLIC_KEY (Get this from the Helio Dash | Settings | API | Public API Key)
Then select the main function at the top and press Run!
HOW IT WORKS: This script gets your recent Helio transactions filtered aafter the 
specified date, checks if they are not already on the sheet and then appends the
data below the headers, sorting by most recently purchased.
*/
// Add your values here

const PUBLIC_KEY = "";
const HELIO_API_KEY = "";
const FILTER_DATE = new Date("2024-09-15T00:00:00Z"); // Set this to your desired start date
const HEADERS = [
  'Time Purchased (UTC)',
  'Time stopped',
  'Total time charged',
  'Sender',
  'Product',
  'Quantity',
  'Paid',
  'Currency',
  'Payment Link',
  'Payment Link (Short)',
  'SolScan URL',
  'Transaction ID',
  'E-mail Address',
  'Twitter Username',
  'Discord Username',
  'Phone Number',
  'Name',
  'Street Number',
  'Street',
  'Delivery Address',
  'City',
  'Area Code',
  'State',
  'Country',
  'Additional Info',
];

function main() {
  const helioTransactions = getRecentTransactions(HELIO_API_KEY, PUBLIC_KEY);
  if (helioTransactions != null) {
    console.log('RECEIVED: Transaction data');
    const filteredTransactions = filterTransactions(helioTransactions, FILTER_DATE);
    if (filteredTransactions.length > 0) {
      writeNewTransactions(filteredTransactions, HEADERS);
    } else {
      console.log('No new transactions to add after the specified date');
    }
  } else {
    throw new Error('ERROR: could not get helio transactions');
  }
}

function getRecentTransactions(API_KEY, PK) {
  try {
    const response = UrlFetchApp.fetch('https://api.hel.io/v1/export/payments?publicKey='+PK, {
      method: 'get',
      headers: {
        'Authorization': 'Bearer '+ API_KEY,
      }
    });
    if (response.getResponseCode() == 200) {
      return JSON.parse(response.getContentText());
    }
  } catch (e) {
    console.log(e);
  }
}

function filterTransactions(helioTransactions, filterDate) {
  var sheet = SpreadsheetApp.getActiveSheet();
  const existingTransactionIDs = getColumn(sheet, 12); 
  
  return helioTransactions.filter(transaction => {
    const transactionDate = new Date(transaction.time);
    return transactionDate >= filterDate && !existingTransactionIDs.includes(transaction.id);
  });
}

function writeNewTransactions(newTransactions, headers) {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Ensure headers are present and correct
  const firstRow = sheet.getRange(1, 1, 1, headers.length);
  const firstRowValues = firstRow.getValues()[0];
  if (!arraysEqual(headers, firstRowValues)) {
    firstRow.setValues([headers]);
    firstRow.setFontWeight("bold");
  }
  
  // Prepare the data for batch insert
  const newData = newTransactions.map(item => [
    item.active ? `Live (${item.time})` : item.time,
    item.active || !item.cancelAt ? '' : item.cancelDate,
    item.timeCharged,
    item.from,
    item.paymentRequestName,
    item.quantity ?? 1,
    item.convertedAmount + " " + item.currency,
    item.currency,
    item.paymentRequestUrl,
    item.paymentRequestUrlWithSlug ?? '',
    item.solScanLink,
    item.id,
    item.email,
    item.twitterUsername,
    item.discordUsername,
    item.phoneNumber,
    item.fullName ?? '',
    item.streetNumber ? item.streetNumber.replace(',', ';') : '',
    item.street ? item.street.replace(',', ';') : '',
    item.deliveryAddress ? item.deliveryAddress.replace(',', ';') : '',
    item.city ? item.city.replace(',', ';') : '',
    item.areaCode ? item.areaCode.replace(',', ';') : '',
    item.state ? item.state.replace(',', ';') : '',
    item.country,
    item.value && item.name ? `${item.value.replace(',', ';')} (${item.name})` : ''
  ]);

  // Insert all new transactions at once
  if (newData.length > 0) {
    sheet.getRange(2, 1, newData.length, headers.length).insertCells(SpreadsheetApp.Dimension.ROWS);
    sheet.getRange(2, 1, newData.length, headers.length).setValues(newData);
    console.log(`ADDED ${newData.length} NEW TRANSACTIONS`);
  }
  
  SpreadsheetApp.flush();
}

function arraysEqual(a, b) {
  if (a === b) return true;
  if (a == null || b == null) return false;
  if (a.length !== b.length) return false;
  for (var i = 0; i < a.length; ++i) {
    if (a[i] !== b[i]) return false;
  }
  return true;
}

function getColumn(activeSheet, columnIndex) {
  return activeSheet.getRange(1, columnIndex)
    .getDataRegion(SpreadsheetApp.Dimension.ROWS)
    .getValues()
    .flat();
}

Developers can customise headers and columns.

• Save and Run

  • Click the save icon or press Ctrl+S.

  • Select the main function from the dropdown next to Debug if you're running an old version.

  • Press Run . Approve any Google permissions if prompted.

• Verify

  • If successful, your transactions will appear in the Google Sheet.

Optional: Customise the sheet (e.g., hide columns, change header names), but avoid altering the order of columns.

Add automation to your Google sheets

• Open Triggers

  • In the Google App Script tab, click the clock icon Triggers in the left menu.

• Set Up a Trigger

  • Click Add Trigger.

  • Configure the following:

    • Function to Run: Select Main.

    • Deployment: Select Head.

    • Event Source: Choose Time-driven.

    • Type and Interval: Select an internal (e.g., every 30 minutes or 1 hour).

For assistance, visit our Discord support channel.