AC Spreadsheet Tracker & Automation: New AC Rules Edition!

[redarm]

Hey guys! This is Yuff (Allen/Noah player), with another spreadsheet and automation code to help players keep track and post their AC in an easier manner.

This particular code has been written with the new AC rules in mind, and has been tested for the past month (shout-out to Saffy for helping me with the testing!). 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.


Spreadsheet Preview:

(Note: Template doesn't have the Completed Thread Key, but it's easy to add in yourself you want since it's just changing background colors of cells. Also the regain section looks slightly different as I tweaked that at the last minute.)

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". 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.


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][11] ?>"><?= data[2][9] ?> <?= data[2][10] ?> w/ <?= data[2][12] ?></a> (<?= data[2][13] ?> comments)<br/> <a href="<?= data[3][11] ?>"><?= data[3][9] ?> <?= data[3][10] ?> w/ <?= data[3][12] ?></a> (<?= data[3][13] ?> comments)<br/> <b>AC PROOF 2:</b> <a href="<?= data[4][11] ?>"><?= data[4][9] ?> <?= data[4][10] ?> w/ <?= data[4][12] ?></a> (<?= data[4][13] ?> comments)<br/> <a href="<?= data[5][11] ?>"><?= data[5][9] ?> <?= data[5][10] ?> w/ <?= data[5][12] ?></a> (<?= data[5][13] ?> comments)<br/> <b>REGAIN PROOF:</b> <a href="<?= data[9][10] ?>"><?= data[9][9] ?> Regain (<?= data[9][11] ?>)</a><br/> <br/> <b>POINTS</b><br/> <a href="<?= data[4][11] ?>"><?= data[4][9] ?> <?= data[4][10] ?> w/ <?= data[4][12] ?></a> <?= data[4][13] ?> comments = <?= data[4][14] ?> points<br/> <a href="<?= data[5][11] ?>"><?= data[5][9] ?> <?= data[5][10] ?> w/ <?= data[5][12] ?></a> <?= data[5][13] ?> comments = <?= data[5][14] ?> 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][7] ?> points<br/> <? } ?> <b>TOTAL POINTS EARNED:</b> <?= data[2][0] ?> Points<br/> <br/> <b>PURCHASED:</b><br/> <? for (var i = 12; i < 16; i++) { ?> <a href="<?= data[i][10] ?>">Transaction (<?= data[i][11] ?>)</a> = <?= data[i][9] ?> 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.

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.

If you're planning on collecting points on your 2nd AC Proof, you don't need to re-link it 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).

If you're planning on collecting points on your 1st AC proof, you will need to put that into the thread tracker area, starting from the comment that's eligible for points.

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 4 regain purchases per month. If you end up purchasing more than 4, 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 < 18;". 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, 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! 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

[thesettingsun]

Thank you for your hard work! I have a question.

So far I have one thread for August, an action thread with 10 comments. I've put in 10 comments in column G, and in column H my number of points is 10, as it should be. However, in column A, under 'earned points', I have 15 points. Why is that?

Edit: disregard that, it was counting your example thread in the AC section, which had 5 points in it. Woops.

[redarm]

Whoops, sorry about that! I wanted to have some examples in there for clarity, that's all. Glad you were able to figure it out! Enjoy!

[megabond]

Regarding the rollover points, should I edit that since this is the first month I ever use this spreadsheet and I got many points from last month?

[redarm]

Yes, you should actually edit the rollover point section every month including the first one!

[megabond]

Good to know. Thanks!

[toobigtohandle]

Hi,

I have followed the steps in the tutorial to deploy the script and it tells me I have an incorrect value on line 17 of my Code file. After checking, it is this one :

return SpreadsheetApp

Which I haven't touched or modified in any way. Any reason why it would do that?

[redarm]

Hmmm, I'm not sure...would you mind showing me what's on your code file via a pastebin?

[toobigtohandle]

Here it is :

https://pastebin.com/8Gq6PWTj

That's just the original code copied and pasted from the guide, the only thing I changed was editing the spreadsheet ID as indicated.

[redarm]

You want to delete those brackets I highlighted in red. They're what's causing the error.

[toobigtohandle]

Thanks, it's working just fine now!

[redarm]

No problem! Enjoy the code!