# \[Token distribution\] Pulling user stats into a Google sheet
**URL:** https://publichappinessmovement.com/t/topic/3099
**Category:** Ruby on Rails, Ember and JS (Discourse and Docuss)
**Tags:** blockchain, sql, ruby-on-rails, done, webdev
**Created:** [22 September 2022 17:11 UTC](https://publichappinessmovement.com/t/topic/3099 "2022-09-22T17:11:25Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [22 September 2022 17:11 UTC](https://publichappinessmovement.com/t/topic/3099/1 "2022-09-22T17:11:25Z")
Edit: 1st post updated with links and important information for later in this thread
Aim: To pull specific stats and user fields from the platform into a Google Sheet in our Drive Folder
**The Database Explorer**
Using the [Discourse Data Explorer Plugin](https://meta.discourse.org/t/discourse-data-explorer/32566) we can specify exactly what stats we want to pull from the platform and import them into a google sheet.
I’ve collected a few examples of codes which pull the data we need. I’m not skilled enough to combine them into our own command though.
[You can create/edit commands to the database here](https://publichappinessmovement.com/admin/plugins/explorer)
**The Templates with Commands we Need**
1. Users email address and user name.
2. User Stats: [Here’s a code template pulling users stats which includes our key one, likes received](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/user-stats.sql)
3. Users Trust Level: Users scores are multiplied by their trust level so we also want to [pull that](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/trust-level-locked.sql)
4. Custom Field - Wallet ID: All users have a custom field where they can add their crypto wallet. This needs to be pulled into our google sheet to tell our token where to distributes newly minted tokens each month.
[Here’s a good read on working with custom fields](https://meta.discourse.org/t/displaying-user-custom-fields-in-data-explorer-query-results/76648)
I’ll set up a second google sheet to pull the data from the 1st providing a little extra security and allowing me to apply functions on the data without touching and risking interfering with the automated one. It also gives us more granular control, so we can in the future for example:
- Multiply their stats by their current user level
- Add weight to stats which are identified as being more critical to growing the community, or as an incentive for more people to join groups which need more activity.
It would be great to automate this to pull data every month, ~~but other users on the plugin don’t think that is possible.~~ and it is possible by setting up API keys.
> You’d need to give the Google Script an API Key. You can restrict that API Key to only be allowed to run data explorer queries, and you can further restrict it to only be able to run specific queries. You are correct, Google Script (or anything external) cannot simply run a data explorer query.
>
> Check out /admin/api/keys/new and the “Granular” scope level. Scroll down to see the data explorer scope. See [docs.discourse.org](http://docs.discourse.org) for details on using the API.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [22 September 2022 17:11 UTC](https://publichappinessmovement.com/t/topic/3099/2 "2022-09-22T17:11:37Z")
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [23 September 2022 10:17 UTC](https://publichappinessmovement.com/t/topic/3099/3 "2022-09-23T10:17:50Z")
@pablodclavijo Its basically combining the two examples, but because they’ve been written in a different structure i found it a bit confusing having never written SQL before.
We want to pull:
- all users stats which one example already does so no modification should be needed
- a custom user field, which the second example already does
- users trust level, which may or may not already be included in the 1st set of stats. If not we’ll need to work that out
A second element has just been added with creating api keys to automate the process which i probably can handle myself, but it’d be great if you have any experience with api’s so i can share any issues i run into.
Let me know what time you’re planning to work on it and i’ll try to be online as well so we can support each other.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:14 UTC](https://publichappinessmovement.com/t/topic/3099/4 "2022-10-01T21:14:45Z")
I’m sorry @pablodclavijo, i can’t seem to find the links to the two templates we were going to use. Did i send them to you before?
If not i’ll check through my browser history for them.
---
### Author:  [@pablodclavijo](https://publichappinessmovement.com/u/pablodclavijo)
#### Post date: [1 October 2022 21:15 UTC](https://publichappinessmovement.com/t/topic/3099/5 "2022-10-01T21:15:33Z")
I’m pretty sure you did. Let me check
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:16 UTC](https://publichappinessmovement.com/t/topic/3099/6 "2022-10-01T21:16:32Z")
You can also begin by reading [through here](https://meta.discourse.org/t/discourse-data-explorer/32566) and getting an idea of how the plugin works, and whether it needs to be SQL or supports other code.
---
### Author:  [@pablodclavijo](https://publichappinessmovement.com/u/pablodclavijo)
#### Post date: [1 October 2022 21:18 UTC](https://publichappinessmovement.com/t/topic/3099/7 "2022-10-01T21:18:57Z")
Cool, I’ll give it a quick read. And about the templates, I can´t find them either
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:20 UTC](https://publichappinessmovement.com/t/topic/3099/8 "2022-10-01T21:20:41Z")
Ok, here’s the [basic plugin skeleton](https://github.com/discourse/discourse-plugin-skeleton) they recommend building into (although we can also use any of the examples and code into it)
We need to pull:
1. [User Trust Level](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/trust-level-locked.sql)
2. [User Stats](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/user-stats.sql) - Most importantly how many hearts they’ve received, but the more data the better
3. [Custom User Field](https://meta.discourse.org/t/how-to-create-and-configure-custom-user-fields/113192) - to pull their wallet id
All of these should be linked together as one user if possible so we’re just working with one row of the google sheet. That’ll make the latter part of the task easier.
---
### Author:  [@pablodclavijo](https://publichappinessmovement.com/u/pablodclavijo)
#### Post date: [1 October 2022 21:29 UTC](https://publichappinessmovement.com/t/topic/3099/9 "2022-10-01T21:29:04Z")
Cool. Do you happen to have the database schema?
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:29 UTC](https://publichappinessmovement.com/t/topic/3099/10 "2022-10-01T21:29:58Z")
No, i imagine it’ll be in the [Meta.discourse.org](http://Meta.discourse.org) thread
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:30 UTC](https://publichappinessmovement.com/t/topic/3099/11 "2022-10-01T21:30:57Z")
I’ll make you an admin of the platform so you can access the entry field where we’re coding. The schema might also be there.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:32 UTC](https://publichappinessmovement.com/t/topic/3099/12 "2022-10-01T21:32:07Z")
One… quirk is that you can only type one letter at a time into the admin section due to a Docuss bug that i’ve never been troubled about enough to focus on fixing. Its best to type anything you want to type in admin somewhere else and then copy/paste it in.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:33 UTC](https://publichappinessmovement.com/t/topic/3099/13 "2022-10-01T21:33:44Z")
You should now be an admin. The address is here: [Public Happiness](https://publichappinessmovement.com/admin/plugins/explorer)
---
### Author:  [@pablodclavijo](https://publichappinessmovement.com/u/pablodclavijo)
#### Post date: [1 October 2022 21:37 UTC](https://publichappinessmovement.com/t/topic/3099/14 "2022-10-01T21:37:58Z")
I am.
Ok so to reiterate, if that’s how it’s spelled, the task consists in creating a discourse plugin that:
- Queries the database and selects a certain set of data from every user
- Returns said set as a single row
- Copies it onto a google spreadsheet
Am I missing something else?
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:39 UTC](https://publichappinessmovement.com/t/topic/3099/15 "2022-10-01T21:39:13Z")
So the basic idea is that if someone does something on this platform which advances a task, supports the community, or advances the mission, users are instructed to throw them a heart. We’re going to collect those up to show who is contributing most here each month.
So hearts are most important, but grabbing more data means we can get more granular as we need, or change what tokens are rewarded for if hearts are being used incorrectly by most users.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:41 UTC](https://publichappinessmovement.com/t/topic/3099/16 "2022-10-01T21:41:10Z")
Yes, that’s it. Although the plugin already exists, we just need to put the right commands into it. The three linked examples are pulling the exact data we want so they should contain the… um, calling string for everything we want to put into our commands.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:42 UTC](https://publichappinessmovement.com/t/topic/3099/17 "2022-10-01T21:42:01Z")
My issue with the task is that the format of the examples is different and it got all messy when i tried working out how to combine it myself, as i’m not a dev.
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:46 UTC](https://publichappinessmovement.com/t/topic/3099/18 "2022-10-01T21:46:09Z")
Once you’re running on that i’ll start looking at our token and begin working out how to mint it to addresses collected into our google sheet. I may need technical support when working out how to add a value to the amount minted, or i may not. I’ll find out when i get there.
After that the final task is playing around with a second google sheet to pull the data we want and manipulate it into the format needed for the token distribution.
Then we’ll have achieved having a live token which is earned by contributing to this platform. A very cool 1st task 🕶
---
### Author:  [@AndyatFocallocal](https://publichappinessmovement.com/u/AndyatFocallocal)
#### Post date: [1 October 2022 21:48 UTC](https://publichappinessmovement.com/t/topic/3099/19 "2022-10-01T21:48:06Z")
Oh, and you mentioned you had an interest in security so as we build have an eye open to how you’d suggest building the more secure system which replaces this, and you can jot down thoughts while the ideas are still fresh even though we won’t need to build it for a while yet.
---
### Author:  [@pablodclavijo](https://publichappinessmovement.com/u/pablodclavijo)
#### Post date: [1 October 2022 21:52 UTC](https://publichappinessmovement.com/t/topic/3099/20 "2022-10-01T21:52:27Z")
Okay, great. I think I have everything I need to start, so I’ll get on it. I know is like 11 pm in Portugal so don’t feel like you have to stick around.
[Next page](https://publichappinessmovement.com/t/topic/3099.md?page=2)