Hello everyone,

This is the first time that I am writing about coding here and I hope that this post will be useful for someone.

A little bit about my background for context: I am a computer engineer, but I have never worked in this area, since I finished university, I have been working as a control systems engineer. It is not a very different area, but I don’t do that much coding daily. A couple of years ago I decided to go back to coding for some projects that I want to develop. Since then, I started studying Python and then created a discord bot using this language. Now I’m studying JavaScript and reading the Hive documents to learn how to interact with the blockchain. And because I’m having a hard time to understand the documentation and how to achieve certain tasks, I wanted to write this post that might be able to help someone in the same position.

The first thing that I wanted to do to start learning about how to deal with the blockchain was to get some historical data about transfers made to my account. This is supposedly a simple task but it took me some time to understand how to read this data. My idea was to do this from inside a Google Sheets file so I wouldn’t be able to use any of the libraries available, I would have to access the API directly. After a few tests and talking to some people that had done similar things before (thanks @h3m4n7), I finally pulled the information I wanted into the spreadsheet.

For people who haven’t worked with the scripts in Google Sheets before, from a new spreadsheet, just click on the menu Extensions > Apps Script to write your function.

The first few lines of the function were to configure the rows and columns of the spreadsheet to have a header for the table.

Next, I created a variable to save the account name that will be accessed. This will be improved in the next version of my code to be more dynamic and get other account names from the spreadsheet cells.

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1).setValue("Date");
  sheet.getRange(1,2).setValue("From");
  sheet.getRange(1,3).setValue("To");
  sheet.getRange(1,4).setValue("Amount");
  sheet.getRange(1,5).setValue("Memo");
var acc_name = "nane-qts";

Now it is time to setup the arguments that are going to be sent in the call to the API to get the information. I separated this in 2 parts because I like to have a clear view of the things that I’m configuring.

In the first object, I set up the parameters of the API that will be called, in this case it is the method get_account_history of the API condenser_api. The parameters needed for this API are the account name, the starting point and the number of operations. Here I’m using –1 to get the most recent operations and 1000 is the number of operations (this is the maximum limit that we can get in each call).

In the other object I configured the parameters for the fetch command that will connect with the api.hive.blog to get the information made available by the condenser_api.

var args_json = {
    jsonrpc:"2.0",
    method: "condenser_api.get_account_history",
    params: [acc_name, -1, 1000],
    id: 0
  }
var options = {

'method' : 'post',

'contentType': 'application/json',

// Convert the JavaScript object to a JSON string.

'payload' : JSON.stringify(args_json)

};

After executing the fetch command, I parse the result to an object to make it easier to access the items inside. This part took me a bit of time to understand because the result turns out to be objects inside objects inside arrays and so on. So, I started using the log function to print each item inside the object result until I found the information that I wanted. Then I organized the loop to get each item and write it in the spreadsheet.

var response = UrlFetchApp.fetch("https://api.hive.blog", options);
  var resultado = JSON.parse(response.getContentText());
var result = resultado.result;
for(i=0; i<result.length -1; i++ ){
var itemarr = result[i];

if (itemarr[1].op[0]=="transfer" && itemarr[1].op[1].from!="nane-qts"){

  var row = sheet.getLastRow() + 1;

  sheet.getRange(row,1).setValue(itemarr[1].timestamp);
  sheet.getRange(row,2).setValue(itemarr[1].op[1].from);
  sheet.getRange(row,3).setValue(itemarr[1].op[1].to);
  sheet.getRange(row,4).setValue(itemarr[1].op[1].amount);
  sheet.getRange(row,5).setValue(itemarr[1].op[1].memo);
}    

}

I will leave the complete code here in case someone wants to use it as a base to do their own script.

function GetDataHive() {
var sheet = SpreadsheetApp.getActiveSheet();

sheet.getRange(1,1).setValue("Date");

sheet.getRange(1,2).setValue("From");

sheet.getRange(1,3).setValue("To");

sheet.getRange(1,4).setValue("Amount");

sheet.getRange(1,5).setValue("Memo");
var acc_name = "nane-qts";
var args_json = {

jsonrpc:"2.0",

method: "condenser_api.get_account_history",

params: [acc_name, -1, 1000],

id: 0

}
var options = {

'method' : 'post',

'contentType': 'application/json',

// Convert the JavaScript object to a JSON string.

'payload' : JSON.stringify(args_json)

};
var response = UrlFetchApp.fetch("https://api.hive.blog", options);

var resultado = JSON.parse(response.getContentText());
var result = resultado.result;
for(i=0; i<result.length -1; i++ ){
var itemarr = result[i];

if (itemarr[1].op[0]=="transfer" && itemarr[1].op[1].from!="nane-qts"){

  var row = sheet.getLastRow() + 1;

  sheet.getRange(row,1).setValue(itemarr[1].timestamp);
  sheet.getRange(row,2).setValue(itemarr[1].op[1].from);
  sheet.getRange(row,3).setValue(itemarr[1].op[1].to);
  sheet.getRange(row,4).setValue(itemarr[1].op[1].amount);
  sheet.getRange(row,5).setValue(itemarr[1].op[1].memo);

}

}
}

This task took me a while but it was a great exercise to get me starting to understand how to interact with the blockchain. My next step will be to make the spreadsheet a bit more dynamic, by getting the account name from an input field instead of having it fixed inside the code. Then I will try to get information from hive engine too, to get transfers of other tokens.

I will try to write more posts about this process.

---

---

---