[GUIDE] Spreadsheets for Breeding: a Guide

[Black Waterfall]

Warm greetings everybody! Welcome to Spreadsheets for Breeding: a Guide!
 
For some reason I have been asked by some users for tips on how to create a spreadsheet on Google Sheets, and since many others may have the same interest, I thought that it would be nice to organize all the information I have on the subject and make a thread for future reference. However, note that I am not exactly an expert on Google Sheets, so let us help each other: if you happen to know of any other useful tips or material please don't hesitate to share them with us here!

In this guide we will show you how to create a spreadsheet to track your breedjects step-by-step — more precisely, you will learn how to create a sheet with twenty rows for breedjects (whose details are described in thirteen columns) and in-cell images, in-sheet images, unicode characters, alternating colors, drop-down lists, filters and much more.

Here is a simple preview of our goal:



Disclaimer: All Pokemon described above are fictional breedjects used only for illustrative purposes.

We will be using illustrations such as this one above as much as possible to exemplify each step described in this guide.

Before we start, we should mention that the above spreadsheet is available online in the following link: 

• My Awesome Spreadsheet — You are invited to make a copy of it and try it yourself. You can do this accessing the link above and choosing the 'Make copy' option under the 'File' tab (apparently using 'Download as' might break some of it formulas).
  

I encourage you to play with this spreadsheet while you are reading the steps described in this guide.

1. Creating a spreadsheet

In the following we will assume that you already has a Google account (you can create an account here)

Creating a spreadsheet in Google Sheets is very easy. From the Google Sheets home under the 'Start a new spreadsheet' tab, click in 'Blank' to create a new spreadsheet from scratch.



Now that you have created a blank spreadsheet we can proceed to the next step.
 
2. Detail your Pokemon

It is now time to insert all the relevant information you want to include for your Pokemon, in our case: species, icon, gender, ball, level, nature, ability, IVs, move slot 1, move slot 2, move slot 3, move slot 4 and OT. Note that there is absolutely no general rule here since this is the part where you should custumize your spreadsheet. That said, people prefer to organize Pokemon details horizontally to allow their mons to be listed in a vertical way. 

Following this idea, we want to add a column for each one of the above mentioned names. This can be done by clicking on a cell and pressing enter to type a value. For example, assuming that we want to digit those values in the first row of the table, we would have something like this:



As a matter of fact, you can do the same thing to enter your all your own Pokemon data as well if you want to, but let's just focus on the 'Species' entry at the moment. To give a illustration, suppose you want to list: Abra, Absol, Aerodactyl, Aipom, Alomomola, Amaura, Anorith, Archen, Aron, Audino, Axew, Azurill, Bagon, Baltoy, Barboach, Basculin, Beldum, Bellsprout, Bergmite, Bidoof — as shown below:



3. Inserting in-cell images

Now we would like to display the icons of those Pokemon using in-cell images. To insert an image in a cell you just need to click on the desired cell, press Enter or F2 to enter the formula mode, and then type the command `=image("URL",mode)` (without the outermost brackets) where 'URL' is the web address of that image you want to add and 'mode' specifies what to do if the image's size do not fill in the cell. 

There are four modes named from 1 to 4 (for more tips about the image function see this and this page) 

• 1 resizes the image to fit within the cell and maintain the aspect ratio;
  
• 2 stretches the image or compresses it to fit inside the cell, it ignores the aspect ratio;
  
• 3 leaves the image at original size;
  
• 4 allows you to specify the image’s width and height;
  

We will be using the only the third mode in this guide (resizing often makes images look weird).

A more important question at this point is: how do we find the icons for those mons on the web? I learned from my buddy Tenebris_Kane that you can find literally thousands of icons (for mons, items, balls, body styles, TMs, HMs and more) in msikma's pokesprite Github repository. In sum, this is what we have now:



When an image don't fit in the cell, you can simply adjust the row height and column width of the cell. You can actually do this uniformly for every row and column of your sheet by clicing on the blank cell below the formula bar to select every cell. Now our spreadsheet looks like this:



4. Coloring Alternate Rows

Okay, that was a nice start for our spreadsheet. But it still looks too simple, right? What about having alternating rows formatted with different colors?
This can be easily done in Google Sheets: choose 'Alternating colors' from the 'Format' menu. Select 'Apply to range' enter the desired range.

In Google Sheets every range has the format FIRSTCELL:LASTCELL, where 'FIRSTCELL' and 'LASTCELL' are respectively placeholders for the first and last cell you want to include in the range. In our case we want to cover from Species (Column A) to OT (Column M), and from Abra (Row 2) to Bidoof (Row 21), so we choose the range A1:M21. You can also choose the colors you want to use as well. After all that, we have the following:



5. Unicode characters & Centering

