Add Helio transactions to your Google Sheets using the Helio API
Would you like to see your Helio transactions automatically show up in Google Sheets?
We integrate with Google Sheets and this tutorial explains how to set it up.
Note: you need to generate your API key in the Helio dashboard to use the Google sheet integration. If you prefer to retrieve your Helio transactions manually, you can export a .csv file by logging in to the Helio dashboard
Current script version: v0.2.6
Please test with a new Google sheet when upgrading the script. Changes to the script around adding new columns will add all previous existing data with new columns and duplicate your data.
Get started
To get started, create a google sheets and open a new sheet. 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.2.6AUTHOR: michael.beer@helio.coHOW 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, 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 hereconstPUBLIC_KEY="";constHELIO_API_KEY="";constHEADERS= ['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', ];functionmain() {consthelioTransactions=getRecentTransactions(HELIO_API_KEY,PUBLIC_KEY);if (helioTransactions !=null) {console.log('RECIEVED: Transaction data');writeRecentTransactions(helioTransactions,HEADERS);} else {thrownewError('ERROR: could not get helio transactions');};}functiongetRecentTransactions(API_KEY, PK) {try {constresponse=UrlFetchApp.fetch('https://api.hel.io/v1/export/payments?publicKey='+PK, {method:'get',headers: {'Authorization':'Bearer '+API_KEY,}});if (response.getResponseCode() ==200) {returnJSON.parse(response.getContentText());}} catch (e) {console.log(e);}}functionwriteRecentTransactions(helioTransactions, headers) {// Get the google sheetvar sheet =SpreadsheetApp.getActiveSheet();constfirstRow=sheet.getRange(1,1,1,headers.length);constfirstRowValues=firstRow.getValues()[0];if (!arraysEqual(headers, firstRowValues)) {firstRow.setValues([headers]);firstRow.setFontWeight("bold");}console.log("CHECKING FOR NEW TRANSACTIONS");for (let index =helioTransactions.length-1; index >=0; --index) {item = helioTransactions[index];consttransactionIDs=getColumn(sheet,12);if (!transactionIDs.includes(item.id)) {constfullSecondRow=sheet.getRange(2,1,1,sheet.getMaxColumns());constsecondRow=sheet.getRange(2,1,1,headers.length);fullSecondRow.insertCells(SpreadsheetApp.Dimension.ROWS); //Push rows down by onesecondRow.setValues([[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})`:'']]);console.log("ADDED NEW TRANSACTION: "+item.time);} else {console.log("TRANSACTION ALREADY IN TABLE");}SpreadsheetApp.flush();}}functionarraysEqual(a, b) {if (a === b) returntrue;if (a ==null|| b ==null) returnfalse;if (a.length!==b.length) returnfalse;for (var i =0; i <a.length; ++i) {if (a[i] !== b[i]) returnfalse;}returntrue;}functiongetColumn(activeSheet, columnIndex) {returnactiveSheet.getRange(1, columnIndex).getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues().flat();}functiongetRow(activeSheet, rowIndex) {returnactiveSheet.getRange(rowIndex,1);}
TIP: If you are a developer you can go ahead and try to change the headers and columns to customise the sheet. You can also ask for help in our support channel in Discord.
Nearly there, next you need to click the save icon (or ctrl+s) to save. Finally, you need to run the βmainβ function (the default should be 'main'). In the top bar, click the dropdown next to βDebugβ and select βMainβ if you are running an old version.
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 show your new transactions!
TIP: You can add optional columns at the end, change header names, styles etc, but other than that changing the order of columns etc might break the script. You hide columns using the βHide Columnβ feature but do not hide the transaction ID
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.
Add automation to your Google sheets
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 suitable for you. This basically means how often the sheets will update. We recommend choosing 30 minutes as a minimum time period. For this tutorial we chose 1 hour.
Hit save! Congratulations, you've successfully connected Helio to Google Sheets for automatic transaction updates π₯³
Please run the the script manually to ensure it works and populates with the required data. Once that is good, then enable the trigger and watch your payments data arrive at the interval set.
If you have any further questions, please jump in to our Discord to open a ticket or chat in the support channels.