Would you like to see your orders automatically show in Google? We support integration with Google Sheets. This tutorial shows you how to connect your Google Sheets with Stream and automate transaction data.
Note: you need to generate your API key in the StreamPayments dashboard to use the Google sheet integration. If you prefer to retrieve your Stream transactions manually, you can export a .csv file by logging in to the StreamPayments 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 previously existing data with new columns and duplicate your data.
Get started
To get started, 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
/*
STREAM PAY SHEETS INTEGRATION v0.2.6
AUTHOR: contact@streamprotocol.org
HOW TO USE:
Add your...
STREAM_PAY_API_KEY (Join our Discord @ https://discord.com/invite/streampay and ask for one)
PUBLIC_KEY (The wallet Public Key you use for StreamPayments, 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 Stream 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 PUBLIC_KEY = "";
const STREAM_PAYMENTS_API_KEY = "";
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 streamTransactions = getRecentTransactions(STREAM_PAY_API_KEY, PUBLIC_KEY);
if (helioTransactions != null) {
console.log('RECIEVED: Transaction data');
writeRecentTransactions(streamTransactions, HEADERS);
} else {
throw new Error('ERROR: could not get stream transactions');
};
}
function getRecentTransactions(API_KEY, PK) {
try {
const response = UrlFetchApp.fetch('https://api.streampayment.app/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 writeRecentTransactions(streamTransactions, 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");
}
console.log("CHECKING FOR NEW TRANSACTIONS");
for (let index = streamTransactions.length -1; index >= 0; --index) {
item = streamTransactions[index];
const transactionIDs = getColumn(sheet, 12);
if (
!transactionIDs.includes(item.id)
) {
const fullSecondRow = sheet.getRange(2, 1, 1, sheet.getMaxColumns());
const secondRow = sheet.getRange(2, 1, 1, headers.length);
fullSecondRow.insertCells(SpreadsheetApp.Dimension.ROWS); //Push rows down by one
secondRow.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();
}
}
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();
}
function getRow(activeSheet, rowIndex) {
return activeSheet.getRange(rowIndex, 1);
}
TIP: If you are a developer you can go ahead and try to change the headers and columns to customize 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!
Congratulations, you have now connected StreamPayments 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 and 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 minutes, or hours. For this tutorial, I chose 1 hour.
Hit save! Congratulations, you've successfully connected StreamPayments to Google Sheets for automatic transaction updates.
If you have any further questions, please jump into our Discord to open a ticket or chat in the support channels.
