ACTIVITY TRACKER - DEERINGTON

[pursuitofcappiness]

Hi guys! This is Xy @ assemble I made myself a Google Sheets-native AC tracker to calculate AC points and print out a fully-formed comment. Since there was demand for one that worked outside of Excel, I thought I would share it. All that's really necessary to set it up is in the instructions section, but I've included a section explaining the formulas, a faq, and an optional set of instructions to get it accessible by phone. As a caveat, I won't be able to help troubleshoot any issues with the phone setup as Google Sheets is not well supported on mobile platforms. Please let me know if there are any bugs or if any information is unclear, as it has been tested, but not exhaustively.

INSTRUCTIONS

This should be all you need to get going, but please also read through the formula and faq sections!

1. Go here and select "File-->Make a Copy" and save it to your drive. You must make a copy as this is the template and you cannot directly edit the template.
2. Do not rename the tab "CurrentTracker". You may make new spreadsheets in the workbook but you must always have one named "CurrentTracker."
3. Fill out any fields that are not highlighted yellow. The yellow fields have formulas in them and will populate once you have filled out your spreadsheet.
4. At the top, select "Deerington Activity-->Print My Activity Comment". If this is your first time running it, google will ask you permission to run from your account. You need to give it permission! Afterwards, a panel should show up with a preview and a textbox with all that you need.

FORMULAS

In case you accidentally delete your formulas, here they are with explanation. The points are already populated for the first 100 rows, and the comments are optional. You must replace the 0 with the number of the column you are applying the formula to.

COMMENTS (optional)

➟ This is for automatically counting comments from a given link (cell A*) and your username (cell K4).
➟ Basically does a ctrl+f for your <user name="yourusername">
➟ Subtracts 1 if you have the entry selected as "Mine" since your username will be counted for the entry.
➟ This will only count on unlocked posts and posts that are not marked 18+, as google spreadsheets can only "visit" dreamwidth pages logged out, and is blocked by the "are you 18+?" page.
➟ Too many uses of this (20-25) will slow spreadsheets down.

=IF(A0="","",IF(C2="Mine",(COUNTIF(importxml(A0,"//span[@class='ljuser']"),$K$4)-1),IF(B0="Toplevel/Post","N/A",COUNTIF(importxml(A0,"//span[@class='ljuser']"),$K$4))))

POINTS

➟ Calculates how many points based on Post Type column and Comments column. 10 for a flat toplevel, 1 for each network, and 3 for action. You will need a new entry if your network thread becomes an action thread.

=IF(B0="Toplevel/Post",10,IF(B0="Network",G0,IF(B0="Action",G0*3,"")))

TOTAL EARNED

➟ If this is a check-in, it will add everything up in your points column.
➟ If this is not a check-in, it will subtract 30 points for your base AC.

=IF(J2="No", SUM(H:H)-30+COUNTA(I2:I)*2,SUM(H:H)+COUNTA(I2:I)*2)

PHONE ACCESS

Here are the steps to get a link in order to edit + print your activity comment from your phone. This is not highly recommended as sheets is not built to work very well on mobile, but it is an option for anyone really on the go.
➟ You will need the google sheets app in order to edit your sheet/entries. However, the script to print your AC will not run in the google sheets app as you can't access the menu to get to Deerington Activity.
➟ While you have sheets open on your computer, go to Tools-->Script Editor.
➟ A screen should pop up with a bunch of code on it that you can ignore, go to Publish-->Deploy as Web App and copy the Current Web App URL.
➟ Save this URL somewhere in a comment or somewhere else that is easily accessible to you on your phone.
➟ That's it! Visiting this link will operate the same way as Print My Activity Comment. You will again need to give it permission the first time to run on your phone the same way in the initial step.
➟ As a note, you will need to log into your gmail account on your phone to do this, and only the accounts that you have granted access to edit your spreadsheet will be able to run the script.

FAQ

Please read this first if you have any issues!

• My spreadsheet keeps saying "Loading..." or has -1/0 everywhere on the comment count formula, what's up with that?
  ➟ Google spreadsheets is notorious for considerable lag. This is generally a problem with >20, 25 threads. You can simply wait for it to load, or manually input the comment number. Manually inputting a number will erase the formula and therefore stop trying to load the dreamwidth entry to count your comments. If a thread is done, I recommend copying over the formula with the number, just so it has one less thread to load.
