Categories
How To

How I Made It: Stream Search 1.0

In my efforts to do more with spreadsheets, I decided that having a way for fans of streamers to be able to find what games they played, what was streamed on a particular day, or who was a guest to be pretty useful.

So I made a spreadsheet where you can now search and find when and what stream ProtonJon played a game, or find out who ProtonJon had as a guest or find out what streams that guest has been on.

https://docs.google.com/spreadsheets/d/1ok8ONlceu-AqMik1cBCr1Vn-PPyCSTFAbe-E2Nhx6U4/edit?usp=sharing

If you want to learn a bit about google sheets or have a desire to create your own Stream Search spreadsheet, you’ll find out how below.

Archive Your Work

What you’ll need first and foremost is to record your streams into a database or spreadsheet. Here you’ll need to decide what information you want record like the date, the games you streamed, or guests.

If you haven’t recorded your streaming escapades yourself in a database or spreadsheet you can export some data from analytics websites like SullyGnome. Note that websites like SullyGnome don’t always record everything. But it can save you time from a lot of manual data entry.

When you have recorded all the information about your streams, you will want to make two google sheets. One to hold the raw data, and a second for viewers to use to search through the raw data. The reason you will need two google sheets is you’ll want to keep the raw data on the first google sheet protected as we’ll be giving people the ability to edit the search sheet.

So our first goal is to bring the raw data into the search sheet.

Bringing the Data for Search

Now that you have your dataset of your streams in the first google sheet, you will need to import the data to the search sheet. To do this, you’ll need to use the IMPORTRANGE function.

Simply put, the IMPORTRANGE function will duplicate a range of cells from an external spreadsheet using a URL. The two arguments IMPORTRANGE requires is the URL which will be the link of the spreadsheet that has your raw data and range of cells that contains the dataset of the raw data.

To make the second argument easier to put into the IMPORTRANGE function and make it so you will only need to update the raw data spreadsheet, you can use a named range.

A named range allows you to set a range of cells to be identified by a name instead of the cell address. For example, instead of having a range be A1:C200, you can call the range “GameDataTable”. Since named ranged can’t have spaces, you can use an underscore to connect two words as an option.

Both the URL and named range of the IMPORTRANGE function references the raw data spreadsheet.

To import the entries from the raw data spreadsheet, you’ll be copying the URL of the spreadsheet and then enter the named range surrounded by quotas as seen in the picture above. The result will be a duplicate of the raw data from the raw data spreadsheet. With the data imported the next step is to make the search function using the filter function and data validation.

Search List using Data Validation

From here on out you’ll only need to work with the second google sheet that will function as the search page for games, dates, etc.

To make it so users can select the game from all the games that was streamed, you’re going to need to make a named ranged consisting of all the games from the worksheet that has the copy of the raw data set.

For example, I’ve titled the named range as “StreamGame” and have selected all the game titles and have excluded the header.

Remember that since the data is using the IMPORTRANGE function, the data will dynamically update as you update the raw data spreadsheet. So I would recommend to expand the range to go past the final row of data to the bottom row of the worksheet. We’ll be taking advantage of this when we use the FILTER function.

With your named range created, you can now create a new worksheet which we will make a selectable list using data validation. This list will serve as the search bar.

Click on the Data tab in the toolbar and select Data Validation. For the Cell Range, select a cell (or merge cells) in your worksheet that you want to operate as the search bar. For the Criteria, select from the drop-down list “List from a range” and for the range you will want to type in the named range that you created. In our example, the named range is “StreamGame”. Finally, leave the checkbox “Show dropdown list in cell” checked and select Save.

The result will be a cell that will now have every unique game or date that is in the named range. You can also type in the cell and it will provide matches that exist from the named range.

With the search bar created, to get a list that pulls the data and creates a table that matches the search criteria we’ll need to use the FILTER function.

Making Search Results using FILTER

In order to create search results in google sheets from the search bar, you are going to need to make one more named range consisting of the data that you want to show as the results.

Going back to the worksheet that contains the dataset and for our example, you will make one more named range that will consist of dataset excluding the range that you used for the named range “StreamGame”. Below you will see that I named this range “GameTable2” but any name will work.

I would recommend as well to make the end range match that of the named range you created for your search bar. The reason is that the FILTER function requires that the range of cells must match in length. So in our example having both named ranges “StreamGame” and “GameTable2” end at row 4114 will allow the spreadsheet to be dynamically updated whenever new entries are inputted in the raw data spreadsheet. So you’ll only have to input new entries once.

Now that you have the named ranges, you can now make the filter function. Going back to the search bar worksheet, select a cell where you want the results to be displayed. In my example, I put the FILTER function below the search bar and put headers as to label the results.

The FILTER function needs two arguments, the first argument is the range of data that will be displayed as the results of the search. The second argument is the condition that will be used to filter and display the data that matches.

So in the cell you want to put the FILTER function in, you will have a function that will look like this.

=FILTER(GameTable2, StreamGame = $B$7)

The results of the function will now generate a table from your dataset based on the name of the game in the data validation list.

The cell address in your FILTER function will vary based on the cell address of where you put the search bar.

Now that the functions and formulas are in place, the last thing to do is to protect the sheet so that the only part of the spreadsheet they can use is the search bar.

For Search Only

The one problem I had to solve was how to let people edit the search bar but not be able to edit or worse delete the spreadsheet. Setting the share permission to view only would prevent people from using the spreadsheet at all. However, there is a feature that allowed me to set the share permission to edit but only allow users to edit the search bar.

Using the Protect Sheet and Ranges feature in the data tab allowed me protect each worksheet in the Stream Search but also create a rule where only the search bar could be editable.

So I protected each worksheet so that only I could make edits. With the worksheets that has the search bar, I used the Except Certain Cells checkbox and selected the search bar cell address as the exception.

The cell address B7:D7 is the location of the search bar.

The result is that even though all the worksheets can only be edited by me, the search bar itself can be used when the Share permission is set to “Anyone with link can edit”.

Only thing left to do was some designing to have some form to go along with the function.

So, there you go. If you ever wanted to make your own Search box in Excel and have it create tables of data from the search box you now know what to do.

This can be handy to help your fans quickly find past streams based on the date, or game that they want to watch. With named ranges you only need to update the raw data spreadsheet and the Stream Search worksheet will import any new entries that you enter.