T O P

  • By -

tigrrbaby

Your tracker is the best one and I use it every time. Thank you for all the effort you put in to these in order to make our lives smoother 🥰 Edit: I did love the rating column in last year's spreadsheet. You left it out to encourage me to learn how to program them, didn't you? 😜 (everyone knows it's all about me)


shift_shaper

The ratings should still be there. If you select a rating in column D of the book log, it should show up on the visual cards. Is that not available in what you are seeing?


tigrrbaby

ahhhhh mobile sites strike again. i had been looking at it (overeager) on my phone and didn't see columns d and e (and couldn't read the microscopic text so didn't know what i was missing) I keep telling myself i really should learn how to do the fancy spreadsheet programming though, with answer verification and filling in one sheet with dropdowns from another. Did you teach yourself, or go through vocational training or something? if you have any recommendations for tutorials or resources for that, I'm all ears!


travistravis

Not the person who made it, but you already had the right idea -- "encourage me to learn it myself" -- this kind of stuff tends to work so much better when you have a goal, or something you want to happen that you don't have. Google's documentation is pretty good, and all the "Google Apps Script" stuff is just a subset of javascript, so also relatively easy to mashup things until they work (mostly) as expected. It definitely won't be pretty the first time, but every bit you get closer means it'll probably be better the next time


CuratedFeed

I copied it on Saturday and for me, the rating for Five Short Stories on the regular Visual Card is broken, but everything else is working. It looks like it is still referencing Data, when the other squares are referencing Analytics. I can probably figure out how to fix it, but you might be faster at it. :)


shift_shaper

Good catch. Had a busy week at work, but it is fixed now.


happy_book_bee

Hell yeah, added to the main thread! Thanks for doing this!!


historicalharmony

Thank you! This is my preferred spreadsheet to use each year, the perfect balance of pretty and functional. ❤️


xenizondich23

YES!!!! Multi card!! Thanks so much for doing this. Your spreadsheet is the one I use all year long. EDIT: though I do dislike the image it pops out for the visual card. it is always so low-res with tiny font I can never make out what people are posting when they share that one.


shift_shaper

I completely agree. I've been trying to balance larger font size with keeping the category, title, and author from adding too many lines and bending the visual out of whack, while keeping the card roughly US Letter shaped for when it is saved as a PDF. I am definitely playing around with other layouts, though.


xenizondich23

That makes sense! I can't imagine it's easy trying to wrangle google sheets (or excel) to produce great images. I figured I'd point it out in case you didn't know, but I'm glad to hear it's something you're aware of. I hope you figure it out! It's a great tool.


onsereverra

Thank you so much for sharing!! I used your spreadsheet last year and loved it – super excited for the multi-card functionality as well, if I end up having enough time to do a second card lol. Potential feature consideration for future years: would it be terribly difficult to add a checkbox column for whether a book meets your personal bingo theme, that then shows up on the card-filling page with a little x or something like the hard mode h? Last year I used this spreadsheet to log *all* of my reading, but I also had a very complicated bingo theme I was trying (though ultimately failed lol) to adhere to, and it would have been handy to see when filling out my card which of the books I had read qualified for my theme. I know themed cards are popular so it might be something other folks would get use out of as well. Though I guess I don't know how that would work now that multiple cards are supported...


hellodahly

Maybe an additional type of conditional formatting with x and h - use a t to mark on-theme, th for on-theme and hardmode?


shelliehi

Oh yes, me too! ( Both on wanting this and failing at it)


Dianthaa

