Getting data from Hive to Google Sheets

avatar

banner planilha hive.png


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.

divisores-41.png

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.

planilha.png

divisores-41.png

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";

divisores-41.png

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)
};

divisores-41.png

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);
}    

}

divisores-41.png

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);

}

}

}

divisores-41.png

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.


nane-qts

divisores-41.png


banner-hivepizza-04.png


Raven Discord invite.png



0
0
0.000
37 comments
avatar
(Edited)

PIZZA!
The Hive.Pizza team manually curated this post.

$PIZZA slices delivered:
nane-qts tipped h3m4n7
nane-qts tipped vaipraonde
nane-qts tipped crazyphantombr
dibblers.dabs tipped nane-qts
nane-qts tipped quekery
nane-qts tipped zallin
h3m4n7 tipped nane-qts
nane-qts tipped hivetrending
nane-qts tipped coinjoe
nane-qts tipped gwajnberg
nane-qts tipped dibblers.dabs
@nane-qts(2/20) tipped @ceedrumz

Learn more at https://hive.pizza.

0
0
0.000
avatar

Love your HivePizza footer. May I steal it?

0
0
0.000
avatar

Of course! 😊
I saved it in the assets channel.

!PIZZA

0
0
0.000
avatar

Last week I started to learn Javascript too. And as I told you for Python I use the libraries beem and hiveeingine.

Looks like you use no libraries for Javascript. That's interesting. For my Javascript experiments I tried dhive and hive-js. Dhive was a little bit tricky for me because the lib uses promises.

var client = new dhive.Client(["https://api.hive.blog", "https://api.hivekings.com", "https://anyx.io", "https://api.openhive.network"]);

let user = client.database.getAccounts(['quekery'])

user.then(
    function(value) { console.log(value[0].hbd_balance) },
    function(error) { /* code if some error */ }
  );

And I also made a small script for sending Hive with Keychain.

After that I searched for a library for Hive Engine and found ssc and dhive-sl. But dhive-sl needs a server installation so I tried ssc. Not as good as hiveengine but it works.

const ssc = new SSC('https://api.hive-engine.com/rpc');
ssc.find('tokens', 'balances', { account: 'quekery'}, 1000, 0, [], (err, result) => {

    //console.log(err, result);
    let tokens = result
    for (let token of tokens) {
        if (token.symbol=='CCD'){
            console.log(token.balance+token.symbol)
        } else if (token.symbol=='SQM') {
            console.log(token.balance+token.symbol)
        } else if (token.symbol=='LGN') {
            console.log(token.balance+token.symbol)
        } else if (token.symbol=='PIMP') {
            console.log(token.balance+token.symbol)
        }
        
    }
    
})

I'm looking forward to see some code for Hive Engine calls without a lib. BTW. for Hive Engine calls I made the experience that it is really helpful to know Mongodb queries. But in the code above I didn't used any fancy Mongodb queries. !LOL

!hiqvote

0
0
0.000
avatar

We like the way you play the game! !PGM
Thanks for being a supporter of The LOLZ Project.

0
0
0.000
avatar

Sent 0.1 PGM - 0.1 LVL- 1 STARBITS - 0.05 DEC - 1 SBT - 0.1 THG - 0.000001 SQM - 0.1 BUDS - 0.01 WOO tokens

remaining commands 14

BUY AND STAKE THE PGM TO SEND A LOT OF TOKENS!

The tokens that the command sends are: 0.1 PGM-0.1 LVL-0.1 THGAMING-0.05 DEC-15 SBT-1 STARBITS-[0.00000001 BTC (SWAP.BTC) only if you have 2500 PGM in stake or more ]

5000 PGM IN STAKE = 2x rewards!

image.png
Discord image.png

Support the curation account @ pgm-curator with a delegation 10 HP - 50 HP - 100 HP - 500 HP - 1000 HP

Get potential votes from @ pgm-curator by paying in PGM, here is a guide

I'm a bot, if you want a hand ask @ zottone444


0
0
0.000
avatar

I see you have been keeping busy too. I think in the long term, JavasScript will be very useful. I really like Python, but for some types of projects, JavaScript is a better option.
I'm planning to make some calls to Hive Engine, don't have a clue yet how to do it without a library, but will find a way. 😄
I am using Mongodb for my bot, but only the most basic stuff. I'm curious to see what else you are doing with it.

!PIZZA

0
0
0.000
avatar

Nice work Nane! This is epic! !PIZZA !PIMP

0
0
0.000
avatar

Thank you 😊

!PIZZA

0
0
0.000
avatar

The post deserves the double !PIMP treatment!

0
0
0.000
avatar

Owww irado, parabéns Nane, to só esperando terminar o Python para começar a me aventurar com java, to com medo de bagunçar minha cabeça 🤣.

0
0
0.000
avatar

Já te adianto que vai bagunçar um tanto. 🤣
JavaScript tem um jeito próprio de fazer as coisas. Ainda estou tentando entender como as coisas funcionam nele.
Mas o Python é uma ótima porta de entrada pra programação. É uma linguagem mais simples e intuitiva de usar.
Mas vai ser mto útil pra vc pegar o JS tb. Nessa vida a gente precisa ser poliglota nas linguagens de programação. 😄

!PIZZA

0
0
0.000
avatar

Congratulations @nane-qts! You have completed the following achievement on the Hive blockchain And have been rewarded with New badge(s)

You got more than 2250 replies.
Your next target is to reach 2500 replies.

You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Check out our last posts:

Hive Power Up Month Challenge - May 2023 Winners List
Be ready for the June edition of the Hive Power Up Month!
Unveiling the Exclusive Web3 Berlin Conference Badge. HiveBuzz Adds a Touch of Excitement!
0
0
0.000
avatar

Thanks for your contribution to the STEMsocial community. Feel free to join us on discord to get to know the rest of us!

Please consider delegating to the @stemsocial account (85% of the curation rewards are returned).

You may also include @stemsocial as a beneficiary of the rewards of this post to get a stronger support. 
 

0
0
0.000
avatar

Jizz! I didn't know that we could do that in google sheets! But I had this idea of coding inside these sheets tool!! Maybe you should use something like VS code and generate a sheet from this code! That will allow you more flexibility and the help of an IDE =)

0
0
0.000
avatar

Yeah, there are some cool things we can do with a simple google sheets 😁
The idea to use google sheets was to have a simple interface to start with and also to be easy to share the file with other people. But I have plans to create more elaborate stuff. Just need to organise my time. ;-)

!PIZZA

0
0
0.000
avatar

This is great work and I am sure many will find it very useful.

0
0
0.000
avatar

Glad to be of help :D

!PIZZA for the !QUEEN

0
0
0.000
avatar

Keep up the great work Queen @nane-qts, h3m4n7(1/17) is impressed by the thought and consideration you put into this post. Your work is truly appreciated.

We are so impressed by your content! As a token of appreciation, @h3m4n7 has sent you 0.05 SOULS. Keep shining!

BTW! with SOULS you can access our infernal coliseum game, conquer territories and earn rewards


0
0
0.000