• I've waited forever and it still says -1/0 on some entries, but everything else has loaded.
  ➟ If the post is locked or marked 18+, google will not be able to get to the page because it can only access dreamwidth logged out. I've tried to get around this but unfortunately it's currently not supported.
• The comment count formula on this thread is off by 1. What's going on?
  ➟ The comment count is a little dumb and basically does a ctrl+f for your username on the entry and counts up all the instances. So if it's your inbox, select "Mine" under entry, which will subtract where your username is on your inbox entry. If your username is mentioned in a toplevel, simply link from the next comment instead.
• Will the comment count formula still work if I have a common one-word username?
  ➟ Yes! The comment count function will only look for the specific dreamwidth tag for <user name="YOURNAME">.
• I don't see a menu item for "Deerington Activity", where is it?
  ➟ Just give it a minute, it takes awhile to load. If it still hasn't loaded: did you copy the contents from the template instead of copying it by "File-->Make a Copy"? Spreadsheets won't copy the necessary script unless you copy the sheet from the file menu.
• I was resetting my spreadsheet and deleted the formulas by accident! How do I get them back?
  ➟ I've pre-populated the first 100 rows with the formulas for point formula. Scroll down until you find one and drag the square on the bottom right corner up to cover the empty rows. If you somehow deleted all 100 rows, you can simply copy the original formula from the formula section above.
• You said you made edits, but they're not showing up in my spreadsheet. How do I get them?
  ➟ You'll have to follow the original instructions and make a new copy to get any edits. However if you've already started filling out for this month, you can salvage anything you already have by copying the information onto the new spreadsheet.
• I'm having another issue/still having troubles. Can you help?
  ➟ Feel free to comment to this post for assistance! I work full-time so I can't promise I'll get to your comment right away, so make sure to please not wait until the last minute to do your AC if you are going to use the spreadsheet.

As always, enjoy!

NOTE FOR MAY 2019: We all get a bonus of 100 points this month, which is not covered by the spreadsheet since it is unique to this month, so your end comment will have to be edited manually to account for this.

UPDATE FOR MAY 26TH, 2019: Looks like I missed counting up crosscanons in the total. For total earned, please replace your formula with =IF(J2="No", SUM(H:H)-30+COUNTA(I2:I)*2,SUM(H:H)+COUNTA(I2:I)*2). You do not need a fresh spreadsheet for this!

NOTE FOR MAY 2020: We all get a bonus 200 points this month (I think??), which is not covered by the spreadsheet since it is unique to this month, so your end comment will have to be edited manually to account for this.

Comments

ACTIVITY TRACKER - PATCH NOTES

[pursuitofcappiness]

Please do not respond to this comment. Eventually if there are fixes that require re-download, I will update this comment.

ACTIVITY TRACKER - QUESTIONS

[pursuitofcappiness]

If there are any questions that aren't covered by the FAQ, please ask them here and please keep the subject header so I know immediately what this is for while reviewing my inbox!

Re: ACTIVITY TRACKER - QUESTIONS

[boyobscured]

Okay so I seem to have lost the formula somehow so nothing is like adding up. I have NO IDEA how to fix this and the thing about dragging it that's above doesn't seem to be working. What do I do?

wrong account too lazy to switch :)

[lunchbreaks]

Hi there - you can just copy/paste the formula into the first points row and then drag it from there. It won't work if you've deleted all of them, because dragging on spreadsheets just means "copy whatever's in this cell to the next one, but update the references." If there's nothing there, it won't work.

Past this into H2:

=IF(B2="Toplevel/Post",10,IF(B2="Network",G2,IF(B2="Action",G2*3,"")))

Then drag from there.

[boyobscured]

GOSH I FEEL SO STUPID NOW, that worked. Thank you! orz

[witchy_migraine]

Thank you so much for making this! This was my first AC and it was super helpful.

[pursuitofcappiness]

Aw you're welcome! I'm glad you found it useful :)

[tetractys]

seconding this, i love u xy. can never get my head around AC system comment formatting without help lol

[loupnoir]

Hello! This sheet is absolutely FANTASTIC and has saved me a ton of headache when I was still in Deerington, and I'd like to thank you for putting it together!

I was wondering if I have permission to modify this sheet's custom scripts to set up an excel sheet tracker for Ryslig? Like Deerington, Ryslig has a system that counts bonus coins. I've seen a lot of players have grief over calculating their rewards by hand, and I was wondering if I could use your code as a jumping board. Credit would be included, of course!

[pursuitofcappiness]

Sure! Let me know if you need help understanding it!

[loupnoir]

Thank you so much!