(ง'̀-'́)ง (![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png)
antagonize) wrote in ![[community profile]](https://www.dreamwidth.org/img/silk/identity/community.png)
recallings2017-03-29 01:30 am
antagonize) wrote in recallings2017-03-29 01:30 am
how to make google organize your activity check comment for you
Hello, friends! I saw some of you on plurk wishing to automate the points part of AC using Google spreadsheets, so I hunkered down and scripted a... thing. The big disclaimer here is that I have never used the scripts function before now and referenced DimensionSlip's CR Chart Automation script the whole time — so please go check that out when you have the chance! It like, literally gave birth to this thing.
That said, look beneath the cut for details on how to make Google do your activity check comment for you.
STEP ONE:
First thing is first: you need to have all of your activity on a Google spreadsheet. If you haven't done this already, then go take a look at the handy dandy tutorial Jem (our Elizabeth player) created. Beside automating your activity check comment, it's very useful for actually tallying your points in the first place.
For reference, I'll be using Jason's thread tracker:
As you can see, I've included a column with the full url. This is important for what we're going to do later, so even if you make it as tiny as possible like I did, be sure to include that!
STEP TWO:
Once you have your beautiful spreadsheet up and running, go to Tools and click on Script Editor.
It should take you to the following page:
That sure is a whole lot of nothing, huh? We'll fix that in a bit.
STEP THREE:
First off, grab the code from here and copy/paste it into the script editor. After that's done, take this bit I've highlighted over here...
... And replace it with your thread tracker's ID, which is the string of letters and numbers between d/ and /edit on your Google spreadsheet's URL.
(Once again, from Jason's thread tracker.)
STEP FOUR:
Go to Files > New, and then create an HTML file.
In order for this to work, you need to title your html file activity in lowercase. Having done that, go grab the code from here and paste it into your html file. It should look like this:
There are a lot of things to replace here, but don't despair! I'll walk you through it. But unless you know how to work with scripts and the like, I strongly recommend you don't tamper with anything besides the [BLAH BLAH COLUMN] bits.
Before we start replacing the things between the brackets, first you need to know how columns are numbered in Google spreadsheets. Using Jason's Thread Tracker as an example once again, column A = 0, column B = 1, column C = 2, and so forth.
That means that for the first bracket, the one that says URL COLUMN, I need to put the number associated with the column where I have all my thread URLs. In Jason's Thread Tracker, I have all my links in column B. Since we've just discussed that A = 0, B = 1, C = 2, and so forth — then I should put 1 inside that bracket.
Beautiful.
Using that same logic, just replace the rest of the column placeholders with the appropriate numbers. In Jason's Thread Tracker, the date column would be 0, thread type (which I actually have under "what", not type — oops) would be 3, character name would be 4... and the rest is easy to follow. The important part is that you have all the information in your spreadsheet to comply with what's required for the activity check comment.
My code ended up looking like this after I was done replacing everything:
STEP FIVE:
Having done all of the above, go to File and select Save All to make sure none of your hard work is lost.
Then, go to Publish and select Deploy as web app.
This should show up for you:
Click on Deploy
Review permissions... And a new window will pop up. You need to click on Allow for your new script to do its thing.
Finally, click on the bit I've highlighted to see your script in action.
STEP SIX:
Here it is, in all its messy glory.
Don't worry too much about the mess above and just grab the code from the text box. It'll look fine once you plug it into your activity check comment. If you have doubts, here's Jason's point comment on the activity check post after I used the script for it.
For thread trackers with multiple sheets, you need to make sure the month you're automating is on the leftmost corner. That is to say, if I wanted to automate Frisk's March activity right now, I just need to make sure their spreadsheet is arranged like this:
So, latest month first since that's what we really want for our AC comments.
And if you need to automate your activity check comment again, then just go to Deploy as web app like last time. The box that will pop up this time will be a little different, but all you need to do is click on latest code. It should show up with the same information as your spreadsheet.
EDIT: If you're having trouble with your date formatting as (for example) "Tue Mar 28 2017 00:00:00 GMT-0700 (PDT)", you need to disable auto-formatting. As Boba (Yato's player) explains it:
And that's all, folks! If you have any questions, let me know and I will do my best to explain.
That said, look beneath the cut for details on how to make Google do your activity check comment for you.
STEP ONE:
For reference, I'll be using Jason's thread tracker:
As you can see, I've included a column with the full url. This is important for what we're going to do later, so even if you make it as tiny as possible like I did, be sure to include that!
STEP TWO:
It should take you to the following page:
That sure is a whole lot of nothing, huh? We'll fix that in a bit.
STEP THREE:
... And replace it with your thread tracker's ID, which is the string of letters and numbers between d/ and /edit on your Google spreadsheet's URL.
(Once again, from Jason's thread tracker.)
STEP FOUR:
In order for this to work, you need to title your html file activity in lowercase. Having done that, go grab the code from here and paste it into your html file. It should look like this:
There are a lot of things to replace here, but don't despair! I'll walk you through it. But unless you know how to work with scripts and the like, I strongly recommend you don't tamper with anything besides the [BLAH BLAH COLUMN] bits.
Before we start replacing the things between the brackets, first you need to know how columns are numbered in Google spreadsheets. Using Jason's Thread Tracker as an example once again, column A = 0, column B = 1, column C = 2, and so forth.
That means that for the first bracket, the one that says URL COLUMN, I need to put the number associated with the column where I have all my thread URLs. In Jason's Thread Tracker, I have all my links in column B. Since we've just discussed that A = 0, B = 1, C = 2, and so forth — then I should put 1 inside that bracket.
Beautiful.
Using that same logic, just replace the rest of the column placeholders with the appropriate numbers. In Jason's Thread Tracker, the date column would be 0, thread type (which I actually have under "what", not type — oops) would be 3, character name would be 4... and the rest is easy to follow. The important part is that you have all the information in your spreadsheet to comply with what's required for the activity check comment.
My code ended up looking like this after I was done replacing everything:
STEP FIVE:
Then, go to Publish and select Deploy as web app.
This should show up for you:
Click on Deploy
Review permissions... And a new window will pop up. You need to click on Allow for your new script to do its thing.
Finally, click on the bit I've highlighted to see your script in action.
STEP SIX:
Don't worry too much about the mess above and just grab the code from the text box. It'll look fine once you plug it into your activity check comment. If you have doubts, here's Jason's point comment on the activity check post after I used the script for it.
For thread trackers with multiple sheets, you need to make sure the month you're automating is on the leftmost corner. That is to say, if I wanted to automate Frisk's March activity right now, I just need to make sure their spreadsheet is arranged like this:
So, latest month first since that's what we really want for our AC comments.
And if you need to automate your activity check comment again, then just go to Deploy as web app like last time. The box that will pop up this time will be a little different, but all you need to do is click on latest code. It should show up with the same information as your spreadsheet.
EDIT: If you're having trouble with your date formatting as (for example) "Tue Mar 28 2017 00:00:00 GMT-0700 (PDT)", you need to disable auto-formatting. As Boba (Yato's player) explains it:
And that's all, folks! If you have any questions, let me know and I will do my best to explain.
ADVANCED COURSE TO LAZINESS: PART I.
Automatically Calculate Points
This formula will automatically calculate the amount of points your comment thread is worth based on the type of thread, and the number of comments.
STEP ONE:
Open up your spreadsheet and double-click on the first cell in your POINTS column where points are actually being written (so row 2), so it goes into edit mode.
STEP TWO:
Copy and paste this code into the cell:
You will want to replace these following things with the proper cell numbers:
[TYPE CELL] = Replace it with the cell name that is associated with the type of thread (cell names go by Column first, then Row Number. Aka "G2" for the cell in the G column in the 2nd row). In the example spreadsheet template, that would be C2.
[COMMENT CELL] = Replace it with the cell name that's associated with the comment count. If you're using the example spreadsheet template, that would be F2.
When you're finished, hit enter.
STEP THREE:
Select the cell again but only SINGLE click it. In the lower right-hand corner, a little blue square will appear. See it?
That
Hover your cursor over that little box, and your cursor will change into a cross. When it does that, click and drag our mouse down the point column, letting go when you've reached the number of rows you think you'll need for threads (don't worry if you turn out wrong, you can always add more later!). This will copy the code into every box that was selected by this method, and modify the cell selection to match each row so you don't have to edit everything. Neat trick huh?
NOTE - If you're using the automation process, and you set up more rows like this than you actually needed, it might pop up in the automated code as blank links and what not. Which is weird, but just delete them before you post in your AC thread and it should be no big deal.
FINAL STEP - USING THE COUNTER:
Now your point calculator is all set up! Click out of that column and never touch it again unless you need to add more rows.
The formula is set up to activate whenever you fill out a thread's Type and Comment Number, and it will change the math formula it uses based on the type you use (so if you put it down as "Action" the formula will be Comments = Points, while if you put down "Network" or "Inbox", it changes to Points = Comments * 0.5. Meanwhile AC proof threads will automatically shave off 5 or 10 points from the total count, depending on if it's an action or network AC proof.
These are the specific types that will activate the formula:
As for the comment part, you just enter in the number of comments you have in the COMMENT cell. And Vola! Less math to do!
NOTE: If a thread changes forms (as in it starts as a network thread but turns into an action thread), the formula will not take that into account, so be aware of that.
Never Type out Types Again
If you're lazy like me, it's a pain to type out the same thing over and over. So why bother? Instead, we're going to make a drop down list so you can select those things with ease.
STEP ONE:
Open up your spreadsheet and select the first cell in your TYPES column where points are actually being written (so row 2). Either right-click on it, or go to Data on the top menu, and select Data Validation. It'll be on the bottom, so scroll down if you don't see it right away.
STEP TWO:
You'll be taken to a little menu to fill out.
This!
What you'll want to do is go to the Criteria option, and selection the option List of Items. Then in the text field next to that, C&P this list into it:
Then make sure the Show dropdown list in cell option is checked off.
When you're finished, hit Save.
Note: You can change the list of items to something else if you'd like, but this is the list made to work with the point calculator, so you'll have to edit that too to work.
STEP THREE:
Just like before, we're going to do the click and drag method.
Select the newly modified cell again but only SINGLE click it. In the lower right-hand corner, a little blue square will appear. See it?
That
Hover your cursor over that little box, and your cursor will change into a cross. When it does that, click and drag our mouse down the type column, letting go when you've reached the number of rows you think you'll need for threads (don't worry if you turn out wrong, you can always add more later!). This will copy the code into every box that was selected by this method.
NOTE - If you're using the automation process, and you set up more rows like this than you actually needed, it might pop up in the automated code as blank links and what not. Which is weird, but just delete them before you post in your AC thread and it should be no big deal.
FINAL STEP - USING IT.
Now it's all set up! All you have to do is hit the little arrow on the cell, and a drop down menu will appear. If you're the keyboard type, you can also double click and start typing in the type, and the option will appear to select will appear there too. Super simple!
That's all for now! I'll be typing up more tutorials in a bit, once I finish with beta testing them (as in testing them on myself and screaming). If you have any questions or suggestions, feel free to let me know!
no subject
no subject
no subject
no subject
For the code to work, you need to put the numeric value of the columns instead of the letters. That is to say, instead of A you need to put 0, instead of B you need to put 1, and so forth. Here's an example of how each column is numbered, from Jason's spreadsheet:
You also need to get rid of the parts I've highlighted from your HTML code.
It should work after correcting these parts, but let me know if you run into additional trouble!
no subject
no subject
ADVANCED COURSE TO LAZINESS: PART II.
Automatically Tally Earned/Rollover/Spent Points
Okay so if you did the stuff I instructed in Part 1, you already have an easy way to calculate how many points your threads are worth. However, at the end of the day, you still need to add them all up and calculate a few other things. Or...do you?
This trick will make things a bit easier.
STEP ONE:
Open up your spreadsheet and click on the final column. From here, you want to add another column (Insert > Column Right) or use the column next to it.
ALTERNATIVELY, if you already have a column designated for adding up total comment points like in the basic template, you can use that column too.
STEP TWO:
Format the columns so it looks something like this:
You can make it look however you want, but the important part is that there should be a spot for Earned Points, Rollover Points, Spent Points, and Total Points Left. If you need your column to be bigger, you can do so by making the column itself bigger, or merging it with a neighboring column.
STEP THREE:
Once you have it looking how you want it to look, now comes the formulas.
For Earned Points slot, C&P this code:
Point column is literally the column where all the points are being calculated. So in the example picture, that'd be G, so the formula would be =sum(G:G).
For Rollover and Spent Points, there is no formula currently as I have yet to work out one that wouldn't end up taking more work than it's actually worth in the long run. So for now, enter the points in manually there.
Finally, for Total Points Left, you want to enter in this formula
Replace the brackets with the cells associated with those numbers. This will allow your points to be automatically calculated. The total points left at the end of the month will be your rollover points at the start of next month. Easy peasy right?
And that's all for now! I still have a couple others i'm still working out, but for now I hope this helps you guys attain further goals in laziness!
AUTOMATIC COMMENT COUNT
Here's the snippet to stick in your comment count cell:
So mine looks like:
You might have to add or subtract 1 comment (code below) because of the fact that it's counting every time your username is mentioned, which I will elaborate on:
Add 1 Comment:
Subtract 1 Comment:
Also, for whatever reason, the importxml command only refreshes every 2 hours or so, and it is impossible to trigger a refresh. I dug through a bunch of pages and it seems like everyone using it has complained to google about this, and there's no real fix. Fortunately it seems mine are refreshing a lot faster than 2 hours (more like every 10 minutes or so upon testing), but ymmv.
AUTOMATIC THREAD DATING
Here it is:
AUTOMATIC AC COMMENT | SPREADSHEET TEMPLATE
*Image calculation is slightly wrong because I forgot to mark the AC thread correctly so it counted up the points for that anyway, but it'll work if properly labeled =x
no subject
But for some reason the INBOX type only counts 1 post when I link one of my own Inbox threads.