Creating Transactions from Spreadsheets
This tutorial demonstrates how to execute different Algorand transactions from an imported Excel spreadsheet. Three transaction types are covered. First, is a standard pay transaction, then a closeToRemainder transaction, which transfers all the assets from one account to the other. Last, is creating an Algorand Standard Assets (ASA). The spreadsheet is formatted with all the required details. A set of conditions are used to check the type of transactions that will be performed based on a set of parameters.
JavaScript
Requirements
- Algorand Javascript SDK
- Development environment for JavaScript, suchas Xampp
- Algorand accounts, funded from the testnet dispenser
- The AlgoExplorerapi for getting and posting transactions to TestNet
- JavaScript Excel reader
Background
Sometimes an organization may want to batch multiple transactions to different accounts. Performing these transactions one at a time could be time consuming. This tutorial demonstrates how this can be automated using a formatted Excel spreadsheet. A master account which owns the assets performs the transactions. For security reasons, the asset balance can be transferred to a new account after performing the batch transactions. Pay transaction, closeToRemainder transaction and asset creation have been demonstrated. The principles can be applied to rekying and bidding once you set the right conditions in the Excel sheet. A sample Excel sheet has been created with the headers and accepted parameters. These can be changed to fit your unique coding needs. A simple html/JavaScript web page is used to demonstrate the functionality.
Steps
1. Setup header and body of script
In this section, we include the Algorand JavaScript SDK, the xlsx reader and the form to support the Excel file upload.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta http-equiv="X-UA-Compatible" content="ie=edge" />
<title>Document</title>
<script src="scripts/algosdk.min.js"></script>
<script
type="text/javascript"
src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.3/xlsx.full.min.js"
></script>
</head>
<body>
<input type="file" id="fileUpload" accept=".xls,.xlsx" /><br />
<button type="button" id="uploadExcel">Convert</button>
<pre id="jsonData"></pre>
</body>
<script>
2. Get account details from Mnemonic passphrase
Input the mnemonic passphrase to get the account that is signing the transactions. Also, declare 2 constants which are the AlgoExplorer API endpoints for GET and POST calls.
< var mnemonic = "side kidney book discover amused grunt anchor run vessel insane melody violin entire dynamic cloth fog dry deputy process since barely brass tuna absorb crawl";
let account = algosdk.mnemonicToSecretKey(mnemonic);
address = account.addr;
const url = "https://api.testnet.algoexplorer.io/v2/transactions/params";
const traxUrl = "https://api.testnet.algoexplorer.io/v2/transactions";
3. The makePayments, createAsset and Get functions
These functions are used to perform different transaction types throughout the script.
- makePaymets() function handles payment transactions types for payments and closeToRemainder payment. It first performs all payment transactions that are within the account asset balance. After these transactions are completed, the remainder of the balance is transfered to the other account. The closeToRemainder parameter on the excel sheet should be set to 1 for that column. The first column to meet the condition will be processed. The script makes use of the SDK makePaymentTxnWithSuggestedParams() in both cases to process the payments.
function makePayments (payAddress, payAmount, payNote, payCloseRemainderTo,account){
fetch(url)
.then(response => response.json())
.then(params=> {
firstRound = params["last-round"];
lastRound = params["last-round"] + 1000;
genesisID = params["genesis-id"];
genesisHash = params["genesis-hash"];
params.fee= 1000;
amount = payAmount;
let note = algosdk.encodeObj(payNote);
address = account.addr;
let suggestedParams = {
"flatFee": true,
"fee": params.fee,
"firstRound": firstRound,
"lastRound": lastRound,
"genesisID": genesisID,
"genesisHash": genesisHash,
};
if (payCloseRemainderTo ===1){
let txn = algosdk.makePaymentTxnWithSuggestedParams(address, payAddress, amount, payAddress, payNote, suggestedParams);
let signedTxn = txn.signTxn(account.sk);
console.log(signedTxn);
fetch(traxUrl, {
method: 'POST', // or 'PUT'
headers: {
'Content-Type': 'application/x-binary',
},
body: signedTxn,
})
.then(response => response.json())
.then(data => {
console.log(data);
})
.catch((error) => {
console.error('Error;', error);
});
}
else{
let txn = algosdk.makePaymentTxnWithSuggestedParams(address, payAddress, amount, undefined, note, suggestedParams);
let signedTxn = txn.signTxn(account.sk);
fetch(traxUrl, {
method: 'POST',
headers: {
'Content-Type': 'application/x-binary',
},
body: signedTxn,
})
.then(response => response.json())
.then(data => {
console.log(data);
})
.catch((error) => {
console.error('Error:', error);
});
}
})
;
}
- createAsset(): This function is responsible for creating the ASA as set out in the Excel sheet. The function accepts the asset creation parameters and begins the process to create the assets. The function is illustrated below.
function createAsset(totalIssuance, decimals,reserve,freeze, clawback,manager, unitName, assetName,noteAsset,assetURL){
fetch(url)
.then(response => response.json())
.then(params=> {
var firstRound = params["last-round"];
var lastRound = params["last-round"] + 1000;
var genesisID = params["genesis-id"];
var genesisHash = params["genesis-hash"];
var fee= 1000;
let note = algosdk.encodeObj(noteAsset);
var addr = account.addr;
let assetMetadataHash = undefined;
let defaultFrozen = false;
let txn_asset = algosdk.makeAssetCreateTxn(addr, fee, firstRound, lastRound, note,
genesisHash, genesisID, totalIssuance, decimals, defaultFrozen, manager, reserve, freeze, clawback,
unitName, assetName, assetURL, assetMetadataHash);
let signedTxnAsset = txn_asset.signTxn(account.sk);
fetch(traxUrl, {
method: 'POST', // or 'PUT'
headers: {
'Content-Type': 'application/x-binary',
},
body: signedTxnAsset,
})
.then(response => response.json())
.then(data => {
return data;
console.log(data);
})
.catch((error) => {
console.error('Error:', error);
});
});
}
- Get(): For most parts of the script, the fetch() function is used to make
GETandPOSTcalls to API. However, an alternative function for is provided for those not comfortable with the fetch() function.
function Get(yourUrl){
var Httpreq = new XMLHttpRequest(); // a new request
Httpreq.open("GET",yourUrl,false);
Httpreq.send(null);
return Httpreq.responseText;
}
4. Process uploaded Excel file
When the file is uploaded, the scrip begins to process the spreadsheet rows. It reads all the rows and puts each into an array object.
//Begin processing form input to get excel file
var selectedFile;
document
.getElementById("fileUpload")
.addEventListener("change", function(event) {
selectedFile = event.target.files[0];
});
document
.getElementById("uploadExcel")
.addEventListener("click", function() {
if (selectedFile) {
var fileReader = new FileReader();
fileReader.onload = function(event) {
var data = event.target.result;
var workbook = XLSX.read(data, {
type: "binary"
});
workbook.SheetNames.forEach(sheet => {
let rowObject = XLSX.utils.sheet_to_row_object_array(
workbook.Sheets[sheet]
);
var jsonObject = JSON.stringify(rowObject);
5. Loop through jsonObject array
After looping through, each row field will be processed separately based on the payment/transaction type. This is how the object array looks like from the file I ran.
[{"amount":5000000,"note":"John Eric","rev":"2WEXHKWRYK6MQLNTW7GMDX72ZKAW3TVJ52SZ2EDAE23OF2Q34UUOGGT7CY","close_remainder_to":0,"type":"pay"},{"amount":3000000,"fee":1,"note":"Ben Eri","rev":"GGY4WEN5FLISHFSXEPAELC6JWFB7R3UVTB3DWBPFUCBXZ3J26I2LDVCYZM","close_remainder_to":0,"type":"pay"},{"amount":0,"note":"Jared Jo","rev":"6NGU52ZU3XPRH5QJFBFG62H3FNGGGOHOSP462RICAFZCKII56ZMYEFV5UU","close_remainder_to":1,"type":"pay"},{"type":"asset","addr":"GGY4WEN5FLISHFSXEPAELC6JWFB7R3UVTB3DWBPFUCBXZ3J26I2LDVCYZM","defaultFrozen":false,"totalIssuance":1000000,"decimals":4," reserve ":"6NGU52ZU3XPRH5QJFBFG62H3FNGGGOHOSP462RICAFZCKII56ZMYEFV5UU","freeze ":"6NGU52ZU3XPRH5QJFBFG62H3FNGGGOHOSP462RICAFZCKII56ZMYEFV5UU","clawback":"2WEXHKWRYK6MQLNTW7GMDX72ZKAW3TVJ52SZ2EDAE23OF2Q34UUOGGT7CY","manager":"2WEXHKWRYK6MQLNTW7GMDX72ZKAW3TVJ52SZ2EDAE23OF2Q34UUOGGT7CY","unitName":"ESG6","assetName":"mtoken","assetURL":"blockghana.org"}]
//loop through object aboveto get all excel data and process them one after the other
for(var i = 0; i < rowObject.length; i++) {
var payType = rowObject[i].type;
var payAddress = rowObject[i].rev;
var isValid = algosdk.isValidAddress(payAddress);
var payAmount= rowObject[i].amount;
var payAmountConvert = payAmount/1000000;
var payFee = rowObject[i].fee;
var payNote1 = rowObject[i].note;
var payNote = algosdk.encodeObj(payNote1);
var payCloseRemainderTo = rowObject[i].close_remainder_to;
if (payType == "pay" && payCloseRemainderTo !==1) {
amountUrl = "https://api.testnet.algoexplorer.io/v2/accounts/"+address;
var json_obj = JSON.parse(Get(amountUrl));
amountCheck = json_obj.amount/1000000;
if ( isValid ==true && amountCheck >payAmountConvert){
makePayments (payAddress, payAmount, payNote, payCloseRemainderTo,account);
}
else{continue;}
}
else if (payType == "asset") {
// skip the row if any of these row columns are empty: the asset address, the asset name and the asset unitname
if (!rowObject[i].addr || !rowObject[i].assetName || !rowObject[i].unitName ){
continue;
}
let totalIssuance =rowObject[i].totalIssuance ;
let decimals = rowObject[i].decimals;
let reserve = rowObject[i].reserve ;
let freeze = rowObject[i].freeze ;
let clawback = rowObject[i].clawback;
let manager = address;
let unitName = rowObject[i].unitName;
let assetName = rowObject[i].assetName;
let noteAsset = rowObject[i].noteAsset;
let assetURL = rowObject[i].assetURL;
//createAsset(totalIssuance, decimals,reserve,freeze, clawback,manager, unitName, assetName,noteAsset,assetURL)
}
else if(payType =="pay" && payCloseRemainderTo ===1){
if ( isValid ==true ){
makePayments(payAddress, payAmount, payNote, payCloseRemainderTo,account);
}
else{continue;}
}
}
});
};
fileReader.readAsBinaryString(selectedFile);
}
});
</script>
</html>
6. Complete Code
View the entire script on the GitHub repo. It also contains the dependencies such as the excel file and the Algorand JavaScript SDK.
https://github.com/bayuobie/Algorand-transactions-from-excel