Google Sheets has support for unicode characters, which means that we can add fancy symbols such as '♀' or '♂' to our spreadsheet in the same way we type any other character. You can also center your cells clicking on 'Center' in the toolbar.



6. Drop-down Lists (Part I)

What if we had a drop-down list that allows you to select whether a given Pokemon is female, male or genderless?

Drop-down lists are only visible to those who have edit permissions for the document. There are many ways of creating drop-down lists in Google Sheets and we will be using two of them in this guide. We will begin with the easiest one: Select the cells C2 to C21 and choose 'Data validation' from the 'Data' menu. Select 'List of items' from the dropdown in 'Criteria' and put '♂, ♀, -' in the input box.



Now we have a drop-down list for the gender of each Pokemon from the rows 2 to 21!



7. Inserting images to a sheet

Now we would like to create a nice five-rows header for our spreadsheet. Unfortunately we have no room for it, since we started our list from the row 1 and not row 6. But we can solve this problem easily with a simple cut and paste, cutting the cells from A1:M21 to (say) A6:M26.

Now that we have enough room for it, what about inserting an image to the header of the sheet? There are essentially two ways of inserting images to a spreadsheet in Google Sheets: 

1. In-cell images are locked into a particular cell you choose. We already covered them in Step 3.
   
2. In-sheet images are locked into a particular sheet (page) you choose but you can click and drag those images anywhere in the sheet.
   

To insert an in-sheet image click in 'Image' under the 'Insert' tab and choose how you want to add the image you want. I usually choose to insert images by URL. For example, this is how we insert the Pokémon: Gotta Catch 'Em All logo (available online in this link):



As a side note, this is also how you insert a gif without breaking its animation: you just need to paste the public web address of the gif you want to add.



8. Merging cells

To merge a set of cells you should highlight the cells you want to merge and select the merge type on the main toolbar (you can either merge all selected cells, or merge them vertically / horizontally). Note however, that when you have data in the cells to be merged, only the data in the leftmost cell is preserved.



The picture above shows the result of merging the cells from A1:M1.

7. Adding new sheets

Before introducing a new way of implementing a drop-down list we should say a few words about how to create new pages (sheets)

Many people like having a spreadsheet organized with multiple sheets. This feature is particularly useful if you want to have just a single file to keep track of your breedjects, mons for trade, event mons etc. You can go under the tab 'Insert' and click on 'New sheet' to add a new page to your spreadsheet. 

10. Drop-down Lists (Part II)

That being said, we want to create a drop-down list that contains all (non-neutral) Pokemon natures: 

• Adamant (+Atk, -SpA)
  
• Bold (+Atk, -Def)
  
• Brave (+Atk, -Spe)
  
• Calm (+SpD, -Atk)
  
• Careful (+SpD, -SpA)
  
• Gentle (+SpD, -Def)
  
• Hasty (+Spe, -Def)
  
• Impish (+Def, -SpA)
  
• Jolly (+Spe, -SpA)
  
• Lax (+Def, -SpD)
  
• Lonely (+Atk, -Def)
  
• Mild (+SpA, -Def)
  
• Modest (+SpA, -Atk)
  
• Naive (+Spe, -SpD)
  
• Naughty (+Atk, -SpD)
  
• Quiet (+SpA, -Spe)
  
• Rash (+SpA, -SpD)
  
• Relaxed (+Def, -Spe)
  
• Sassy (+SpD, -Spe)
  
• Timid (+Spe, -Atk)
  

But the problem is that we cannot simply validate the data using the 'list of items' criteria as we did before because Google Sheets automatically separates items whenever it parses a comma: on the other hand, each one of the above identifiers have one occurrence of commas inside them!

So we will be using the 'List from a range' data validation criteria instead. For that we shall create a separate table of natures in our sheet 2 (Data).



After that we go to our sheet 1 (Breedjects), select the cells F7 to F26 and under 'Data validation', we select the criteria 'List from a range' and put the range A2:A21 from our data sheet, i.e. `Data!A1:A21`.



and this is what we have now:



Now we are almost done! There are only two other things that I would like to talk about, the first one being:

11. Filters

Sometimes your spreadsheet has too much data that you have trouble organizing it or you may want to hide some data temporarily. This is when filters may be useful for you because they temporarily remove rows, based on what you need to show at the moment — in other words, you don't need to delete any information! Needless to say, only people with edit permission can see and manipulate filters. 

To begin with, let us add some random data in the nature field for our breedjects. Now we select the cells from A6:M26 and select 'Create new filter view' under the tab 'Filter views' from the toolbar. This should be your output:



Suppose that now we want to remove the rows with male and genderless mons and display only the female ones. In that case you just need to click on the filter arrow from C6. You will see a checklist with all existing values in this column, like this:



and, after clicking on the 'Ok' button, the selected filter will be applied:



