AC Spreadsheet Tracker & Automation: Version 2.0!

[redarm]

Hey guys! This is Yuff (Noah/Elliot 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 new 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:

Point system updated (Network threads count for 1 while Action count for 2)

AC Section has been reformatted.

Equation added so code will automatically withhold points from all AC proofs until there are ten comments total.

Code will now highlight numbers in red if thread is too small for AC proof.

Automation template has been updated.


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.


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

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.

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 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 (note: there are 4 proof slots but you only need 2. Proof 3), 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

[appraiser]

You're awesome, Yuff, thank you!

[rygdea]

This going to be so helpful! Starting to use it now

[totallynotanoni]

Is it supposed to change the sheet or something to look like the image above when I do the whole "Copy to..." thing? Because nothings happening when I do that.

Or do I have to set up everything on my own?

[fiddlestick]

What it should do is add a new sheet/tab into the sheets document you selected! Look at the bottom of your screen, and there should be a tab with title "Copy of Month 'XX". Clicking on that will let you access your own copy of this new template!

[totallynotanoni]

Oh, that makes sense. Just found it. Thank you.

[fiddlestick]

You're welcome! Enjoy!

[totallynotanoni]

One other thing, sorry. When I finally hit the error message, what do you mean by "Click to got to the AC Tracker page"? Is there a link through the advanced options or am I missing something here.

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.

[fiddlestick]

Yeah, there should be a link that says "Go to [whatever you named your sheet] (unsafe)" once you enter the Advanced options area (Which is here). You might need to scroll down a little to see it - but it's there!

[totallynotanoni]

Yeah just found it.

The thought sit occurred to me that I don't know if this is meant to just keep track of my AC proofs or is essentially a thread tracker.

[fiddlestick]

It does both! You're supposed to turn in all your threads for bonus points, so it keeps track of your AC and all the extra threads. The automation part you just asked about makes it so every thing you enter into the charts gets assembled into a nice HTML format for easy AC turn in.

[totallynotanoni]

Good to know. I think thats all the questions I have. Thanks for answering.

BACKTAG VERSION

[livingdeadgirl]

Heyo - I went ahead and modified the template to include a space for threads from the previous month.

Template spreadsheet is available here.

Accompanying code for activity.html in the script editor:
<? 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/> <b>AC PROOF 1b:</b> <a href="<?= data[4][11] ?>"><?= data[4][9] ?> <?= data[4][10] ?> w/ <?= data[4][12] ?></a> (<?= data[4][13] ?> comments)<br/> <b>AC PROOF 2:</b> <a href="<?= data[3][11] ?>"><?= data[3][9] ?> <?= data[3][10] ?> w/ <?= data[3][12] ?></a> (<?= data[3][13] ?> comments)<br/> <b>AC PROOF 2b:</b> <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/> <ul><li><a href="<?= data[2][11] ?>"><?= data[2][9] ?> <?= data[2][10] ?> w/ <?= data[2][12] ?></a> <?= data[2][13] ?> comments = <?= data[2][14] ?> points</li> <li><a href="<?= data[3][11] ?>"><?= data[3][9] ?> <?= data[3][10] ?> w/ <?= data[3][12] ?></a> <?= data[3][13] ?> comments = <?= data[3][14] ?> points</li> <li><a href="<?= data[4][11] ?>"><?= data[4][9] ?> <?= data[4][10] ?> w/ <?= data[4][12] ?></a> <?= data[4][13] ?> comments = <?= data[4][14] ?> points</li> <li><a href="<?= data[5][11] ?>"><?= data[5][9] ?> <?= data[5][10] ?> w/ <?= data[5][12] ?></a> <?= data[5][13] ?> comments = <?= data[5][14] ?> points</li> <? for (var i = 1; i < data.length; i++) { ?> <li><a href="<?= data[i][3] ?>"><?= data[i][1] ?> <?= data[i][2] ?> with <?= data[i][4] ?></a> <?= data[i][6] ?> comments = <?= data[i][7] ?> points</li> <? } ?></ul> <b>TOTAL POINTS EARNED:</b> <?= data[2][0] ?> Points<br/> <br/> <b>POINTS FROM [LAST MONTH]</b><br/> <ul> <? for (var i = 19; i < data.length; i++) { ?> <li><a href="<?= data[i][11] ?>"><?= data[i][9] ?> <?= data[i][10] ?> w/ <?= data[i][12] ?></a> <? data[i][13] ?> comments = <? data[i][14] ?> points</li> <? } ?></ul> <b>TOTAL POINTS EARNED:</b> <?= data[12][0] ?> Points<br/> <br/> <b>PURCHASED:</b><br/><ul> <? for (var i = 12; i < 16; i++) { ?> <li><a href="<?= data[i][10] ?>">Transaction (<?= data[i][11] ?>)</a> = <?= data[i][9] ?> points</li> <? } ?></ul> <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[12][0] ?> [LASTMONTH] - <?= data[6][0] ?> spent) <raw-code>

[goen]

YUFF I'M HAVING DIFFICULTIES...

When I try to deploy the web app and "test web app for your latest code", the window that pops up is just an error saying Sorry, unable to open the file at this time.

I tried googling it and the common fix seems to be staying logged into only one google account, but that doesn't apply to me since I only have one google account to begin with...

Do you have any idea what I'm doing wrong? If it helps, I'm working off the Backtag Version you posted just above. I also took screenshots of my spreadsheet, code.gs, and activity.html.

[fiddlestick]

OKAY SO THIS SEEMS WEIRD BUT....I would try completely closing out of the spreadsheet tab (make sure they're bookmarked so you can get to them quickly), and the script editor too, give it about 10-30 seconds, then open them up again and try to deploy the web app again.

Sometimes this happens when you first try to deploy it, and for some random reason? This seems to work. So give it a go and tell me what happens.

[goen]

... IT WORKED!!!! YER A WIZARD YUFF

That's so weird... Thanks for the swift reply and the coding and hard work!!

[redarm]

I AM A WIZARD!!!

Yeah i have no idea why it works, but it does. And it's no problem at all!! Lemme know if anything else comes up!

[notyourutopian]

Hey so I've been trying to figure this out and what I ended up with when I deployed the webb app was this error:

TypeError: Cannot read property "11" from undefined. (line 7, file "Code")

Any idea what I might have broken? Haha.

[redarm]

Hiya! That is usually an error that appears when it's looking for data in a certain row, but there's none to be found.

To fix it, I recommend to follow this step:

Highlight the cells in last row you have formatted: 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 about row 16 or 17. This will format all the rows you selected to count bonus points too.

Lemme know if that fixes it!

[notyourutopian]

Hmm, it didn't, same error, same line.

I haven't actually put any threads in the tracker yet. Does that make a difference? Can it not process a sheet that only has the template so far?

[redarm]

It shouldn't make a difference, but out of curiosity, when you click the cell H2 (the first point cell) - does anything appear in the text box above or no?

[notyourutopian]

Yeah

=COUNTIF(importxml(D2,"//span[@class='ljuser']"),"USERNAME")

I tried changing username to eleanor's username and then drag-copying that down the sheet, but I'm still getting the same error.

[redarm]

Okay it should be her username, so that's good!

But hmmm, would you mind sending me the link to your spreadsheet via PM? Make sure I have editting permission (by clicking and editting settings on the Share button on the top-right corner). There's something I wanna try to see if I can fix it.