Tutorials
No Results
Tutorial

Intermediate · 1 hour

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.

Requirements

  1. Algorand Javascript SDK
  2. Development environment for JavaScript, suchas Xampp
  3. Algorand accounts, funded from the testnet dispenser
  4. The AlgoExplorerapi for getting and posting transactions to TestNet
  5. 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 GET and POST calls 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.

Sample Transaction file

EditorImages/2021/01/13 21:33/excel.png

//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

algorand standard assets

javascript

January 20, 2021