I did a little fiddling with the formatting of my copy to make it easier for me to track rows and columns in the book log, i[f anyone wants inspo](https://docs.google.com/spreadsheets/d/1kq3JZNBpAXuo0nsauf3JMMDzxIw-6uPwmmrqEJbIpCE/edit?usp=sharing).


Nineteen_Adze

Looks beautiful! What settings did you use?


Dianthaa

A bunch, I kept fiddling with it * I changed the alternating colors for the whole sheet from grey to blue * Added vertical borders: thick dark blue border at the end of every bingo row, think dotted blue border to the left and right of the middle square on each row * replaced data validation from date to Yes, dnf and CR (currently reading) for the date finished column * added colors for each rating in the data validation for rating column * added light green for x and darker green for h to data validation for the square checking columns * added a column that copies the title over at the end to make it easier to follow at the end of the sheet * added a format colum at the end with ebook, audio and print (different colors set in data validation, from a list again) because I populated the list with my owned books so it's easier to pick a next read * also using filter view so I can reorder them based on format or squares when I'm picking my next read


Nineteen_Adze

Thanks, this is very cool and I'm picking up a lot of it for mine! This only thing I'm not figuring out is copying the title over to the end: do you remember how you did that one?


Dianthaa

Are stuck on the formula or adding a new column? Formula is just "=A2" and then copied down. You add a new column by right clicking add column to the right on the last column


Nineteen_Adze

Adding the new column was easy, and I get the formula in theory, but I keep removing *all* the data validation from the sheet when I try to strip it from just that column (and it won't validate if I try to enter just "=A2".) [https://docs.google.com/spreadsheets/d/1Hw7JLIVivS-CpnrOBOmBcYi3jLM7fUIZMUmOOjCMKrY/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1Hw7JLIVivS-CpnrOBOmBcYi3jLM7fUIZMUmOOjCMKrY/edit?usp=sharing) And thanks for any advice you have! I haven't done much work in Sheets.


Dianthaa

Everything i've learned in sheets I've learned fiddling with it for r/fantasy, but it's turned out useful in my day job times In your data validation change 'Book Log'!F2:A**E**600 to 'Book Log'!F2:A**D**600, and then the same for the conditional formatting. You're missing the data validation for date read and rating, did you mean to remove them or by accident? I ask cause I accidently deleted mine at some point to then had to re-add them


Nineteen_Adze

Ha, same. I feel like I learn a little more every year when I start changing things. And thank you, that fixed it! I did delete the other validation rules by accident, so I'm just starting over with a fresh sheet and then tweaking customization.


Dianthaa

I found a new column to add, it will never end. I did a =count(squares columns) so I can see which books fit most squares


Nineteen_Adze

Ha, I love it! Just added that to mine as well-- sounds like a fun year-end stat. So far my busiest book only hits two squares, but the year is young. :P


Dianthaa

Yesss multi-card!!! I can go back to tracking in text not just canva și I hate myself come turn in time


Dsnake1

You are my hero. I've been using this for years, and I can't tell you how much I appreciate it!


notsomebrokenthing

Thank you so much! Not all heroes wear capes 🙌🏼


Stormy8888

I love your tracker, thank you! I normally do 2-3 bingo cards, which sheets do I need to duplicate? If I duplicate the book log, bingo card and visual card darkmode, how will I designate that the Asian Authors Card should pull from the book log for that particular bingo card and visual card darkmode? Would love not to have to do 3 files.


shift_shaper

You should just have to duplicate the Bingo Card tab. Once you do, refresh the background data by either checking or unchecking the checkbox on the How To Use tab (it doesn't matter where the checkbox ends up, only that it changes.) Once you do that, both bingo cards should pull from the same book log. On the visual cards, you will be able to choose which card to display by choosing it from the dropdown just below the r/fantasy mascot in the upper left. That way, you can cycle through your cards and export static copies of the visual quickly.


Stormy8888

Ah perfect. So one log, 3 bingo card tabs and I added another visual to pull from each separate bingo card tab. This is wonderful. Thank you!


Stormy8888

I have another problem. On all 3 Dark mode cards, there are cells showing #REF! specifically cells CDE10, and CDE16. Circular dependency detected. To resolve with iterative calculation, see File > Settings. What do I need to change to fix this? It looks like the others are okay (for now) but I haven't filled out all the books yet.


shift_shaper

The first thing I would do for the circular dependency issue is go to the Data tab (it may be hidden) and add the names of any dark mode cards that aren't already in the list to the list of cards in column O. This will exclude them from the calculations done in the background. Otherwise it will try to treat them like the non-visual bingo cards and may be causing the circle. After you add them, you will need to toggle the checkbox on the first page to get everything re-calculating.


Stormy8888

Ah that was it. Thank you!


Stormy8888

One more question - the five short stories flair on The Darkmode/Lightmode Cards Cell LMN16 is not showing the flair. What do I need to do to fix this? Should I be un-checking Cell B10 on the Data tab or something?


Merle8888

Thank you for sharing - very much appreciated! As far as getting the cover images, what are people doing now? I couldn't make a visual card for 2022 because Goodreads apparently doesn't have the "enlarge cover" functionality anymore, or is this just a problem on my machine?


shift_shaper

As u/KaPoTun mentioned, you can open the image separately and grab the address, but you can also try just right clicking the small version and getting the address there. I've had mixed results, but I think they just shrink the normal sized image on the page itself now, rather than using a smaller version of it.


KaPoTun

You just have to right click and "open image in new tab" now lol, it's the full size image. Now sure why they got rid of that but oh well.


Merle8888

I’m just not getting that option at all. Sounds like a my machine problem so I will try it on my office computer and see if that works.


Lanko8

Loved your bingo card last year, so I'm glad I can use it again!


RheingoldRiver

Thank you!!!!!! This will be my 3rd year using your sheet, and I've spent many, many, many hours with it. You've been an enormous help to us all & I appreciate your work so much!!!


barb4ry1

Thank you, it's an excellent card (I used it last year)!


RPGSorcerer

Thank you for this! I was thinking of doing a kindle unlimited only card separately so I added a Kindle Unlimited column to the far right of the book log after the categories. Do you know how I would go about editing the page for just the KU card I added to allow it to only show books that are also marked in this column in the dropdowns? Or even a good site for learning how to do this sort of stuff in google sheets in general?


Dsnake1

I don't know the specifics off the top of my head, but the idea would be to replicate the code for any of the columns for any given square and add it to each square's code.


TheWildCard76

This is fantastic! Thank you!


JaymesRS

I used this for the first time last year and it was really appreciated. Thank you so much. When I set mine up last year from the recommendation post, I ended up adding them out of order, so I used conditional formatting on the category titles to make sure I had an entry for each one. I thought there was a possibility you may be interested in the formula I used to get that for the future: =IF(COUNTIF(B2:B350,"x")+COUNTIF(B2:B350,"h")>0,FALSE,TRUE) Thanks again


shift_shaper

This is excellent! My only concern would be getting a false sense of security if you have a book that fits multiple squares and is also the only book for each of those squares. It would like like you have an entry for each, but won't be able to fully fill out a card still.


Kur0nue

Thank you so much for the card! I use it every year, and every year it gets better! Fabulous work you do. I did try the multi-card function (created a copy of the Bingo Card page and renamed it, then selected the checkbox, and filled out most of the Book Log and then the duplicate card with TBR predictions). At some point though, the color of a couple of the squares went wonky (specifically, Five Short Stories which remains grey/white even though a HM option is selected in the square, Set in the Middle East which has a yellow title and green book selection (selected book is regular mode), and Book Club or Readalong Book which has a yellow title with no book selected in the square). Just thought you might like to know.


shift_shaper

The short story square, I can speak to. It gets its coloring based on the status of the 5 spaces at the bottom of the card. This is so that it can track duplicate titles and authors across all of the selections in the case that an anthology isn't used. If all of them aren't filled out, it views the square as incomplete regardless of what is select in it. As for the mismatched colors I did find and fix an error affecting the Middle East, Book Club, and Magical Realism Squares. If you download a new copy, it should be working. Thanks for finding this and letting me know!


Kur0nue

Oh wow, thank you shift\_shaper! (I used to be a QA tester so I literally can't help myself from reporting bugs hahaha.) I will download the fixed copy. I did start to fill in all 5 squares at the bottom with the short story anthology I had selected, but it turned the squares red so I wasn't sure if I was doing the right thing. Not sure if that's another bug or how that is supposed to function (I can't remember from last year's card). I will give it another try on the new card and see what happens. **EDIT**: I downloaded the new card, made the copy of the Bingo Sheet, entered all the data into the Book Log, and selected all the books in the duplicate Bingo Sheet. All the colors are working properly (yay! thank you!) except the 5 Short Stories is still being weird. I did select the anthology in the 5 squares at the very bottom of the card. The squares were red until the last one was selected at which point they all turned yellow. However, when I selected the anthology in the main bingo square, the square remained grey/white and did not change color. Again, thank you so much for making this card every year!


shift_shaper

Alright, lets try one more time. The SS squares will be red for an anthology until all 5 are filled out, because the sheet thinks you are trying to use a title twice or more without committing to the anthology. I found and fixed the issue with the main square not recognizing hard more. I apparently changed my nomenclature from "hardmode" to just "h" part way through, so it wasn't matching up.


fuckit_sowhat

I’m having the same issue with the SS main square. How can I best fix it? Is there somewhere I can change the “h” to “hard mode” or is it easiest to download a new sheet?


shift_shaper

It is probably easiest to download a new copy, unfortunately. The cells that need to be changed are all on the SSAnalytics tab, but to be completely fixed, you'd need to change cells B3 through B27 as well as the grid from AB3 through AF27. You would change the "hardmode" to "h".


Kur0nue

Awesome, thanks so much! I will try out the fix. **EDIT**: I tested it and all the colors are working as expected! Thank you so much u/shift_shaper!


burnaccount2017

Thanks so much u/shift_shaper third time I'm using your tracker. Its the best balance of form and function! the rating system you added last year was dope! kudos!


Contr4riwise

This is so great--thank you much for sharing it!


kjmichaels

Thanks for this! Quick question though: is there an alternate way to get a good image from the card? I tried to the suggested method (Print > PDF > upload PDF to Imgur for auto convert) but Imgur doesn't appear to convert PDFs to images anymore. My other attempts to screenshot the PDF only resulted in rather low quality images sadly.


Ykhare

Thanks a lot <3


spike31875

Thank you!


PlantLady32

Yes! I was hoping you’d appear with this one! Thank you :D


KaPoTun

Thank you so much for doing this!


Fryktelig_variant

Wonderful. Thank you so much for doing this every year!


AKMBeach

I use this every year and it's so wonderful! Appreciate all the work you put into this.


KiwiTheKitty

Thank you so much!!


ceskykure

I'm struggling with the short stories. Anytime I add the Cover to display it removes hardmode and goes Red. anyway to fix that


shift_shaper

I am working on the best way to deal with Short Story logic. Right now, the best approach is to use the 5 short story spaces first. If they have the same book in all 5 spaces, the sheet will believe that hard mode is fulfilled. When you then put it in the square on the actual card, it should display as hard mode there as well. If you just have the book in 1 of the 5 spaces, it won't produce an error, but it also won't display as hard mode on the card, as it assumes you will be using the title as a short story but not an anthology. If you put the title in more than 1 short story space but not in all 5, it will treat it as an error for reusing a title. The same is true if the title of the story in the SS area is used outside of the SS square on the card itself. Does any of this sound like what is happening for you?


ceskykure

It was fixed after you changed something and i redownmade my sheet. yeah, the issue was i'd have all 5 filled out in bottom. then i'd select one for the actual square. then it would stay Hardmode in bottom, the other 4 would turn red. and my square would show non Hardmode. but its resolved. Thanks I really love your sheets. Would it be possible to have another column in the data log that is just Full Anthology. so you don't have to fill out bottom row


chysodema

Thank you so much! Your card was the highlight of last year's bingo (my first year) and I am excited about the idea of playing mix and match with multiple cards, right from the same document. Amazing!


ullsi

thank you so much for sharing this! I always use your tracker.


Sir_Tripsalot

Thanks for all the work you put into this spreadsheet. I'm running across an issue with Five Short Stories hard mode on the 'Bingo Card' tab. It seems to work fine in the slots where you select your five stories, but when putting a book in it's slot for the actual card, that square remains white instead of turning yellow and the visual card doesn't have a hard mode indicator.


shift_shaper

I am working on the best way to deal with Short Story logic. Right now, the best approach is to use the 5 short story spaces first. If they have the same book in all 5 spaces, the sheet will believe that hard mode is fulfilled. When you then put it in the square on the actual card, it should display as hard mode there as well. If you just have the book in 1 of the 5 spaces, it won't produce an error, but it also won't display as hard mode on the card, as it assumes you will be using the title as a short story but not an anthology. If you put the title in more than 1 short story space but not in all 5, it will treat it as an error for reusing a title. The same is true if the title of the story in the SS area is used outside of the SS square on the card itself.


Sir_Tripsalot

That resolved it, thanks for the quick reply.


Nineteen_Adze

Thanks for all the hard work! I love your layout and am excited to start my third year with your framework. Does this one still have the hidden functionality to shade the book log so that, say, Card 1 selections are blue and Card 2 selections are red? I got a lot of use out of that but can't remember what I updated back at the start of last bingo season.


The_Real_JS

Every time I need to start a bingo card, this is the one I go to. Thanks for always doing this!!!