To undo the filter you just have to check the values back. Since we still have one last function that I would like to stress, we will undo the above filter (and for the sake of completeness fill all other fields with some random data) 



12. Freezing rows and columns

Freezing a row or column means to fix this row or column allowing it to be visible no matter how a reader scrolls your spreadsheet – a trick that is very handy when you want to emphasize a particular piece of information in the spreadsheet. To freeze a row or column, highlight any cell in the desired row or column and under the 'View' tab click on 'Freeze' and 'Up to current row' or 'Up to current column'. 

Say we want to freeze the cells A1:M6. To do that we simply highlight a cell in row 6 and freeze up to the current row.



Sometimes you will get an error message such as 'sorry you can't freeze rows which contain only part of a merged cell' at this point. This only means that you probably have a merged cells occurring at the exact row (or column) that you are trying to freeze, so there are two ways of solving this problem: you can either find the merged cells and unmerge them or you can freeze more rows (or columns) to include the complete merged cells.

--

I guess that this is it for now! I will be updating this thread with new content depending on the community's feedback. Again, I am not exactly an expert on Google Sheets but please feel free to ask any questions (or PM me) and I will try to help you as I can.
 

 That's all folks! 

[Tenebris_Kane]

@Black Waterfall

Nice Guide! This should help people set up shop here 

I am in the process of rebuilding my spreadsheets but I am looking for a new fancy format instead (I want a searchable / filterable one) so if I find a way I will let you know, we can add it to the guide 

[Black Waterfall]

(Apr 1, 2018, 05:12 AM)Tenebris_Kane Wrote: Nice Guide! This should help people set up shop here 

I am in the process of rebuilding my spreadsheets but I am looking for a new fancy format instead (I want a searchable / filterable one) so if I find a way I will let you know, we can add it to the guide 

@Tenebris_Kane That will be very helpful for the guide! I still don't know much about filters and it would be very nice to learn! 

I was looking for a systematic way of coloring alternate rows in a spreadsheet and I found this page very informative. I will add this to the guide very soon.

[Tenebris_Kane]

@Black Waterfall 

I want to get it so that you can do something like a drop down menu to filter for say a certain type or ball and also have some system that allows users to see all available balls / egg moves etc as a dropdown so there is not a really long list with duplicates, it should help both of our spreadsheets as well as any other shop owners

Update: So far my main issue is that although I think I have got the programming sorted if 1 person uses the filter it means that everyone sees the results they select... Need to find an alternate method that gets around this

[Black Waterfall]

(Apr 1, 2018, 05:45 AM)Tenebris_Kane Wrote: I want to get it so that you can do something like a drop down menu to filter for say a certain type or ball and also have some system that allows users to see all available balls / egg moves etc as a dropdown so there is not a really long list with duplicates, it should help both of our spreadsheets as well as any other shop owners

@Tenebris_Kane I know what you mean, like for example in this spreadsheet here, right?

This auto fill drop down menu looks so nice!

[Tenebris_Kane]

@Black Waterfall 

It says I don't have access to that file unless I am given permission 

[Black Waterfall]

@Tenebris_Kane That is weird... can you access it from here? Or this reddit post?

[Tenebris_Kane]

@Black Waterfall

Yeah, it works from that one. I was working on something similar to that, I need to make it so the dropdowns don't effect everyone somehow or else it is going to probably be annoying if multiple people are browsing

I noticed that that appears to only be a basic inventory and it doesn't look like it can be used much by customers for searching though

The database is quite detailed so I may steal small parts of it and adapt them into the code depending on how it works out


Current Progress Update: Still not figured out the multiple users thing but I have got the basics sorted for a protected list section for where the owner can write stock and a seperate section for viewing Pokémon for customers

[Black Waterfall]

@Tenebris_Kane This protected list section stuff sounds cool, maybe we can add it to the guide later!

Yeah, the dropdowns being globally visible to every user browsing the spreadsheet would definitively be a terrible idea for a shop. Are you sure that this is the case with that reddit spreadsheet? The level of details of its database is really amazing (although as you mentioned, the inventory sheet needs lots of changes if one wants to use it as a breedject spreadsheet).

[Tenebris_Kane]

@Black Waterfall

The spreadsheets hosted on google sheets are always counted as one file with multiple simultaneous users so it would unfortunately be the case. However I am working on a more user friendly version of the sheet which will hopefully be better for our needs.

Until I can find a work-around for the shared display I will need to leave out the search feature but the rest should definitely be useable with drop downs since it shouldn't be a problem with as many users as we will have. 

When I finish the spreadsheet's coding and databases I will upload a copy as public and allow people to download it as a template since other colours and visual style can be edited easily. I will make sure that anyone can get a copy for their own shop and we can add it here along with my instructions for use since people need to know how to add their own stock 

It may take a little while though since I am trying to add a lot into the database so that the spreadsheet does as much work as is possible for the user