How to use Google Sheets with SteemSQL

[bitrocker2020]

Here is a quick and simple tutorial of using Google Sheets with SteemSQL

A sample query included thanks to @carlgnash for his help with the query.

Why Google Sheets ?

1. Everyone who has a Gmail account will have access to Google Sheets. How to access it is pretty simple, just head to http://drive.google.com and from there you login your Gmail account and you can create a new spreadsheet
2. You can work in a collaboration mode where someone who is really good at writing a SQL query can execute it and then is able to share the results thru the spreadsheet with anyone they wish to.
3. You don't need to install anything, full accessibility thru your desktop / mobile / pads
4. Google Drive is FREE up to 15GB of usage

---

Getting started !

First you would need to open up a spreadsheet from http://drive.google.com then from there you need to click Add-Ons --> Get Add-Ons then search for an add-on called SeekWell

this add-on is not free and has a fee of only USD19 per month. 14 days trial period You would need to purchase the license as we are using SteemSQL which is a Microsoft SQL Server and not MySQL

---

Once you've added the add-on. You can now click Add-Ons --> SeekWell --> Launch

---

You would now be required to put in the credentials of SteemSQL

• Select SQL Server
• Hostname : sql.steemsql.com:1433
• Database : DBSteem
• Username : steemit
• Password : steemit

Thanks to @arcange who has kept SteemSQL consistently up and updated for us to be able to use for analytical purposes.

---

Once you are logged in you can now start to create your query.

Lets start with this scenario. We want to do a quick analysis of who has the most pending payouts over the past few days on the tag utopian-io . So copy and paste this query on the query area

select
 author, 
 count(author) as Posts, 
 sum(net_votes) as Votes, 
 sum(pending_payout_value) as Pending_Payout,
 sum(children) as Comments
from 
 Comments (NOLOCK) 
where 
 dirty = 'False' and
 json_metadata LIKE('%"utopian-io"%') and
 parent_author = '' and
 datediff(day, created, GETDATE()) between 0 and 7
group by 
   author 
order by 
 Pending_Payout desc

You can then click run and it would run the query and display the results in a either the active sheet or a new sheet whichever you select.

---

Your results should look something similar to this.

So now once you've pulled in the data into the spreadsheet you can do whatever you want with it for your analytical purposes. For example thru this query here are the static from what was found for people using the utopian-io tags over the past 7 days

No. of Authors : 671

No. of Posts made : 2,256

Total of votes received on all posts : 43,906

Total no. of comment : 10,873

Total pending payout : $40,229.05

---

Here are the top 30 authors with the most number of posts over the past 7 days using the tag utopian-io

author	Posts	Votes	Pending_Payout	Comments
arcjen02	21.00	170	124.24	38
omeratagun	20.00	310	442.91	79
irfandogan	20.00	285	392.36	46
nuges	20.00	604	272.17	100
realinfo	19.00	220	383.81	82
kalvas	18.00	376	480.89	69
omersurer	17.00	422	346.56	66
myjourney	16.00	174	308.55	31
ranielbrianulan	16.00	308	224.90	63
by-yesilbag	15.00	293	330.64	76
drigweeu	15.00	189	182.01	63
h4ck3rm1k3st33m	15	86	83.604	41
flauwy	14.00	813	551.24	219
aymenz	14.00	154	190.53	71
kizilelma	13.00	150	314.19	31
guinsoo	13.00	184	310.40	42
redjepi	13.00	154	297.34	28
raptorjesus	13.00	107	273.52	36
anwei	13.00	121	271.06	35
anggaariska	13.00	204	220.56	36
ihtiht	13.00	99	196.34	34
mikekenlytungal	13.00	131	131.21	38
holabisi	13.00	155	121.62	60
andravasko	13.00	131	104.59	23
sametceylan	12.00	175	229.94	30
kwonn	12.00	169	227.10	47
gilangarif131294	12.00	85	119.60	27
olaivart	12	149	34.406	90
monomyth	11.00	333	276.03	35

---

I do hope that this quick tutorial was useful for you and especially for those who are curating certain tags and would like to do analysis and then share the results within the community. Cheers and have a great week ahead

---

Posted on Utopian.io - Rewarding Open Source Contributors

---

Comments

[deborism]

Very informative ! So I resteemed :)

[bitrocker2020]

ty :)

[sphenix]

nice one.... this is how they extract the data....tq

[kokuryo]

Superbly useful!

[rimon24]

educational knowledg

[superoo7]

Just Setup on my Desktop with JetBrains DataGrip. Thanks for the articles.

Never use MSSQL before, but is a great chance to test out haha 😆

[bitrocker2020]

nice !!

[explorernations]

Wow, that's something needed for me to take note. Google now has seems to be overtaking everything on the internet. I once heard that the rich get richer while the poor will be bullied to the ground. Seems like Google is expanding with all their capacity to permanent their status globally. Btw, thanks for sharing! #teammalaysia

[bitrocker2020]

oh yea .. they've updated google drive with a lot of new features and its still free !! likw whaaatt ?

[explorernations]

Exactly what I am predicting, soon they will overtake Microsoft. Just the matter of time..

[cryptoboii]

They have already beaten out Microsoft in the cloud services sector. As for mobile phone OS, Google wins. Apps store, Google wins. So far the only market Google hasn't dared to touch is the professional media creation toolset market. Imagine one day Google overtakes Adobe. (Google has already begun working on improving their photos service )

[bitrocker2020]

its just the matter of time before Google Ventures buys a competitor and just sexyfy the entire thing

[rahulsen]

Yeah,,,Great. very valuable post.
Thank you so much for your important post

[bitrocker2020]

cheers @rahulsen

[rahulsen]

Yeah,,,Cheers brother :) :) @bitrocker2020

[kimzwarch]

Great tutorial for a starter like me. hehe

[bitrocker2020]

we all start somewhere :)

[kimzwarch]

yup, thanks boss!

[spiritualmax]

The tutorial is indeed simple... I'm just a hammer head. Either way, I came here to thank you for your contribution as people like you... who are not hammerheads like me... is what keeps the community going.

Cheers

[muhammadkamal]

Verry essential knowledge Which is beneficial for us.
thanks for sharing @bitrocker2020

[sojib24]

It is very good to post it. It needs to be done every day but I do not know how it matters. @bitrocker2020

[spectrums]

oh my god, this is so helpful. thank you!

[scipio]

Your contribution cannot be approved because it does not refer to or relate to an open-source repository. See here for a definition of "open-source."

Explanation:

• Hi @bitrocker2020 , although I also like Google Sheets, I'm sorry but it is not open sourced, but a commercial service (even if you're using the free version, it's still closed source),
• as a Utopian moderator I must "guard" the propoer appliance of the Utopian rules, and the core rule is of course: it must be about open source.

Anyway, in case you like to proceed contributing to Utopian (please feel free to do so!) then I advise you to begin researching GitHub repositories, then find some you like contributing to, and only then begin your contribution work. This way, you are at least sure that it's open source software you are contributing to.

Good luck!

@scipio [mod]

You can contact us on Discord.
[utopian-moderator]

[pakwai]

Awesome stuff! As a programmer, all we need is something to guide us to something that we are familiar on. Then magic happens ;)

Thanks, resteem this as is something useful to refer on for future use.

[howtostartablog]

i can't connect to the database. Need to subscribe for the monthly payment only can connect?

[bitrocker2020]

As of Jan '18 yes you need to subscribe . Before that was free