Automatic AC Comment

[frisyr]

No longer supporting this version, please visit here

Hi guys! So after setting up your AC tracker, you might be thinking "whew, that's a lot of looking at code and stuff, I'm really glad that's over now." But for the like one or two of you who are thinking "wow, this is really interesting and I want to do an even more arduous setup to do even more really cool stuff!" then boy, you have come to the right place. I will preface this by saying that it is a pretty long read, but it's mostly because I am going to assume you have 0 programming knowledge. You do have to have the previous post set up (not the stuff in the comments, but I recommend having that set up as well!)

What you'll end up with is a code that spits out everything you need to post your AC comment, except the header.

Everything in this comment came from a spreadsheet:

*My AC thread for this image is accidentally marked "TDM (action)" in the spreadsheet which is why the count is off by 10. The calculation works if you correctly mark your threads unlike me (:

NOTE: There aren't more images of code examples until you get to the notes. All my images will be there of my working code, but please note the refs may not work for you if our spreadsheets are set up differently, so don't copy it verbatim.

Step One: Editing Your Spreadsheet

Okay, so the first thing we need to do is add more data to your spreadsheet for the code to process.

The first thing you'll need to do is add a column in your activity tracker spreadsheet for tracking whether or not you plan on submitting the thread for AC or not. If you are, then put AC in the cell next to the thread. Capital A, Capital C. No spaces, and nothing else. If you are not, put in literally anything else, including a blank. Personally I'm just using my old "notes" column, but if you're already using that for something else then go ahead and make a new column!

The second thing you'll need to do is make a new tab named CurrentBank (and remember from the initial setup post that your current tracker has to be on the far left, so make sure not to make the bank spreadsheet the first one.) It is kind of important that it be called CurrentBank with C and B being capitalized, but if you really hate that name, I've included instructions where to rename it when we get to that part in the code section.

So now on your CurrentBank sheet, you need to have at least the following 3 headers

1. Transaction Link
   
2. Transaction Description
   
3. Transaction Price

The reason I say at least is because you can optionally also add the mini-table from Yuff's Tally/Rollover/Spent points post. I personally have it set up with that on my CurrentBank tab because I like to have my "monthly statement" and my "transactions" in the bank, separate from my activity tracker.

If you don't have this mini table set up, you will also need to add the following 4 headers because your code will need to find the information somewhere:

1. Earned Points
   
2. Rollover Points
   
3. Spent Points
   
4. Total Points Left

So now my CurrentBank header row looks like this:
Earned Points | Rollover Points | Spent Points | Total Points Left | Transaction Link | Transaction Description | Transaction Price

The set of four headers (Earned/Rollover/Spent/Total) will each need 1 cell to house your monthly points statement. The Transaction headers (Link/Desc/Price) will have lists of each of your purchases. Your Earned Points cell will need a reference to your AC tab if Earned Points is on your bank tab, and this is what to put in your earned points cell:

=SUM(ACTabName!PointColumn:PointColumn)

So mine looks like:

=SUM(CurrentActivity!G:G)

And it doesn't really matter where you place these (it also doesn't matter if they're horizontal or vertical), but the Transaction items are going to be a list, so I prefer to have them be separate columns with nothing underneath.

Okay, that's all the new data you need in the spreadsheet!

Step Two: Editing Code.gs

At the very end, add this snippet:

function getBank(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheetByName("CurrentBank");
return sheets.getDataRange().getValues();
}

If you move your cursor to the top brace ({), it will light up in green which one your closing brace is. Everything between the braces is called a block. Take care to ensure that this function is not inside another block.

At this time, if you had change the tab name from CurrentBank while setting up your spreadsheet, you will need to update it above where it is bolded. It has to 100% match whatever you write in your spreadsheet, including case and spaces. I do not recommend having a space (ie "Current Bank"), I don't think that will break anything but it's a little unconventional.

Step Three: Editing activity.html

Oh boy, you've made it to step three: The trickiest part. But don't worry too much, I'll try to make this as painless as possible. Just keep in mind the two things that make this tricky are:

1. Inserting code around what code already exists, so it's very important that things be in the right place.
   
2. Replacing your cells (EG A1, B3) with their corresponding array assignments (EG [0][0], [2][1]). I've included a guide on how to figure out your cells in the notes below, so if you are totally lost on your cells and columns have no fear, I'll explain!

Okay, so here's basically what you need:

1. On the line that says:
   <? var data = getData();?>
   you need to add the bolded part so that it reads:
   <? var data = getData(); var bank = getBank();?>
   
   
2. Above the code you already have from the original autopoints code, copy this entirely:
   <b>AC PROOF:</b>
   <? for (var i = 1; i < data.length; i++){ if(data[i][COLUMN OF AC INDICATOR].equals("AC")){?>
   <a href="<?= data[i][COLUMN OF THREAD URL] ?>"><?= data[i][COLUMN OF DATE] ?> <?= data[i][COLUMN OF THREAD DESCRIPTION] ?></a>, with <?= data[i][COLUMN OF PARTICIPANTS] ?> (<?= data[i][COLUMN OF COMMENT COUNT] ?>)
   <? }} ?>
   
   <b>POINTS:</b>
   And remember that the columns start at 0, so A=0, B=1, etc. So your columns should be 0, 1, 2, etc. The AC indicator column is that new one that needed to be added to the spreadsheet, which lets you know if you plan on submitting the thread for AC or not.
   
   
   
   
3. The remainder all gets stuck at the very end, underneath all your existing code. It's a lot of replacing, so please bear with me! As a reminder, rows also start at 0, so if your item is in "row 1" in this code it will need to be replaced with "0", "row 2" with "1", etc. I have included two versions of this code, you only need one.
   
   If your Earned/Rollover/Spent tracker is on your CurrentBank tab:
   
   <b>TOTAL POINTS EARNED:</b>
   <?=bank[ROW OF YOUR POINTS EARNED][COLUMN OF YOUR POINTS EARNED]?>
   
   
   <b>PURCHASED:</b>
   <? for (var i = 1; i < bank.length; i++) {?>
   <a href="<?= bank[i][COLUMN OF YOUR TRANSACTION LINKS] ?>">Transaction <?=i?></a> (<?= bank[i][COLUMN OF YOUR TRANSACTION DESCRIPTIONS] ?>) = <?= bank[i][COLUMN OF YOUR TRANSACTION PRICE] ?> Spent
   <? } ?>
   
   <b>TOTAL POINTS SPENT:</b>
   <?=bank[ROW OF YOUR TOTAL POINTS SPENT][COLUMN OF YOUR TOTAL POINTS SPENT]?>
   
   
   <b>POINTS TOTAL:</b>
   <font color="blue">+</font> Earned <?=bank[ROW OF YOUR EARNED POINTS][COLUMN OF YOUR EARNED POINTS]?>
   
   <font color="blue">+</font> Rollover <?=bank[ROW OF YOUR ROLLOVER POINTS][COLUMN OF YOUR ROLLOVER POINTS]?>
   
   <font color="red">-</font> Spent <?=bank[ROW OF YOUR SPENT POINTS][COLUMN OF YOUR SPENT POINTS]?>
   
   = <b><?=bank[ROW OF YOUR TOTAL POINTS][COLUMN OF YOUR TOTAL POINTS]?></b>
   
   
   If your Earned/Rollover/Spent tracker is on your Activity Tracker/Sheet1 tab:
   
   <b>TOTAL POINTS EARNED:</b>
   <?=data[ROW OF YOUR POINTS EARNED][COLUMN OF YOUR POINTS EARNED]?>
   
   
   <b>PURCHASED:</b>
   <? for (var i = 1; i < bank.length; i++) {?>
   <a href="<?= bank[i][COLUMN OF YOUR TRANSACTION LINKS] ?>">Transaction <?=i?></a> (<?= bank[i][COLUMN OF YOUR TRANSACTION DESCRIPTIONS] ?>) = <?= bank[i][COLUMN OF YOUR TRANSACTION PRICE] ?> Spent
   <? } ?>
   
   <b>TOTAL POINTS SPENT:</b>
   <?=data[ROW OF YOUR TOTAL POINTS SPENT][COLUMN OF YOUR TOTAL POINTS SPENT]?>
   
   
   <b>POINTS TOTAL:</b>
   <font color="blue">+</font> Earned <?=data[ROW OF YOUR EARNED POINTS][COLUMN OF YOUR EARNED POINTS]?>
   
   <font color="blue">+</font> Rollover <?=data[ROW OF YOUR ROLLOVER POINTS][COLUMN OF YOUR ROLLOVER POINTS]?>
   
   <font color="red">-</font> Spent <?=data[ROW OF YOUR SPENT POINTS][COLUMN OF YOUR SPENT POINTS]?>
   
   = <b><?=data[ROW OF YOUR TOTAL POINTS][COLUMN OF YOUR TOTAL POINTS]?></b>
   
   If you are paying very close attention, the only difference between those previous two snippets of code is that the ones with the points tracker on a bank tab is pointing to bank[x][y], where the points tracker on the activity tab is pointing to data[x][y]. Make sure you have the right one selected, or your code will be looking on the wrong spreadsheet for your information.
   
   

   Notes

   
   

   Images for Reference

   
   I have highlighted all changes in yellow! All these will open in new tabs so you don't lose this tab. This is just what I have set up, so these numbers won't necessarily work for you.
   Spreadsheet: Activity Tracker
   Spreadsheet: CurrentBank
   Code.gs
   activity.html
   
   

   Notes on replacing row/columns

   
   How this works is that the spreadsheets app functions like Excel, it gives you a letter for the column and a number for the row, so you have references to column/row sort of like chessboard notation. E5, D6, etc. However, the GoogleApps scripting language represents these items with array notation. Starting from index 0 (computer scientists always start counting at 0 :)), you get the notation:
   nameOfYourArray[row][column]
   So let's pretend we have a spreadsheet called Sheet1. On Spreadsheets, you would say Sheet1!A1. But in GoogleApps code, you would refer to the same cell with Sheet1[0][0].
   
   Notice that in spreadsheets, you mention the column first, and in GoogleApps, you mention the row first. So in order to find your number in array notation, first take your chess notation number, and subtract 1. This will be your first number in array notation. Then take your letter, find its corresponding position in the alphabet, (C is the third letter), and again, subtract 1. This is now your second number in array notation.
   
   So let's take cell Z45.
   
   Your array coordinates will look like [x][y].
   
   x=45-1=44.
   y=26 (as Z is letter 26)-1=25.
   
   So Z45 will look like [44][25]
   
   

   Troubleshooting

   
   You've tried your best, but something broke! Here are a few things that might go wrong and how to fix them:
   
   1. If your app hasn't updated at all, but is still doing what it used to do, you just need to hit deploy as app, and on the dropdown where it says "version", you need to select "new".
      
   2. If your printout is giving you weird values, then one of your references is wrong. For instance if your transaction description is giving you a number, you probably gave it the column for transaction price by accident. If you need help diagnosing which one is giving you issues, just let me know and I can help you look!
      
   3. If your printout is giving you blank values where you KNOW you have values, and your reference is correct, you may have deleted an equals sign. Some of the code is encased in what looks like <? ?>, and some of it is encased in <?= ?>. If your eyes are glazing over trying to figure out where, let me know and I'll come look it over for you.
      
   4. If you run your code and you're getting "Error: name_of_your_app is messed up on line 34" or something, you have a typo... This is the worst, because it could be anything. A missing brace, a deleted carrot, a wayward parentheses... Also let me know if this happens and I'll also take a look.
   -->