recolle AC Tracker version 2.1

[randomtology]

Hey guys! This is Yuff (Noah/Elliot player), with a spreadsheet and automation code update to help players keep track and post their AC in an easier manner. You can see my previous version here.

This particular code has been written with the new current AC rules in mind. It's easy to use even for people not familiar with spreadsheets or automation codes, and completely covers everything you need for an AC submission.

For this code you are required to have a google/gmail account. Google accounts are free to make.

NOTE! The following updates have been made to the code from the last version:

Auto-Comment Counter has been added for checking purposes!

Total Comment Counter has been added!

Regain Entries has been increased to 8.

A key for Completed Threads has been added.


Spreadsheet Preview:


Creating Your Tracker
1. Create a brand new spreadsheet. If you are already using a google spreadsheet for your AC tracker, you can skip this step. Otherwise go to Google Drive and click the New button, and select Google Sheets -> Blank Spreadsheet. You can name your spreadsheet by clicking on "Untitled" on the top-left and naming it whatever you desire.

2. Once you have a spreadsheet of your own, head over to this template. At the bottom-left hand corner, there is a tab that's labeled "Month 'XX (NEW)". Right-click on this tab and select "Copy to...". Select your own spreadsheet and the page will be sent directly to that page, formatting and all.

3. To set up the Auto Comment Counter, click on the H2 square (the one with a 0 in it). There should be a code that appears that says: =COUNTIF(importxml(D2,"//span[@class='ljuser']"),"USERNAME"). Replace USERNAME with your character's username.

Setting up Automation
1. If you've never done Automation before (or you need a refresher, follow this tutorial, but skip steps 5-8 (as well as step 1 since you already did this) as I'll be providing the automation code for you.

2. Once you reach Step 5 and need the code, C&P this code:
<? var data = getData(); ?><raw-code> <b>AC PROOF 1:</b> <a href="<?= data[2][12] ?>"><?= data[2][10] ?> <?= data[2][11] ?> w/ <?= data[2][13] ?></a> (<?= data[2][14] ?> comments)<br/> <b>AC PROOF 1b:</b> <a href="<?= data[4][12] ?>"><?= data[4][10] ?> <?= data[4][11] ?> w/ <?= data[4][13] ?></a> (<?= data[4][14] ?> comments)<br/> <b>AC PROOF 2:</b> <a href="<?= data[3][12] ?>"><?= data[3][10] ?> <?= data[3][11] ?> w/ <?= data[3][13] ?></a> (<?= data[3][14] ?> comments)<br/> <b>AC PROOF 2b:</b> <a href="<?= data[5][12] ?>"><?= data[5][10] ?> <?= data[5][11] ?> w/ <?= data[5][13] ?></a> (<?= data[5][14] ?> comments)<br/> <b>REGAIN PROOF:</b> <a href="<?= data[9][11] ?>"><?= data[9][10] ?> Regain (<?= data[9][12] ?>)</a><br/> <br/> <b>POINTS</b><br/> <a href="<?= data[2][12] ?>"><?= data[2][10] ?> <?= data[2][11] ?> w/ <?= data[2][13] ?></a> <?= data[2][14] ?> comments = <?= data[2][16] ?> points<br/> <a href="<?= data[3][12] ?>"><?= data[3][10] ?> <?= data[3][11] ?> w/ <?= data[3][13] ?></a> <?= data[3][14] ?> comments = <?= data[3][16] ?> points<br/> <a href="<?= data[4][12] ?>"><?= data[4][10] ?> <?= data[4][11] ?> w/ <?= data[4][13] ?></a> <?= data[4][14] ?> comments = <?= data[4][16] ?> points<br/> <a href="<?= data[5][12] ?>"><?= data[5][10] ?> <?= data[5][11] ?> w/ <?= data[5][13] ?></a> <?= data[5][14] ?> comments = <?= data[5][16] ?> points<br/> <? for (var i = 1; i < data.length; i++) { ?> <a href="<?= data[i][3] ?>"><?= data[i][1] ?> <?= data[i][2] ?> with <?= data[i][4] ?></a> <?= data[i][6] ?> comments = <?= data[i][8] ?> points<br/> <? } ?> <b>TOTAL POINTS EARNED:</b> <?= data[2][0] ?> Points<br/> <br/> <b>PURCHASED:</b><br/> <? for (var i = 12; i < 20; i++) { ?> <a href="<?= data[i][11] ?>">Transaction (<?= data[i][12] ?>)</a> = <?= data[i][10] ?> points<br/> <? } ?> <b>TOTAL POINTS SPENT:</b> <?= data[6][0] ?> Points Spent<br/> <br/> <b>POINTS TOTAL:</b> <?= data[8][0] ?> (<?= data[4][0] ?> rollover + <?= data[2][0] ?> earned - <?= data[6][0] ?> spent ) <raw-code>

