VEX Forum User Data: 5000 Users' Post to Like Ratios and Much More!

In April, I posted my primitive spreadsheet of random users’ data, collected by manually typing it into the spreadsheet, in Post to like ratio. @Foster suggested

and gave me the Discourse API documentation to study. I had never bothered with APIs before and was moderately bewildered, but I put on my mental list of things to try some time.

Two months later, I ran into APIs again. I googled it, and after a week or two of playing with APIs, I figured I should try the VEX API. I wrote a short script attached to a Google Sheet to fetch
function putUserDataInSpreadsheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  var periodOptions = ['daily', 'weekly', 'monthly', 'quarterly', 'yearly', 'all']
  var period = periodOptions[5]
  var orderOptions = ['likes_received', 'likes_given', 'topic_count', 'post_count', 'topics_entered', 'posts_read', 'days_visited']
  var order = orderOptions[6]
  var search = 'https://www.vexforum.com/directory_items.json?period='+period+'&order='+order+'&page='
  var pages = 100
  for (var page = 0; page < pages; page++) {
    var response = JSON.parse(UrlFetchApp.fetch(search+page))
    var userObjectList = response.directory_items
    Logger.log('Number of users: '+userObjectList.length+'. First user: '+userObjectList[0].user.username+'. Last user: '+userObjectList[userObjectList.length-1].user.username+'.')
    var tableOfUsers = userObjectList.map(function (userObj) {
      return [...[userObj.user.username, userObj.user.name], ...listify_(userObj, ['id', 'likes_given', 'likes_received', 'post_count', 'posts_read', 'topic_count', 'topics_entered', 'days_visited', 'time_read'])]
    })
    Logger.log(tableOfUsers.join('\n- '))
    
    sheet.getRange((page*50)+3, 1, tableOfUsers.length, tableOfUsers[0].length).setValues(tableOfUsers)
  }
  sheet.getRange('A1').setNote('Last updated: '+new Date().toUTCString('en-US', {dateStyle: 'short', timeStyle: 'medium', timeZoneStyle: 'short'})+'\nSorted by: '+order+'\nPeriod: '+period+'\nNumber of users pulled: '+(pages*50))
}

/**
 * Takes [[object]] and returns a list with elements of [[format]] replaced with their respective properties of [[object]].
 * @param {object} object Original.
 * @param {string[]} format Property names of [[object]] in order you want them.
 * @return {any[]} format
 */
function listify_(object, format) {
  return format.map(function (property) {
    return object[property]
  })
}

https://www.vexforum.com/directory_items.json?period=all&order=days_visited&page={number from 0 to 40}

I installed JSON Viewer on the suggestion of @Noah10851B in a really old topic and when I visited that, I saw

I ran the script to pull that page, reformat it into a 2D array, and paste it into the spreadsheet, over and over... a hundred times.

Then I added a bunch of columns to calculate ratios, averages, times, and anything else I could think of.

So now I present to you…
VEX_Forum_User_Data
(Click it!)

I added a “Comments” tab you can see on the bottom, so if you have any comments, questions, or suggestions, add them there or PM me.

Have fun!

28 Likes

discourse api my beloved

5 Likes

This is pretty cool work, I appreciate the time it took to go from ground zero to a final spreadsheet. Thanks for making the effort!

5 Likes

very cool spreadsheet!

2 Likes

If you do something and all the formula columns slowly disappear, check or uncheck a box and they should come back after a bit.

I sorted by average likes per post:

Username Name User ID Likes Given Likes Received Posts Created Posts Read Topics Created Topics Entered Days Visited Seconds Read Time Read Time Read Likes/Post % of Posts Read Liked Posts Read/ Posts Created Posts Read/ Topic Minutes Read/Topic Likes Given/ Topic Minutes Read/ Day Posts Read/ Day Seconds to Read Post
WiscoBots UW-Platteville VEXU 27462 2 69 0 135 2 25 14 2092 0:35 34.5 1.48% Infinity Check the box! 1.4 0.08 2 9.6 15
NorCal-Robotics 22996 0 95 2 108 3 13 6 3731 1:02 19 0% 54 4.8 0 10 18 35
DanMantz DanMantz 17452 89 4070 216 16017 21 766 699 296270 82:18 3d 10h 17m 17.17 0.56% 74.2 6.4 0.12 7 22.9 18
3141B Ungenannt 27575 1450 1343 76 16565 3 1043 328 170084 47:15 1d 23h 14m 17 8.75% 218 2.7 1.39 9 50.5 10
Illyana 21272 40691 9588 690 153459 4 12250 1215 3807761 1057:43 13d 1h 42m 13.82 26.52% 222.4 5.2 3.32 52 126.3 25

