Getting data from Hive to Google Sheets
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.
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.
Olha só que coisa legal!
Vou tentar fazer também!
!luv
@nane-qts, @crazyphantombr(1/5) sent you LUV. | tools | discord | community | HiveWiki | NFT | <>< daily
Obrigada! Faz sim, é legal qdo a gente consegue entender e lidar com a rede.
!PIZZA
Love your HivePizza footer. May I steal it?
Of course! 😊
I saved it in the assets channel.
!PIZZA
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.
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.
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
lolztoken.com
It was a case of age-related macaroni degeneration.
Credit: reddit
@nane-qts, I sent you an $LOLZ on behalf of @quekery
(1/2)
Thanks for being a supporter of The LOLZ Project.
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!
Discord
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
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
@quekery, the HiQ Smart Bot has recognized your request (2/3) and will start the voting trail.
In addition, @nane-qts gets !WEED from @hiq.redaktion.
For further questions, check out https://hiq-hive.com or join our Discord. And don't forget to vote HiQs fucking Witness! 😻
@nane-qts!
@hiq.smartbot passed you the virtual joint!If you do not want to receive these comments, please reply with !STOP
Gostei! 👏👏👏
!LUV
Obrigada 🙂
!PIZZA
Nice work Nane! This is epic! !PIZZA !PIMP
You must be killin' it out here!
@dibblers.dabs just slapped you with 1.000 PIMP, @nane-qts.
You earned 1.000 PIMP for the strong hand.
They're getting a workout and slapped 1/2 possible people today.
Read about some PIMP Shit or Look for the PIMP District
Thank you 😊
!PIZZA
The post deserves the double !PIMP treatment!
You must be killin' it out here!
@dibblers.dabs just slapped you with 1.000 PIMP, @nane-qts.
You earned 1.000 PIMP for the strong hand.
They're getting a workout and slapped 1/2 possible people today.
Read about some PIMP Shit or Look for the PIMP District
Thank you so much Dibbs ☺️
!LUV
@dibblers.dabs, @nane-qts(1/1) sent you LUV. | tools | discord | community | HiveWiki | NFT | <>< daily
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 🤣.
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
Your post was manually curated by @Zallin.
Delegate your HP to the hive-br.voter account and earn Hive daily!
🔹 Follow our Curation Trail and don't miss voting! 🔹
Congratulations @nane-qts! You have completed the following achievement on the Hive blockchain And have been rewarded with New badge(s)
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:
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.
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 =)
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
This is great work and I am sure many will find it very useful.
Thank you very much 🙂
!PIZZA
You are so welcome.
Glad to be of help :D
!PIZZA for the !QUEEN
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
Thank you 😊
!PIZZA
Wow.. Thanks for Sharing.
Happy to help 🙂
!PIZZA