Forums
Clan List Logger
Quick find code: 86-87-226-65189098
Hey Blasty,
I was wondering if you could tell me how to update the script so the amount of rows stays fixed at 501 on the Current Member List. I am adding an additional column to notate when to rank up members based on their clan xp. However, if the clan size fluctuates down it deletes the remaining rows after the last clanmate. So when we gain more members thereafter, there are rows at the bottom with deleted formulas in Column E as a result (because of the clear function in your script).
I can probably get around it by just making a new sheet and pulling in the info from this sheet and then make my own formula, but keeping it in the same sheet (Current Member List) would be neat.
Here's how the script is now:
function clearData(){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=ss.getSheetByName("Current Clan List" );
var extras=sheet.getMaxRows()-2;
if(extras!=0){sheet.deleteRows(3,extras)};
sheet.getRange(2,1,1,sheet.getMaxColumns()).clearContent();
=======
I'm a total noob at script language so just wondering. I also think what I'm describing would be a great additional feature. If there was a way to input xp req for each rank in the settings, many clans can benefit from a super easy way to do rank promotions each day.
Appreciate any help and thoughts
I was wondering if you could tell me how to update the script so the amount of rows stays fixed at 501 on the Current Member List. I am adding an additional column to notate when to rank up members based on their clan xp. However, if the clan size fluctuates down it deletes the remaining rows after the last clanmate. So when we gain more members thereafter, there are rows at the bottom with deleted formulas in Column E as a result (because of the clear function in your script).
I can probably get around it by just making a new sheet and pulling in the info from this sheet and then make my own formula, but keeping it in the same sheet (Current Member List) would be neat.
Here's how the script is now:
function clearData(){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=ss.getSheetByName("Current Clan List" );
var extras=sheet.getMaxRows()-2;
if(extras!=0){sheet.deleteRows(3,extras)};
sheet.getRange(2,1,1,sheet.getMaxColumns()).clearContent();
Link:
https://docs.google.com/spreadsheets/d/1wh6WI7kP6tTP-wt2LjgL-BQ4kr3yTh3savFrcr9z91k/edit?usp=sharing
=======
I'm a total noob at script language so just wondering. I also think what I'm describing would be a great additional feature. If there was a way to input xp req for each rank in the settings, many clans can benefit from a super easy way to do rank promotions each day.
Appreciate any help and thoughts
23-Feb-2016 00:16:58 - Last edited on 23-Feb-2016 00:52:32 by E Bom
Hi everyone
Sorry I haven't been on in a while, uni and other things irl have been keeping me busy.
Corruptice said :
hi there blasty awesome spreadsheet is also possible to add in who kick members with a log ?
by who rank and timestamp ?
Thanks for showing interest, Rough Draft and Redtunnel answered this very nicely. Runescape's existing APIs don't give us access to that kind of information so it's not possible to automatically keep track of it unless the APIs are expanded, or something/someone constantly watches the game client for updates.
E Bom said :
Hey Blasty,
I was wondering if you could tell me how to update the script so the amount of rows stays fixed at 501 on the Current Member List. I am adding an additional column to notate when to rank up members based on their clan xp. However, if the clan size fluctuates down it deletes the remaining rows after the last clanmate. So when we gain more members thereafter, there are rows at the bottom with deleted formulas in Column E as a result (because of the clear function in your script).
Hey E Bom,
I looked into a few options and thought the one that might work best is to use ARRAYFORMULA to automatically fill the cells with formulas even if rows are deleted.
In E2, try entering the following formula:
=ARRAYFORMULA(if(HLOOKUP(B2:B,({{"Recruit","Corporal","Sergeant","Lieutenant","Captain","General","Admin","Organiser","Overseer","Deputy Owner","Owner"};{100000,500001,1000002,10000003,50000004,100000005,500000006,800000007,1000000008,2000000009,5200000010}}),2,FALSE)< C2:C,"Rank up!","" ))
Replace the numbers with the amount of xp thay need to rank up at a certain rank.
If it works, it should give you a column full with "Rank up!" when a player exceeds the xp required to rank up. Let me know if it doesn't do what you're after. Sorry it's taken so long to get back to you!
Sorry I haven't been on in a while, uni and other things irl have been keeping me busy.
Corruptice said :
hi there blasty awesome spreadsheet is also possible to add in who kick members with a log ?
by who rank and timestamp ?
Thanks for showing interest, Rough Draft and Redtunnel answered this very nicely. Runescape's existing APIs don't give us access to that kind of information so it's not possible to automatically keep track of it unless the APIs are expanded, or something/someone constantly watches the game client for updates.
E Bom said :
Hey Blasty,
I was wondering if you could tell me how to update the script so the amount of rows stays fixed at 501 on the Current Member List. I am adding an additional column to notate when to rank up members based on their clan xp. However, if the clan size fluctuates down it deletes the remaining rows after the last clanmate. So when we gain more members thereafter, there are rows at the bottom with deleted formulas in Column E as a result (because of the clear function in your script).
Hey E Bom,
I looked into a few options and thought the one that might work best is to use ARRAYFORMULA to automatically fill the cells with formulas even if rows are deleted.
In E2, try entering the following formula:
=ARRAYFORMULA(if(HLOOKUP(B2:B,({{"Recruit","Corporal","Sergeant","Lieutenant","Captain","General","Admin","Organiser","Overseer","Deputy Owner","Owner"};{100000,500001,1000002,10000003,50000004,100000005,500000006,800000007,1000000008,2000000009,5200000010}}),2,FALSE)< C2:C,"Rank up!","" ))
Replace the numbers with the amount of xp thay need to rank up at a certain rank.
If it works, it should give you a column full with "Rank up!" when a player exceeds the xp required to rank up. Let me know if it doesn't do what you're after. Sorry it's taken so long to get back to you!
Blasty
// @BlastytheBlue // Blasty#5167
| Co-owner of
Mine Nation
08-Mar-2016 13:40:24 - Last edited on 09-Mar-2016 02:22:17 by Blasty
Agita Sage
said
:
Blasty,
When you open the UI of your Logger, it takes some time to open, which I assume is the Logger gathering all the data relating to every member ever in the Logger.
This information is extremely helpful, showing when members Join, Rank and Leave; just unfortunate that you need to go through each name individually to see this form of data.
Would it be possible to download all this data instead of viewing each name individually?
A mass log of every name with their data for easier manipulation.
Cheers!
Hey Agita Sage,
I've just put in a quick update to make it possible to get a complete table of the data, and also a JSON representation - I wasn't sure which would be more useful so I made both. To use it, first upgrade the library to version 30.
These are the steps to upgrade since they might be hard to find:
1. Open the spreadsheet.
2. Click on Tools -> Script editor...
3. Click on Resources -> Libraries...
4. Change the version of ClanLogger to the latest version.
5. Refresh the spreadsheet.
Once updated, there should be a new option under "Admin Tools" called "More Options", which replaces the previous "Open UI" option. There are now three options at the top for different ways to view the name change data.
Sorry the interface isn't more attractive - I tried to change the theme to material design but it didn't fit in very well with the thick frame. I ended up going with a more minimalist appearance just to get something out quickly.
If you're looking for a particular representation to download it in, let me know and I'll see what I can do
Something to keep in mind, however, is that name changes aren't properly taken into account as it is, so the data obtained might not be that useful. In most cases, I think at most it currently just gives a timeline of rank changes for players who don't change their names.
Blasty,
When you open the UI of your Logger, it takes some time to open, which I assume is the Logger gathering all the data relating to every member ever in the Logger.
This information is extremely helpful, showing when members Join, Rank and Leave; just unfortunate that you need to go through each name individually to see this form of data.
Would it be possible to download all this data instead of viewing each name individually?
A mass log of every name with their data for easier manipulation.
Cheers!
Hey Agita Sage,
I've just put in a quick update to make it possible to get a complete table of the data, and also a JSON representation - I wasn't sure which would be more useful so I made both. To use it, first upgrade the library to version 30.
These are the steps to upgrade since they might be hard to find:
1. Open the spreadsheet.
2. Click on Tools -> Script editor...
3. Click on Resources -> Libraries...
4. Change the version of ClanLogger to the latest version.
5. Refresh the spreadsheet.
Once updated, there should be a new option under "Admin Tools" called "More Options", which replaces the previous "Open UI" option. There are now three options at the top for different ways to view the name change data.
Sorry the interface isn't more attractive - I tried to change the theme to material design but it didn't fit in very well with the thick frame. I ended up going with a more minimalist appearance just to get something out quickly.
If you're looking for a particular representation to download it in, let me know and I'll see what I can do
Something to keep in mind, however, is that name changes aren't properly taken into account as it is, so the data obtained might not be that useful. In most cases, I think at most it currently just gives a timeline of rank changes for players who don't change their names.
Blasty
// @BlastytheBlue // Blasty#5167
| Co-owner of
Mine Nation
08-Mar-2016 14:23:04 - Last edited on 08-Mar-2016 14:24:16 by Blasty
Agita Sage
said
:
Oooh I do love you Blasty <3 this is absolute magic, thank you very much for putting the time into doing that.
I'm always after stats about my community; join dates, leaving dates, rank up times, average time in Clans, etc, so this is really helpful, especially with so many members to sift through.
I'll be able to make educated guesses about Name Changes depending on what Name & Rank left and joined on a set day.
With the Full Name list, instead of it saying for example, "9/8/2014: Liouxcie (Ranked up)",
could it be set to singular name search format, "9/8/2014 - Liouxcie: Ranked up (General > Admin)" so we can see the actual Rank Up/ Down?
I've made a change so that it shows the rank too now. To see the changes, just update the library to version 31, then check out the Full List section in the More Options area.
For my clan, it's kinda interesting to see all the different names in a table and the rank changes that happen over time.
Anyway, I hope it shows you something you were after
Oooh I do love you Blasty <3 this is absolute magic, thank you very much for putting the time into doing that.
I'm always after stats about my community; join dates, leaving dates, rank up times, average time in Clans, etc, so this is really helpful, especially with so many members to sift through.
I'll be able to make educated guesses about Name Changes depending on what Name & Rank left and joined on a set day.
With the Full Name list, instead of it saying for example, "9/8/2014: Liouxcie (Ranked up)",
could it be set to singular name search format, "9/8/2014 - Liouxcie: Ranked up (General > Admin)" so we can see the actual Rank Up/ Down?
I've made a change so that it shows the rank too now. To see the changes, just update the library to version 31, then check out the Full List section in the More Options area.
For my clan, it's kinda interesting to see all the different names in a table and the rank changes that happen over time.
Anyway, I hope it shows you something you were after
Blasty
// @BlastytheBlue // Blasty#5167
| Co-owner of
Mine Nation
10-Mar-2016 07:54:45
Quick find code: 86-87-226-65189098 Back to Top