Quick OC 2.0 sheet

Made a quick OC 2.0 sheet for my faction, probably more to come. Current features are:

• List members not in an OC
• List members missing an item and the item missing
• List members below a required minimal success rate or above a maximal allowed success rate
• Reward overview over the last crimes
• Reward summary, sums over the last 31 days and averages overall

Additionally there is a quick overview of all the faction members, listed in the order from highest CE to lowest with the crimes their in, their roles, their success chance and when they joined.

Create a copy, check the attached Apps script, if satisfied plug in your API key in the "Meta" sheet cell A2 and set the minimal required success rates as you want. Then hit the "update" button in the "problems" sheet. The faction key must be of a member with "Faction API" permissions and at least minimal access.

 https://docs.google.com/spreadsheets/d/1SmeJR_8S7w6BCAqJA1MUifxgmyWggE4VeOJb--9DmTM/edit

This is donation ware if you like it I would appreciate some donations. Also if you want more features please tell me, regardless of if you donated or not.

If new crimes that are not on the list in the "meta" sheet get released: 

Just put the crime name and min/max pass rate anywhere in the list, I had them sorted by the level to make finding them easier, but the order doesn't matter iirc. You can just add new crimes to the bottom as they get released

Added Rewards summary, just averages at the moment and fixed a bug where the Crime Type wouldn't show up in the Rewards tab. Also crimes are guaranteed to not be duplicated in the Ongoing, Participants and Rewards section

(Missing) Items are now shown by name

Great sheet so far! 10/10

bump

Wow this sheet is awesome. Great work! Our faction is currently trying to figure out the best way to manually calculate payouts for crimes as the current system does not cut it.

Do you have any plans for developing a strategy to calculate payouts?

My current thought process that the current built in system doesn't do.....

1. Ratio of faction/participant payout (this is the only thing that the automated system handles)
2. Factor in the market value of the required items that are consumed in the crime. If the person in the crime provided it, then compensate them from the overall payout. Otherwise, do not add that total to their payout. (it should then be removed from the payout total and given to the faction or the person who provided it) Current market value of the item would likely be used for compensation value. I am trying to create this concept myself in a google sheet that is way less developed than yours but I use a checkbox for each required item that says "participant provided item" and it adds or subtracts the item value from their payout amount.
3. Order of crime countdown. The first person to join a crime requires a longer commitment and therefore should get a higher percentage of the payout. The last person to join the crime requires less time in the crime (24 hours in total technically) and should receive a smaller portion of the payout.

That is my thoughts so far.. if there was a way to calculate these amounts on something like the reward overview page of your google sheet that would be absolutely amazing. I would also be very willing to provide you a very healthy donation if that would help make this happen!!

Am i stupid? Obvs. But also i can't get this sheet to work at all

Do you have faction API permissions?

Yes i do

If on mobile the button won’t work to run the script, can only run it from desktop unfortunately. 
Only way around it would be have the script auto run on open so no button press needed. If OP doesn’t do it then I’ll look to change it as I’m hardly on a desktop so need the mobile version too, just no time to tweak it atm

What happens when you click update? Just nothing or are there error messages?

You can now add on open triggers by opening the "Manage" menu in the toolbar (next to "Extensions" and "Help") and selecting "Add onOpen triggers". Dont know if it makes a difference on mobile though.

That may be the issue, i'm using my phone. I'll try again from my work pc later. 

ok, works fine on pc. thanks!

Easiest fix would be to use an onEdit with a checkbox on the sheet that when checked will run the trigger

didn't know that would work, I thought spreadsheets were completely uneditable on mobile, thanks! Added such a checkbox.

• Reward summary, sums over the last 31 days and averages overall

Is there any way to change this to calendar month? asking because my faction is pooling pay and to simplify I'd like to pay monthly.

i can add my own formula obvs but i suck at spreadsheeting yours is much easier on the eyes. lol

I understand I need to have faction API key and minimal access which I have done and added to the meta tab. I'm on mobile so I tick the update checkbox which clears itself after a short wait but I see no details and no activity on the API key.

Am I doing everything correctly?

Can I see some errors anywhere?

Try Manage -> Update

Where is that, I don't see that option on mobile.

Edit: I ran it on desktop instead and it worked but it only picked up the two weeks or so, not a month?

still using and loving this sheet.

on the reward overview page, i'm trying to get a total per month reward sum going, but every time a new crime finishes the cells move and the number is calculated wrong. i suck at spreadsheet formulas, and everything i have found doesn't work. Do you have an easy formula i can add to a column at the end?