Welcome to a mini-guide about the potential of using spreadsheets for linkbricks and guides. My experience and examples are based on using Google Drive spreadsheets, but function/formula concepts should be applicable to other spreadsheet programs.
I don't know if anyone else is using spreadsheets in this way already, but it seemed worth explaining the possibility. While using spreadsheets to keep track of data isn't a new concept this is more about formatting all that data into posts with spreadsheets. They can be designed so updating the spreadsheet leads to a single copy+paste to replace an entire old post with a new post containing the new entries. This reduces how many places/times data has to be entered and acts as a backup.
This can be useful for:
ReRelease RiGs
So many linksHard to check for duplicates
Guide Management
The text of the link data can really get in the way of keeping track of what's in there and where. If a guide is easy to maintain, it's easier to keep it going.Possible to have multiple people adding entries and the thread owner just has to update the post/s with a copy+paste for each post to update.Or a way to pass on the guide in a format that's easy to use and restyle if the original thread owner no longer wishes to maintain it.
For example: Starting the Bewere guide was dependant on if I could figure out how to make it convenient to maintain, from the start its been completely run out of a spreadsheet. Highlights: no hunting for the right line to enter/tweak updates, auto-generates the little index of items on each post, lights up cells to remind me of missing BBcode tags, and lets me know if I've reached the max character count so I know when I need to expand.
Use the File menu: Make a Copy If you want to use a template, even just for messing around [this does mean you need a google account to use these since I'm not making open to edit copies, if you want to just see what it looks like containing data here's the examples spreadsheet].
Note: In the examples spreadsheet can also see a potential format for the non-items posts in a sheet. It's what I used for these posts.
Templates with Example Data
LinkBrick:: Link to Templates filled with example data tab [note: two duplicates are intentionally present in this brick, to show off the highlighting of duplicates in the example data]
Index Generator: same similar items as LinkBrick but alphabetized [the LinkBrick example was rebuilt so they no longer match]
[Animal] Afterdeath Dealings Bundle, [Animal] Ferocious Feline, [Animal] Her Light Pocket Paw, [Animal] Rockin' Rouser, All-Star Cheer: Uni-Hornets 2nd Gen., Astra: Cyan Sparkle, Astra: Full Spectrum, Astra: Halo of Light, Astra: Red Glowing Forehead Moon, Astra: Red Glowing Heart, Astra: Soft Hanging Lanterns, Astra: Spell Book Vol.1, Astra: Spring Shower, Astra: Swishing Lavender Wolf Tail, Astra: Warm Hanging Lanterns, Azuramella, Biancamella 6th Gen, Cream Pandaista, Demon's Prestige, Eternal Amadea, Fremere's Guard 8th Gen., Glacial Vault, Green Corallus Egg 2nd gen., Haute Modena 2nd Gen., Heavenly Gatekeeper, Holy $#17 (4th gen), Honest Intentions, Impious Exorcist, J's Sketchbook, Josie's Journey 2nd Gen., Keiko's Delight, Lightning Mood Bubble, Mocha Ice Mood Bubble, Modus Operandi 12th Gen., Morganite Vault, Panty Mood Bubble, Punishing Gula, Q's Sketchbook, Rainbow Mood Bubble, Red Rose Mood Bubble, Rosamund's Redemption, Rosamund's Revenge 2nd Gen., Sablewood Manor, Seracila Pendant 13th Gen., Seven Day Love 2nd Gen., Song of Amour 2nd Gen., Starlight Godina, Steadfast Amadea, Strawberry and Cream Devil Tail, Sugarsuite 2nd Gen, Ty.Go.li Embryonic Vial 6th Gen., Wed to Darkness, Wisteria 2nd Gen., Yahshua's Wings, Yama no Tamago 6th Gen., Yemaya's Pearl 9th Gen.
Items with Poses: Link to Templates filled with example data note: contains example of not filling an entire row [Obnatus Amare, partial top row, to put the tone colored legs all together]
Also available in the examples sheet is a sample of the layout used in the bewere guide.
More things that can be done but may not be in a current template:
Adding more text to entries besides their name.Dropdown to select post data. When a guide gets to be made of a lot of sheets it can be hard to find the right column of data, example in Lake Kindred: Origins - KinApp sheet change the dropdown, and it changes the resulting data to copy.Entering the url and imgs without BBcode. If you use an extension to get quick url+icon links [like using GaiaUpgrade] it can be more convenient to have the entry cell combined, but if you assemble links manually [like I do] it's simple to set up the sheet to add the tags for you.MP Search urls instead of direct to item links. [could be worked into the Recolor template to make clicking item name bring up MP search for the series]This is what jumps to mind while I'm typing this up, but not limited to these.
I am willing to try help figure out customized solutions. Most of my experience in this field is from experimentation and googling things.
Templates have been designed to show the potential and simplify usage. For example the 'Items Templates' have some rather convoluted looking formulas in order to keep a single columns formulas identical, but if you were comfortable making sure you duplicate the right range of formulas when adding new rows could have simplified [or more complex] formulas for each line of a data set. [can set the background to each formula to a specific color/s, when you copy and paste, if all the colors are there you got your formulas
Example below showing what these formulas can look like and what it does:
Note: I did choose the most convoluted formula from the templates. This example comes from the Items with Poses used for showing a single item with its pose icons all linked to its MP page. Unlike the Recolors with individual icons and links, you have many images, on possibly multiple rows, but only one url for all of them, and to save on space you'd only want the url once.
Summary of what the code needs to do: 1) Since data is in paired rows, is it a row of names or links? 2) If it's Odd [links] do A, if it's Even [names] do B 2a) Odd Rows: Check if there's more than 1 row of links associated with this item. Looks for: no new link url AND check there is data past that [otherwise entry at bottom of sheet would never close tags, this is why the Poses sheet has two blocked out rows on the bottom]. Then move the image data from the next column over with or without the [/url ] tag depending on the results. 2b) Even Rows: Check for link url in specific cell, if found open a [url ] tag and fill in the url and item name, if it exists [it does not actually check if you listed an item name. This allows labeling the second line of data if wanted].
[color=purple]if no use: (IF(ISBLANK(D3),"",TEXTJOIN("",TRUE,"[url=",D3,"]",format!B$10,D2,format!B$11))))[/color]
2) Since the data sheet is built to be used in pairs* [titles take up the first row so items use: 2+3 for Item A, 4+5 for Item B, and when using multiple lines: 6+7+8+9 for Item C.] This means Odd and Even should produce different results. a)Odd Rows [icon links]: if both D4 is blank and B4 is NOT blank then the cell shows the contents of B2, but if either D4 is NOT blank or B4 is blank then the cell shows B2[ /url] b)Even Rows [series name+url]: If B3 is blank then do not show any data in the cell, if B3 is not blank then it builds a link from the url in D3 the item name in D2 and any surrounds the item name with any prepared formatting from the format tab [in the template examples I used a leading >> mark]
*While you enter the data first on even rows then odd rows, the flow of understanding the formula is in opposite order to the data since I build the formulas to put odd conditions first and even secondly, but I keep getting confused when I read ISEVEN and wonder why it says seven so I prefer to use ISODD.
Posted: Wed Oct 17, 2018 10:13 pm
Some Quick Notes
Why designed to not have carriage returns directly in cells: when you copy the cell with a carriage return it will put quote marks around that section [it also does it when I copy from mobile], while you could copy the contents of the cell instead of the cell itself, it creates a lot of extra work to clean up.
If the entire post is contained in one cell then at most you have 2 spare quotation marks to delete. The entire post can fit into one cell if you use the [ list] function to make rows instead of carriage returns. The bullet points will remain, though can make them white for less visibility.
--
! signs are used to identify different tab names so you can access data from one sheet while in another. If your tab names have spaces need to surround the name in apostrophes. tabex: SheetNameOne!A1 or 'Sheet Name Two'!A1 $ signs are used to lock columns and rows references. Placed infront of the letter for the column, and number for the row. tabex: A1 moved down a cell turns into A2, but A$1 moved down a cell stays A$1, but will change to B$1 if moved right Conditional Formatting can be helpful to flag cells that lack required information, or even just make the sheet more readable by highlighting every other row. Query is used to pull data based on specified conditions, a bit like a filter, useful for making full or partial lists while skipping empty lines. Also can be very handy when using hard coded icon layouts [like in the LinkBrick] to not have to fix them everytime you add more rows or columns to your main data sheet.
Renaming or moving sheets/cells will update all the old references to the new location of the data it was pointing to, even if there are $ locks.The exception is query formulas which will update the sheet location but not adjust the search/filter terms to the new location.
--just going to count out the first page, then if anyone wants to post go ahead--
It didn't quite feel like the type of guide I wanted to stick in the general forum resources at this time it can be somewhat of an advanced technique as far as how much time and energy it can take to get a grasp of it, mostly for knowing how to troubleshoot anything going wrong.
