AC Tracker (version 3.0 - END GAME EDITION)

[randomtology]

Hey guys! This is Yuff (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 end-game 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:

AC Numbers have been adjusted!

AC comment minimum has been removed

A checkbox to mark when a thread is done has been added, which will automatically highlight said completed thread.


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 I2 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. But sure do to do this with the AC comment counters as well!

Setting up Automation
FOR PEOPLE WHO ALREADY HAVE A TRACKER: You don't need to re-set up the tracker. Just head into the Script Editor page and C&P the code given below into the "ACTRACKER.html" section, hit save and deploy it as a web app as you did before.

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][13] ?>"><?= data[2][11] ?> <?= data[2][12] ?> w/ <?= data[2][14] ?></a> (<?= data[2][15] ?> comments)<br/> <b>AC PROOF 1b:</b> <a href="<?= data[4][13] ?>"><?= data[4][11] ?> <?= data[4][12] ?> w/ <?= data[4][14] ?></a> (<?= data[4][15] ?> comments)<br/> <b>AC PROOF 2:</b> <a href="<?= data[3][13] ?>"><?= data[3][11] ?> <?= data[3][12] ?> w/ <?= data[3][14] ?></a> (<?= data[3][15] ?> comments)<br/> <b>AC PROOF 2b:</b> <a href="<?= data[5][13] ?>"><?= data[5][11] ?> <?= data[5][12] ?> w/ <?= data[5][14] ?></a> (<?= data[5][15] ?> comments)<br/> <b>REGAIN PROOF:</b> <a href="<?= data[9][12] ?>"><?= data[9][11] ?> Regain (<?= data[9][13] ?>)</a><br/> <br/> <b>POINTS</b><br/> <a href="<?= data[2][13] ?>"><?= data[2][11] ?> <?= data[2][12] ?> w/ <?= data[2][14] ?></a> <?= data[2][15] ?> comments = <?= data[2][17] ?> points<br/> <a href="<?= data[3][13] ?>"><?= data[3][11] ?> <?= data[3][12] ?> w/ <?= data[3][14] ?></a> <?= data[3][15] ?> comments = <?= data[3][17] ?> points<br/> <a href="<?= data[4][13] ?>"><?= data[4][11] ?> <?= data[4][12] ?> w/ <?= data[4][14] ?></a> <?= data[4][15] ?> comments = <?= data[4][17] ?> points<br/> <a href="<?= data[5][13] ?>"><?= data[5][11] ?> <?= data[5][12] ?> w/ <?= data[5][14] ?></a> <?= data[5][15] ?> comments = <?= data[5][17] ?> 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][9] ?> 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][12] ?>">Transaction (<?= data[i][13] ?>)</a> = <?= data[i][11] ?> 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.

Extra Note: If the code isn't generating correctly, attempt to completely close the tables of your AC tracker (both the tracker sheet and the automation), give it a minute or two, then reopen them. For some reason, this seems to fix the issue about 90% of the time. Why? We just don't know.

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 hit the checkbox in the H column! It will automatically highlight the thread so you know it's done!

For collecting points from your AC, 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.

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.

DO NOT remove the formatting for under 40 threads, as this will cause a glitch to the database. If you don't have forty threads, that's okay. Just leave them empty, it's ok.

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

[floret]

Thank you!

[quadrille]

as someone who was not even in recolle: this is apropos of nothing, but i want to say THANK YOU SO MUCH for that importxml() trick, because it just gave me so many ideas re: automatic comment counting and ways for a thread tracker to automatically tell if it's your turn/tag, and my own personal tracker is now so sleek and automated and wonderful. thank you again!! MAGIC.