Podfic Tracker with Dynamic Menu

[godoflaundrybaskets]

This is a little tutorial thing that I've made for the podfic tracker since I'm doing some fun stuff.

Here's a link to the sample spreadsheet: https://docs.google.com/spreadsheets/d/195BZ1QPtTHjT_vvZzyolHFJSKq_Z9m-4RqRA2ZQz_JA/edit?usp=sharing

You can't edit this one, but here's an example of how I use it: https://docs.google.com/spreadsheets/d/1SG2m5_Xm7hkaRpGiK-Iwvly8EZ8CUvz8GpsOVsnDLeg/edit?usp=sharing



Any suggestions/requests/suggestions are more than welcome! I may or may not be able to assist but I'm usually willing to help out.


There's several spreadsheets here:

1. 2018: this is just so I can get a new spreadsheet each year for tracking
2. Analysis: Right now I have a handful of things there getting the # of times a thing has been used (i.e. how many fics i've done for a fandom or what status all my WIPs are in
3. DataValidation: In google sheets when you do "Data->Data Validation->List from a range" it allows you to pick a column, grabs all the values in that and creates a dropdown list for you. This also helps insure data consistency. This is where I put all of my validation columns for the most part. The ones I currently use are:
   • Fandom
   • Series (if the fic has a series)
   • status (of the podfic: Recording, wating on permission, etc)
   • author,(of the fic)
   • category (m/m, gen, etc)
4. FicDetails: this is where i keep information about the fic itself if it is multi-chaptered. I'm not doing too much else with it right now but I wanted to be able to put a link to the fic in one place and keep track of some other information about the fic. Most of the stuff (other than the fic title) isn't used right now.
5. ChapterStatus: this drives a lot of stuff that we'll get into later. For now, it's where I keep track of the individual chapter status of any multi-chaptered podfic.

Hide/Unhide Columns:
So, first and easiest to talk about is the hiding of columns. As I added more detail, I wanted the ability to filter some of it out when desired:

This is actually a custom function that I've made in google sheets. It's not really picking the chapters out dynamically at this point and it's specifically checking to see if cells A3 and A5 have 'y' to see what it should hide.



1) update the value below what you want to hide to 'y'

• chapterInfo (columns specifically relating the the chapter status - columns M:T)
• details (some more data you might want to track - isMultivoice, category, etc but might not want to see all the time - columns H:K)

2)  In the help bar open the menu called: "Custom Functions" > "Hide/Unhide Columns". Click it.
3) The columns should now be out of the way!


Dynamic Chapter Dropdowns and Data


Add A New Author

Secondly, I really like the dropdown lists. So I created a couple of just plane validation columns. Here's an example of me adding a new author to the author column. This also makes it very easy to do stats stuff on it because you're not accidently going to type the authors name in wrong. Here's an example of me adding an author for a new fic:



1. Go to the "Data Validation" tab
2. Add the author name under the author column
3. When you go back to the "2018" sheet, the new author is now in the author dropdown list.
4. Fill in any other details that you want about the fic.

Now that we've got the new fic populated it's time to show off the dynamically populating chapter dropdown.

While I could just create a menu of 1-100, I (1) only want to see chapters that are actually valid for this fic and (2) want to be able to populate and view the chapter details on the main page. This turned out trickier than I had hoped but I've got at least a rough start of it!

Populate Chapter Data
First off, we have to populate the data. This involves putting the data in both the FicDetails and ChapterStatus sheet.



1. I add the fic to the FicDetails sheet. (you can't really see it, but the fic_title on this page is driving the fic_title dropdown in the ChapterStatus sheet.
2. I add the chapters and related details in the ChapterStatus sheet.



Run "Populate Dynamic Chapter Menus"



3. Now navigate to the "2018" page where we want the chapter menu to appear, we need to trigger the "dynamic" menu. I'm still working on optimizing this so I don't have it constantly checking to see if it needs to run. 
4. In the help bar open the menu called: "Custom Functions" > "Populate Dynamic Chapter Menus"
5. Wait for the script to finish running. 
6. Now, when you can see that there's 3 chapters and it pulls the data from the "ChapterStatus" sheet and displays it when the associated chapter is selected.

KEEP IN MIND: the data is just being pulled from the ChapterStatus. You can't just update the data in the main page.

Add A New Chapter
But what if I want to add a new chapter? Unfortunately, since the script isn't currently dynamic you will have to trigger it again.



1) add a new column with the new chapter details
2) go back to the "2018" sheet and re-run the "Populate Dynamic Chapter Menus" script
3) you can now select the new chapter and view its details.

Unchapter A Fic
If you remove the fic entry from the FicDetails sheet, then when the  "Populate Dynamic Chapter Menus" is re-run then the chapter dropdown list will disappear.

Comments

[sylvaine]

This is immensely cool. Thank you for sharing!

thank you so much for this

[kaixo]

This is amazing.