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 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
The Templates with Commands we Need
-
Users email address and user name.
-
User Stats: Here’s a code template pulling users stats which includes our key one, likes received
-
Users Trust Level: Users scores are multiplied by their trust level so we also want to pull that
-
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
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 for details on using the API.
@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.
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.
I’m pretty sure you did. Let me check
1 Like
You can also begin by reading through here and getting an idea of how the plugin works, and whether it needs to be SQL or supports other code.
Cool, I’ll give it a quick read. And about the templates, I can´t find them either
Ok, here’s the basic plugin skeleton they recommend building into (although we can also use any of the examples and code into it)
We need to pull:
- User Trust Level
-
User Stats - Most importantly how many hearts they’ve received, but the more data the better
-
Custom User Field - 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.
Cool. Do you happen to have the database schema?
1 Like
No, i imagine it’ll be in the Meta.discourse.org thread
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.
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.
You should now be an admin. The address is here: Public Happiness
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?
1 Like
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.
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.
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.
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 
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.
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.
1 Like