WiscoBots leads by 15.4 likes/post, with a total of 69 likes. I think we can say DanMantz is most popular, even though he’s in third, since he leads the other two combined by 3,906 total likes.

I’m going to add a column for likes^2/post, similar to @karmanyaahm in Post to like ratios.

6 Likes

Top users in likes^2/post:

Username Name User ID Likes Given Likes Received Posts Created Posts Read Topics Created Topics Entered Days Visited Seconds Read Time Read Time Read Likes/Post % of Posts Read Liked Posts Read/ Posts Created Posts Read/ Topic Minutes Read/Topic Likes Given/ Topic Minutes Read/ Day Posts Read/ Day Seconds to Read Post Likes^2/Post
Xenon27 99999V - SCA 20424 7671 26159 4757 157055 44 12327 1249 5299523 1472:05 2d 8h 5m 5.45 4.88% 33 12.7 7.2 0.62 71 125.7 34 142531
9MotorGang 67101C 26191 8225 15489 1737 78705 66 5079 883 1044406 290:07 12d 2h 6m 8.59 10.45% 45.3 15.5 3.4 1.62 20 89.1 13 133061
Illyana 21272 40691 9588 690 153459 4 12250 1215 3807761 1057:43 13d 1h 42m 13.82 26.52% 222.4 12.5 5.2 3.32 52 126.3 25 132464
DRow DRow 357 537 11666 1017 70680 123 6134 1128 954241 265:04 11d 1h 4m 10.23 0.76% 69.5 11.5 2.6 0.09 14 62.7 14 119382
Taran Taran 19442 3095 19459 3961 77378 179 6537 1210 2590329 719:32 29d 23h 32m 4.7 4% 19.5 11.8 6.6 0.47 36 63.9 33 91462
DanMantz DanMantz 17452 89 4070 216 16017 21 766 699 296270 82:18 3d 10h 17m 17.17 0.56% 74.2 20.9 6.4 0.12 7 22.9 18 69894
RoboJoe Joe 27624 2159 4218 416 19550 4 732 373 362962 100:49 4d 4h 49m 10.04 11.04% 47 26.7 8.3 2.95 16 52.4 19 42361
Str0ngkatTh1rteen 23051 10813 3214 248 49250 7 2786 703 650496 180:42 7d 12h 41m 12.6 21.96% 198.6 17.7 3.9 3.88 15 70.1 13 40509
kmmohn Michael Mohn 3111 3206 8617 2140 110773 43 9781 1232 1406522 390:42 16d 6h 42m 3.95 2.89% 51.8 11.3 2.4 0.33 19 89.9 13 34014
Connor 14849 4227 10673 3436 71610 183 5694 1191 1472935 409:09 17d 1h 8m 2.95 5.9% 20.8 12.6 4.3 0.74 21 60.1 21 31476
Foster Foster - STEM Robotics 71 2911 11514 4087 69043 432 6722 1227 1806729 501:52 20d 21h 52m 2.55 4.22% 16.9 10.3 4.5 0.43 25 56.3 26 29337
Mentor_355v 20426 3090 5448 962 76307 58 6692 1191 1231515 342:05 14d 6h 5m 5.34 4.05% 79.3 11.4 3.1 0.46 17 64.1 16 29099
Golf Parker D 21823 6853 4437 661 60003 19 3651 915 705561 195:59 8d 3h 59m 6.53 11.42% 90.8 16.4 3.2 1.88 13 65.6 12 28951
lacsap Pascal Chesnais 7317 2892 11718 4921 87745 97 6966 1251 2215728 615:29 25d 15h 28m 2.34 3.3% 17.8 12.6 5.3 0.42 30 70.1 25 27364
jpearman James Pearman 3449 329 15290 8461 140636 249 13067 1252 3211352 892:03 6d 4h 2m 1.76 0.23% 16.6 10.8 4.1 0.03 43 112.3 23 26841
holbrook John Holbrook 13139 7417 6509 1598 180811 32 15035 1250 2445904 679:25 28d 7h 25m 3.99 4.1% 113.1 12 2.7 0.49 33 144.6 14 25992
3141B Ungenannt 27575 1450 1343 76 16565 3 1043 328 170084 47:15 1d 23h 14m 17 8.75% 218 15.9 2.7 1.39 9 50.5 10 22831
meng meng 4473 776 7516 2760 68708 73 5011 1252 1420508 394:35 16d 10h 35m 2.65 1.13% 24.9 13.7 4.7 0.15 19 54.9 21 19940
10 Likes

I halted playing R6 to read this

3 Likes

Well… I just increased my “likes given” by one!

Very cool work… The best way to learn something is to apply it to a cool project and you’ve definitely done that! Nice!

8 Likes