I’ve been working on google sheets a lot and I thought I should do a fun project. Most uses of Google sheets has always been to calculate, create dashboards or do some form of reporting. I wanted to do a very lighthearted project that didn’t involve any calculations then I came up with an idea to create my own Youtube search result page on Google sheets.
Note: I didn’t use Google apps script to build this. No code was involved in making this.
How It Works
The logic is simple, copy the format of the youtube search url structure then concatenate it with the name of the artist and the song.
I had planned on generating the playlist but youtube video url structure is really weird and difficult to predict. So I could only attempt building the result page.
The sheet has three columns; The name of the artist, the name of the song and then the youtube url
It also automatically updates when an Artist and a Song is added to a new row.
HOW I SOLVED IT
The formula to use and solve this was pretty straightforward ish. I already know how the youtube search result url works so I only needed to concatenate it. The formula was simply
=if(isblank(A2), “”, HYPERLINK(“http://youtube.com/results?search_query="&JOIN("+",regexreplace(A2, “ “, “+”),regexreplace(B2, “ “, “+”)),”Your result for” &” “ &A2&””& “ -”&B2& “ “& “is ready”))HOW THE FORMULA WORKS
- Concatenate: A sample youtube search url is https://youtube.com/results?search_query=beyonce+spirit. So I had to concatenate the first part of the url with the name of the artist and the song with a + sign as the delimiter . To do that I used the join function .
="https://youtube.com/results?search_query="&join("+",A2,B2)The ampersand (&) is used to concatenate the join function with the rest of the string.
2. Dealing with artists or songs that have multiple words: The formula works well now. But what happens when we have an artist like Chris Brown or the Drake song “Nice for What” the url will look like this “https://youtube.com/results?search_query=chris brown+indigo” or “https://youtube.com/results?search_query=drake+nice for what” these whitespaces affects the url structure and although youtube automatically redirects the url to the proper url I wanted to make my sheet get the right url. I thought of various ways to be able to look at the text in a cell, figure out if there is a whitespace then add a + sign that was when I decided to use the regexreplace function. The regexreplace function is a useful google sheets that replaces part of a text string with a different text string using regular expressions. In this case, I wanted it to replace every whitespace with a + sign. I had to update my formula now.
="https://youtube.com/results?search_query="&join("+",regexreplace(a2, " ","+"), regexreplace(b2, " ", "+")I nested the regexreplace formula inside my join function, so instead of just joining a2 and b2, it first checks if there is a whitespace in either cell then adds a + sign before joining the both cells.
Note:If you want to read more about regexreplace read the official documentation here
3. Adding Hyperlink: Now I didn’t want the youtube url to be displayed. Instead I wanted a nice text to appear and then the link to the page. Thankfully, Google sheets has an hyperlink formula. This function takes in two parameters; the url you want hyperlinked and then the text you want to be returned. To make it a bit fun I wanted to add the following text “Your video result for $name_of_artist -$song is ready” . So I updated the formula as follows:
=HYPERLINK(“http://youtube.com/results?search_query="&JOIN("+",regexreplace(A2, “ “, “+”),regexreplace(B2, “ “, “+”)),”Your result for” &” “ &A2&””& “ -”&B2& “ “& “is ready”)I wanted to add a whitespace between “for” and the name of the artist, so I had to concatenate “ “ in between the name of the artist and “for” . I also added whitespace between the name of the song and “is” . So there was a lot of concatenation in the text so as to add whitespace, the artist name , a hyphen and the name of the song.
4. Automatically Updating the cells: Everything works great at this point. We have the proper url , we are also returning the hyperlinked text but there is a problem — The formula has to be dragged down when a new artist and song is added and this feels a bit manual and doesn’t have a nice feel to it. So to automatically update, I decided to use the isblank() formula as well as the if() formula. Isblank checks if a cell has a value in it, in combination with if formula you can check if a cell has a value and if it does you can perform a particular task as well as when it doesn’t . So to automatically update, I checked that if cell A2 is blank then it should return an empty string but if there is a value(the name of the artist) then it should return the hyperlink formula. I did this and dragged it to the very bottom, so when someone writes the name of the artist and song it “automatically” updates it. So our updated and final formula is :
=if(isblank(A2), “”, HYPERLINK(“http://youtube.com/results?search_query="&JOIN("+",regexreplace(A2, “ “, “+”),regexreplace(B2, “ “, “+”)),”Your result for” &” “ &A2&””& “ -”&B2& “ “& “is ready”))This is the end of how I created my youtube search result page on Google sheets.
If you enjoyed reading this please clap and if you have any idea of an exciting project I can build feel free to reach out to me on twitter or email me at victor[dot]nnamdi[dot]okafor[at]gmail[dot]com .
Thanks for reading this far, I appreciate it !