3. Skip down to Step 9 of Slip's tutorial, and follow the rest. I highly recommend bookmarking the page that gives you a text-generated code, as that page will refresh itself automatically whenever you update your AC spreadsheet.

Note: When doing Step 12, where you review your permissions, if you get an error message that says "This app can't be verified", simply select "Advanced" and click to go to the AC tracker page. This seems to be an extra safeguard Google put up since DimensionSlip's tutorial was written.

And now you're all set up!


Using Your Tracker

The parts you can edit are any parts that have no/white background color. If there is a non-white background color, leave it alone as it likely contains a formula for point calculating. For any parts that require numbers, only enter numbers otherwise it'll cause an error with the formulas.

For any link sections, C&P the URL and nothing else. That way, they can be entered into the HTML formula without any problems.

COUNTING COMMENTS AND THE AUTO COUNTER: So on this version, the auto counter has been set up purely as a COUNT CHECKER, but anything that appears on the auto column will NOT count towards points or appear on the Automation form. This is because the code is designed to just count the number of times a username appears on a page, and that's it. That means if you have a thread in your own inbox, multi-comment responses, an old post from last month or someone just happens to list your username somewhere on that page, or any other instance that'd make your username appear -- it will make the comment count off.

So to have your comments counted towards points, check to make sure the Auto column matches the actual comment number, then enter the real number of comments in the "#" column.

USING THE TOTAL COMMENT COUNTER: It will automatically count anything in the "#" column! You don't need to do anything at all but watch the comments climb.

THE COMPLETE KEY: Basically anytime you know a thread is done and you want a mental note, just highlight that thread's WHITE CELLS (don't mess with the colored cells!), and change the background color to match that's under the paintbucket on the menu. (though you'll have to undo this on the next month but highlighting the area and clicking on "Reset" under the paintbucket icon.

If you're planning on collecting points on your AC Proofs, you don't need to re-link the in the thread tracker area - just having it AC section will make sure it's added to the point section of your AC comment. It should be noted that in order for points to appear, you need to select one of the "New CR" options (which doesn't mean it has to be New new CR for it to count, just CR you haven't used for AC lately- there's just no quick way to phrase that) and you must have at least 10 comments total in your proofs.

The template is set up to count up to 40 threads for bonus points. If you end up having more threads, simply highlight the cells in Row 40: columns B - H. In the right-hand bottom corner, a small blue box will appear:


(this guy)

Hover your cursor over that blue box, and your cursor will turn into a cross. When it does, click and drag down until you reach the number of rows you need. This will format all the rows you selected to count bonus points too.

The code is set up to calculate up to 8 regain purchases per month. If you end up purchasing more than 8, you'll first need to perform the same steps as adding extra point rows, but for the purchase area. Then you need to head into your Script Editor page, and modify Line 18 of code, the line that reads . Add whatever number of extra regains you need to the number 16 (so if you need 2 extra purchase slots, it'd be "i < 22;". Hit save.

When it comes time to print out your code, any formatted cells that aren't used will spit out a code that looks similar to this. . Simply delete those snippet of code. You'll find these codes in the AC proof section (note: there are 4 proof slots but you only need 2), the point section, and the purchased area (though the AC proof and Purchases one will look slightly different).

When next month rolls around, you'll want a new sheet for the month. Head down to the tab that's labeled "Month 'XX" -- which you can change the name of by either double clicking or right-clicking and selecting "Rename" -- and selecting "Duplicate".

Rename the new sheet to avoid confusion, and then click and drag that new tab to the left until it's the first sheet listed (This is because the automation code generates code only for the first sheet of a document). Clear out any old information in the no/white background areas, and enter the number of points you have now in the "Rollover Points" section.

