Want to see your Helio orders automatically show in Google? Helio now supports integration with Google Sheets. This tutorial shows you how to connect your Google Sheets with Helio and get automatic transactions.
Note: if you want to retrieve your Helio transactions manually, you can export a .csv file here
To get started, create a google sheets
Once created you should be looking at new spreadsheet
Next, go to Extensions -> App Script
You should then be looking at a text editor like the one below. It might ask you to connect your google account.
The next step might look scary, but all the code has been provided to you, simply copy below and paste it into the text editor.
TIP: Make sure to remove the starter 'myFunction' function
/*
HELIO SHEETS INTEGRATION v0.1
AUTHOR: michael.beer@helio.co
HOW TO USE:
Add your...
HELIO_API_KEY (Join our Discord @ https://discord.com/invite/helio and ask for one)
PUBLIC_KEY (The wallet Public Key you use for Helio, for example if you are using a wallet browser you can find
it in the extension)
Then select the main function at the top and press Run!
HOW IT WORKS: This script gets your recent Helio transactions, 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 HELIO_API_KEY = "[PUT YOUR HELIO API HERE]";
const PUBLIC_KEY = "[PUT YOUR PUBLIC KEY HERE]";
const HEADERS = [
"Name",
"Twitter",
"Discord",
"Email",
"Phone Number",
"Shipping Address",
"Time Purchased (UTC)"
];
function getRecentTransactions(API_KEY, PK) {
try {
const response = UrlFetchApp.fetch('https://prod.api.hel.io/export/payments?publicKey='+PK, {
method: 'get',
headers: {
'Authorization': 'Bearer '+ API_KEY,
}
});
if (response.getResponseCode() == 200) {
console.log(response.getContentText());
return JSON.parse(response.getContentText());
}
} catch (e) {
console.log(e);
}
}
function writeRecentTransactions(helioTransactions, headers) {
// Get the google sheet
var sheet = SpreadsheetApp.getActiveSheet();
const firstRow = sheet.getRange(1, 1, 1, headers.length);
const firstRowValues = firstRow.getValues()[0]
if (!arraysEqual(headers, firstRowValues)) {
firstRow.setValues([headers]);
firstRow.setFontWeight("bold");
}
for (let index = 0; index < helioTransactions.length; ++index) {
item = helioTransactions[index];
const secondRow = sheet.getRange(2, 1, 1, headers.length);
secondRow.insertCells(SpreadsheetApp.Dimension.ROWS); //Push rows down by one
secondRow.setValues([[
item.fullName ?? item.from,
item.twitterUsername ?? 'N/A',
item.discordUsername ?? 'N/A',
item.email ?? 'N/A',
item.phoneNumber ?? 'N/A', // not supported yet
item.shippingAddress ?? 'N/A',
item.time
]]);
SpreadsheetApp.flush();
console.log("ADDED NEW TRANSACTION: " + item.time);
}
}
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 main() {
const helioTransactions = getRecentTransactions(HELIO_API_KEY, PUBLIC_KEY);
if (helioTransactions != null) {
console.log('RECIEVED: Transaction data');
writeRecentTransactions(helioTransactions, HEADERS);
} else {
throw new Error('ERROR: could not get helio transactions');
};
}
Then add your Helio API key and your wallet public Key (line 15-16). You can get a Helio API key by from the team by joining the Helio Discord. Your screen should now look like something like this:
Nearly there, next you need to run the 'main' function (by default is set to writeRecentTransactions). In the top bar, click the dropdown next to 'Debug' and select 'Main'.
Finally at the top. Press 'Run'. If it is the first time running, you might need to approve some messages from Google. If it all goes well you should have successful messages coming up. If you go back to your sheets it should your new transactions!
HELP: If for whatever reason your code fails to run, join our Discord and ask one of the team for help! We will get back to you fast (:
Congratulations, you have now connected Helio to Google Sheets. Finally we can add automation to so you don't need to follow these steps every time.
To add automation, go back to your Google App Script tab, on the left side menu open click the clock icon 'Triggers'.
Now on the Triggers menu click 'Add Trigger' at the bottom right and you should be greeted with a menu.
For 'Choose which function to run', select 'Main' For 'Choose which deployment should run', select 'Head' For 'Select event source', select 'Time-driven'
Now for 'Select type of time based trigger' and 'Select [time] interval', choose the one that is most sutable for you. This basically means how often the sheets will update. We recommend choosing minutes, or hours. For this tutorial I chose 1 hour.
Once you press save, you are finished!
🥳 Congratulations, you have now connected Helio to Google Sheets for automatic transaction updates. That was not hard right?
Any questions message the Helio team, and follow the Medium for more simple tutorials.