Spreadsheet

Discussion in 'GENERAL DISCUSSION' started by twiki, Jan 10, 2014.

  1. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Re: Spreadsheet WTF

    Haha Lunartic I may even do that in the future! :D Whether is it "forced" or entirely voluntarily, who knows! Besides, I'm choosing between taking up Computer Engineering and Electrical & Electronics Engineering for my university (Or maybe more commonly known as high school) in which I'll enter in 2 years' time. If I actually choose CE over EEE, I might touch on some interesting software programming! I might be able to teach you a thing or two next time if I choose CE! :D

    Meanwhile I'll give your suggestion a good thought to consider during my 2 years before I enter my university! For now I'll invest in music, my subtle passion. Thanks Lunartic! :D

    EDIT: Looks like you need to pay to access the better features for GAME MAKER STUDIO from what you said. Unfortunately, I'm not earning an income now so maybe I'll wait until my income comes in then I'll see. :)
     
  2. ogster

    ogster Elite Member

    i just learned how to edit my scores on the ipad. idiot me to take this long to find out how. had always waited to get my hands on a laptop to enter scores. stupid question coming up... can i edit box shades from ipad too?

    enlightened techno-imbecile
     
  3. Wheat1328

    Wheat1328 Ultimate Survivor Moderator

    Yep. Should be a button at the top that says "Fonts". From there you can change font, size, text color, and background color. At least that's how my iPhone is using the Google Drive App
     
  4. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Re: Spreadsheet WTF

    Someone tried to automate the greying of cells when an achieved max is reached. However, it's not working. It seems like this could be impossible as Google mentioned you can't have conditional formatting based on another cell. But who knows...Some genius may eventually figure it out! !)

    Btw, since we have coop multiplayer now, and these Day I levels are definitely not just plain old the same thing, should we also include clone counts for these coop levels in the spreadsheet as well? Someone has to recount the total number of clones in each level though.
     
  5. Wheat1328

    Wheat1328 Ultimate Survivor Moderator

    Adding Coop totals to the single play isn't happening. I'm alright with it down below. I won't use it but lets keep that 100% separate from single player
     
  6. <|-|!<0

    <|-|!<0 Dr. Doomsday


    200% separated!

    I think it should even be in another page, like you did with Smash Hit. :?
     
  7. Wheat1328

    Wheat1328 Ultimate Survivor Moderator

    Done.
     
  8. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Never thought of combining them anyway. Besides, they are 2 very different things. The separate spreadsheet for coop is great! Vivic is already rushing in with his scores...
     
  9. <|-|!<0

    <|-|!<0 Dr. Doomsday

    Thank you wheat, good job!
     
  10. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    It's apparent that the version of Sheets (Google Docs Spreadsheet) we are using is outdated. There's a newer version of Sheets that rolled out at March/April 2014. I just researched a little and found a lot of new stuff (ESPECIALLY conditional formatting) that we can make use of to enhance the current spreadsheet. I will assure you that I will see to the changes and take good advantage of the new Sheets. There will be significantly enhanced automation of the spreadsheet such that we need to do less to keep it updated without being confusing (As it is albeit slightly now, since some old top clone records are not returned to normal cells, and some new top clone records are not highlighted appropriately).

    However, this would mean I have to completely destroy the old one since only new sheets will have these new features. Our spreadsheet, being an older version when it was created, will not be updated to the new one unless it is completely replaced.

    I therefore seek the approval of you guys. Okay? :mrgreen:
     
  11. n0phun

    n0phun Master of Disaster Moderator

    Yes, I saw that when they announced the new version. The use of formulas in conditional formatting there is exactly what we need.

    Google announced that they will update the old sheets to the new ones some day, at least as far I remember. Thought we can just wait it out. But now their wording has changed: "All previously created spreadsheets will remain in the older version of Sheets for now."

    So let's try the move. But the one to decide that is Wheat.

    I suggest not to destroy the old sheet for starters, but make the new one and block the old one for input, plus put a link to the new one on top of the old. Even the new one should be blocked for input for a day or two, let's first look if everything works out okay.

    Cheers, n0phun
     
  12. Wheat1328

    Wheat1328 Ultimate Survivor Moderator

    I'll look into it sometime in the next couple days.
     
  13. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Maybe I was too rash in my wording. :P I meant to ultimately destroy it, but not until the new spreadsheet is completely functional, and the link of the spreadsheet on this fan site replaced. So we'll have v2.0 and v3.0 online for some period of time.

    If you guys are ok on it, I can start work on it tomorrow. But I'm afraid if Wheat starts work on the spreadsheet as well, we'll be faced with counter-productivity. So if we are all agreeable, I can start the work tomorrow and might be able to roll it out by tomorrow as well (I've got the whole day free :mrgreen: ). If Wheat wants to do it tomorrow (Or he might had already started on it), then I'll leave the work to him.

    What's the plan, guys? :)
     
  14. Wheat1328

    Wheat1328 Ultimate Survivor Moderator

    Go for it Light Rock
     
  15. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Here are my improvement plans for spreadsheet v3.0! ;)

    - Complete Automation of greying and blacking cells with the highest current clone count and maximum possible clone count, respectively. This means no more manual greying of cells. Also, the conditional formatting to blacken cells will no longer need updating; we will just simply need to update the max clone number and the conditional formatting will re-adjust itself. The highest clone count for each level set, each day and overall will all also be automated, since they are similar to automation of grey cells.
    WARNING! This needs to be tested first! This is because indefinite clone counts may cause problems.
    - Done! Indefinite clone counts do not cause any trouble. The Anti-Spam feature ensures values such as 20+ or ? cannot be entered. Only the corresponding integers will have their cells formatted accordingly.

    - Automatic sorting of players based on clone count. Just like what Wheat did, albeit this will be automated. :D I don't have a clue how to do this yet, but I will research! - After hearing n0phun's opinion (He mentioned that it confuses people on where are their columns), I decided to drop the idea. Besides, I don't think it's even possible; I don't think columns are able to sort by themselves.

    - Automatic input of 1 clone count for Spiral and Size Matters once a new player enters his username onto the spreadsheet. This is just for fun! :lol: - Previously undo-able due to data validation, this is now implemented thanks to the ability to use protected ranges, preventing people from entering nonsense after they know how to remove the data validation. There were initially some problems, but they were solved using the IF function, removing the data validation and redirecting the conditional formatting to the username cells.

    - As a continuation of the previous point, the words "New Entry Here" will be removed once players enter their names and hopefully, a new column will appear at the end to keep 2 columns available for new players - The first part was done using a formula on that cell. This system is extended to almost all existing players (In case anyone takes down their records) except from ogster to Wheat, as the "Achievement Guide and Walkthrough" spans across their columns. I sort of solved the second problem, albeit it will still be a bit of a manual process, but less tedious. Basically, although the entry of new player will NOT cause another column to suddenly appear (Well, that will need some witchcraft :lol: ), all we need to do is to unhide the columns at the end (I hid 2 columns) and then hide back 1 of them at the end (Or just leave them there, although it does make the spreadsheet unnecessarily longer :P ). The formatting and validation is already applied to the 3 hidden columns, so less work has to be done.

    - Anti-spam spreadsheet. Although it is unlikely anyone would spam dumb numbers onto the sheet, you never know! Only integer values will be allowed and a certain upper limit is set (For those with indefinite numbers, it would probably be either a reasonable limit, or just the total clones you get in the level). Have a clue how to do this, but not quite clear whether it will work out? - Done! This system is probably the best I made! :D I used data validation with a rather complex custom formula to prevent high clone counts and allow only integer values.

    - Protection of cells that contain formulas. (Prevents them from being edited) Another anti-spam feature to prevent our formulas from screwing up so that our automated systems to count clones doesn't screw up.- Done! I protected the headers from editing as well. Also, only me and Wheat can edit and insert/edit notes on these cells. Anyone can still make comments if they want, but make a nonsensical/hate comment and anyone can remove them too! If any other frequent forum members want access to edit and insert/edit notes, please tell me! The spreadsheet will be a little hard to see because of the striped protected cells. To make the spreadsheet look more aesthetically pleasing and "clean", go to View > Untick Protected ranges.

    - Time played and Total clones lost. Just hope I can make the spreadsheet correct them properly, by ensuring xxxH xxxM format and adding separating commas respectively. It's not important, so I'll concentrate efforts on the more important ones first. - Done! For Time Played, I just manually corrected them. Hardly anyone puts in their Time Played anyway. The formatting for comma separators was successfully implemented throughout the entire row, since some of them were already formatted, so I just extended it.

    - Make the spreadsheet look the same on mobile browsers as on PC. Apparently, the spreadsheet on mobile browsers is extremely screwy. The main section that contains all our clone counts, as well as other rows have their cells all displaced one cell left. I have personally NO IDEA why is this happening. I MUST solve this, but I need some help! - Resolved. Apparently Column D was causing the problems on viewing. I had to unmerge all cells in that column. There is little aesthetic effect by doing so, so please do not re-merge them!

    - Vertical borders between each player's columns, to allow easier viewing of their own clone counts. This will be tested and let us see whether it helps AND it looks at least decent on the spreadsheet. - I think the spreadsheet looks cleaner without the borders. So forget it! :P

    - Allow editing of the spreadsheet on mobile browsers. For some reason, unlike it's v2.0 predecessor, there are no cells that contain "Edit" (which allows editing of all the cells in its row) in-between the column indicating the row numbers and Column A. - I really doubt this is possible. Google probably wants to strongly encourage users to use their Google Drive and Google Docs mobile apps rather than just using any mobile web browsers to freely edit shared Google Docs, which will defeat the purpose of their apps. Google doesn't want it to happen, so until they decide to allow it, it's probably impossible to edit on mobile browsers. I'll KIV this though.

    - Protection of Conditional Formatting and Data Validation. This prevents them from being altered or destroyed to allow people to start entering unwanted nonsense, which will ultimately destroy my efforts in creating the Anti-Spam feature in our spreadsheet. - There's currently only options to allow the public/users to edit, comment only (Make notes) or simply only view the cells (Completely no editing allowed, which includes changing formatting and validation). There are no options to allow users to only edit the displayed cell contents, but deny them from changing/editing the formatting and validations. It seems to be quite a popular request among the community, so we'll see whether Google eventually implements this, but until then, it's another KIV.
     
  16. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Great news! :D After intense effort on my side, v3.0 of the spreadsheet is now up and completely functional! :D Apologies for taking a week or so; been busy with work on my side.

    Lots of great new features in this new spreadsheet and I took advantage of many new functions in the new Sheets. There are some aesthetic changes on the surface (I made all our usernames blue fonts, for fun :lol: ), but functionally there are great changes. The spreadsheet will appear very unpleasant when it first loads, so give Google a while to sort it out. :)

    To summarize the main changes:
    - Complete Automation of greying and blacking cells. Adjust the max possible clones and the spreadsheet will recognize the change. Even as if levels that have previously have no fixed upper limit are changed to reflect a definite, the spreadsheet will recognize the change and start blacking cells where necessary.
    - Automation of coloring cells that reflect the highest clone count for a level set.
    - Anti-spam spreadsheet. Entering of ridiculously high clone counts is banned (For those with no clear upper limit, I set a rather generous limit), and non-integer values will be rejected.

    All other changes will be reflected in my post above, as well as those that have not been done (I hope to figure them out soon), will not be done, and future plans too.

    However, the spreadsheet appears "corrupted" when you attempt to open it in excel. Apparently it gives me the reason that hyperlinks are not recognized. By far, I have no clue how to solve this, though I might figure it out one day?

    Enjoy the new spreadsheet! :mrgreen: All your clone records should be up to date; I've already made a check.
     
  17. n0phun

    n0phun Master of Disaster Moderator

    Not so good, as blue is for links, and mine and Wheat's are clickable. Yes, they're underlined, but still...

    Nice, well done!

    Seems that Safari on iPhone has difficulties to display the last rows (links to everyplay / comments), they are messed up. But my WLAN seems to be gone, anyway. All well with Firefox here.

    The links do work if I download the sheet as .ods and open it with LibreOffice calc.
     
  18. n0phun

    n0phun Master of Disaster Moderator

    Even if it were possible, it's better to do that manually from time to time. Every two or three months, perhaps. As I mostly look at the sheet from the iPhone, where I only have a very small part of the sheet visible, I would not be able to recognize my row (or anybody's) otherwise.
     
  19. FBI Light Rock

    FBI Light Rock Techno Fury Moderator Beta tester

    Yeah, the spreadsheet is not working well on mobile browsers. The columns don't match up to the frozen rows above. Almost everything in main section is shifted left by one cell. So clone counts don't even match. I have no idea what's the problem. Anyone know how to troubleshoot this? :?

    Ok, noted your inputs, n0phun :) I doubt columns can sort by themselves anyway. I'll change the color of the usernames too.

    Do you guys think it's a good idea to add vertical borders between columns? It might make it easier to track your clone counts and not accidentally read the wrong ones.
     
  20. Wheat1328

    Wheat1328 Ultimate Survivor Moderator

    Working fine for me. You'll have to get Lunartic to re-link the link on the front page.

    What should we do with V2.0? Once we're 100% good to go on V3.0 let me know and I'll wipe V2.0 and put a link to 3.0. Otherwise people who have bookmarked V2.0 will just keep updating there and it will be a mess.
     

Share This Page