Finally, if you wish to change the color scheme, font, or text-sizes - go right ahead! None of these will affect the code itself. The only one that might be harder to change is the colors of Total Comment # in the AC proof area, as this has been rigged up to change colors based on if you have the right number of comments for the month (red if you're under, green if you're in the clear). This feature can be remove or changed by right-clicking on that cell and selecting "Conditional Formatting". Then either delete or edit the two rules.

--------------

And that's it! A big thank you to DimensionSlip for designing the automation process, and a thank you to the person who designed the auto-comment counter (if you know who they are, please let me know!).

Hopefully this will help you in your endeavors to keep track of AC better! If you have any questions, feel free to ask in the comments below!

Comments

[odasaku]

hello! thank you so much for this wonderful spreadsheet and code. i have a couple questions, if you don't mind.

1. does the # column need the total amount of comments from both thread participants or just my own? aka. if i have 20 comments in a thread, do i put the 10 that i wrote or the 20 from both mine and my partner's?

2. the automation code is giving me a bit of a problem ; v ; i'm not really sure what is wrong...so i'll just give you the screenshots, okay?
here here and here

thank you for your time and for this amazing piece of work!

[randomtology]

Thank you! As for the questions.

1) It should be just your own comments! So in the example you gave it'd just be your 10 comments.

2) And hmmm...I'm not sure if this would fix it, but I'd double check two things on the Code.gs page.

First: Does the name you gave on at .createTemplateFromFile('NAME') match the page name you gave the html page?

Second: At the part that says .openById('URL-HERE'), did you C&P the entire section of the code. To give a visual example, the highlighted part is what I used for Elliot's code:


Please check both of these and lemme know if that fixed it or not!

[odasaku]

Yeah, they do! I checked them both and they're there as they should be. I even tried 'chart.html' just in case, but obviously to no avail. here and here

I also realized that the spreadsheet was on the second tab, so I moved it to the first. However this only changed the error number? So now it says 'TypeError: Cannot read property "11" from undefined. (line 7, file "Code")' instead of 'TypeError: Cannot read property "12" from undefined. (line 7, file "Code")'

[randomtology]

Hmmm....

Would you mind sharing the document with me? Hit the share button in the top right hand corner, and share it with the email "Yuffie99k@gmail.com" - just make sure I can edit it too. Since I wanna fiddle to see if I can get it working.

[odasaku]

i've sent you the file! thank you so much for your patience!!

[randomtology]

Okay had to call in a friend for help, but I got it fixed! Basically it was a weird random error that popped up due to the number of threads vs. the number of threads the code was looking for. But I formatted more cells to counterbalance that, so you should be a-okay!

Sorry about all that!

[odasaku]

oh my gosh thank you so much for the help ; o ;!!!!!

[ladytakamaki]

yuff, thank you so much for the code!

i'm having a problem with the "auto" column. when i first created the spreadsheet, the thread tracker would autocount the number of comments for me. however, after i refreshed the page, all of my comments in the "auto" column show up as '0' and aren't reverting back. ;~;

any way to fix this?

[randomtology]

Hmmm.

Okay, without seeing the sheet myself, let's check some basic stuff.

1) Click on the first auto cell. Is your username correct?
2) When you double click on the auto cell so it goes into edit mode, does it also highlight the cell that contains the thread like for that row? It should look something like this:


3) Finally if those things are correct. Try closing the tab completely and then relaunching the spreadsheet. Sometimes pages just fail to load and that's the way to fix it.

Let me know if any of this helps!

[ladytakamaki]

yes and yes to 1 and 2!

as for #3: after opening it again, it seems that the formula's working again! though there are a few that are still at '0'. it's probably just a loading error, then!

thank you so much for this and your hard work on the spreadsheet! YOU'RE A LIFESAVER

[randomtology]

Aw yais I'm glad!!

Yeah sometimes the code seems prone to loading errors, or it just won't like a single thread. I have no idea why- but it's yet another reason why its not being used as the official count and just as a check. \o/

Enjoy!!

[usurpers]

yuff this is amazing and everything is working %100 on my end, thank you for all the hard work ;o; i only have one thing that i am unsure how to do but that is just me being a complete dingus at coding, so

how do i generate my entire ac/bonus point comment in html format from this?? i have tried looking around and i might have missed it somehow

[randomtology]

Thank you!! As for your question, I think we covered it in discord! Hopefully it keeps working but lemme know if something comes up.