How to Dynamically Generate URLs on the Fly in Excel or Google Sheets

How to Dynamically Generate URLs on the Fly in Excel or Google Sheets



okay today I'm going to talk about how to dynamically create URLs on the fly using formulas so I'm gonna demonstrate how to do this in Google sheets and then I'm gonna show you how to do it in Excel there's just one extra step in Excel so it's just a little easier in Google sheets and then it's also a little easier to share up this practice file with you so I like to use this approach to dynamically create URLs to access in reports I've used this with SEM Wragge Google Analytics lets sumo lots of tools like that where a link to a report is made up of individual components and those individual components we can use to our advantage so I'm going to start with a simpler example using SEM rush which is one of my favorite tools and then we're gonna get a little more complicated using Google Analytics so the first thing I'm gonna do here is I have a list of keywords and if you're not familiar with SEM rush you can do a search for a keyword or you can do in fact I'll just start with SEM rush here so you can search for a domain a keyword or a particular URL so a landing page on your site and if I search here for funky shoes it's gonna take me to this report that gives me lots of information about sites that are competing for funky shoes all kinds of things so let's say that I have a list of keywords relevant to my particular site and I want to make it easy for someone where if they add more keywords to this list or change them or whatever this link to the SEM rush report will update dynamically so that's what we're gonna do here so the first thing we're going to do is look at this URL so if we go in here I'm just gonna grab this URL and bring it here and I'm just gonna drop it here to kind of deconstruct I'll double click here to Auto expand the width of the column so what you can see here is you have SEM rush com forward slash info and then you have the keyword separated by a plus sign and then this question mark DB equals us because I'm in the United States it's using the US database but I could change that in SEM rush if I wanted to search the UK or Germany France or any of these other countries I could also choose Bing if I didn't want to search Google so I'm gonna go ahead with that and what we're going to do here is we're going to say okay well the first part of the URL is going to be this part here up to info so I'm gonna go in here and I'm going to first gonna take this here and I'm just gonna drop this down here and then I'm going to take off this funky shoes and I'm just gonna say here that's just gonna be a reminder for me a keyword separated by then here what we're going to do is we're going to start to pull together this URL but the first thing we want to do is add the plus sign in between each of the words so I'm going to come over here and we're going to use the substitute function so I'm just gonna start to type it and then as soon as it comes up I'll hit the top key and the first parameter is asking what text do we want to search that's this cell here and then what do we want to search for we want to search for a space and then what do we want to replace it with we want to replace it with a plus sign so any place where we see a space we're going to replace it with a plus sign now if you have spaces after your keywords then you can wrap all of this in a trim function and that will trim off those spaces at the and now I'm just going to double click to send that down okay and I call this a helper column because it's going to make it easier to create this formula over here if I'm referencing a helper column I could do this all in one step but it's really easy to get tangled up in your formula if you do that okay so now what we need to do is pull together at the raw link so what we're going to do here is because we have text in a Cell we could either put the text inside the formula but then you have to use quotation marks or we can reference a cell if we reference the cell then you don't need to use quotation marks now there is one thing that is hugely annoying with google spreadsheets on the Mac and that is I for the life of me cannot find a keyboard shortcut to lock down a cell reference so this first part of the URL is in g4 so I actually have to type in the dollar signs so this is just saying I want to reference g4 and I want to lock it vertically and horizontally so it's only referencing that one cell and then we're going to use the ampersand character to concatenate so first we need that then we need our key words separated by a plus sign so we're going to reference that here and then finally we need this last part here which is g6 so now what we can do is test this and see if it works it's another thing I like about using Google sheets is it's very easy to test and you can see that it opened up the report for cool shoots which is exactly what we want so once we know that that's working then we can double click to send it down but now if you're working in a report these can be really cumbersome so typically what I'll do is I'll simplify this using the hyperlink function and I'll just reference the URL and then the texts that I want to use so I might say it's C report and now if I double click to send that down this is a lot easier to work with in a report then these long URLs in my opinion and then what I'll typically do is hide these columns here you can do that by selecting them and choosing hi columns now one thing I want to show you before we do that is if this first column here could be extended so let's say someone may add keywords so they might take some away then you may want to wrap this formula here in an if function that basically says if this cell here is blank then make this cell blank if not then use this formula and that would look like this so if the cell here equals blank then insert a blank character otherwise run this formula now in order to do this we're also going to have to extend these formulas here so we would need to take these and extend these as far as we think that they'll go now normally I would go ahead and also insert if functions in here because this is going to populate with the first part of the SEM ruch link but since we're going to hide these helper columns it won't really matter so now if I come in here and I extend this all the way down then these are going to be blank but if I add something new you'll see that it opens up the report for something new and shockingly not too many people search for that ok so then we would just go in here and hide these now if you wanted to get fancy here and impress your boss or client or whomever then you could go in and take this form here and insert it into this formula then that would just give you one longer formula and the way I recommend doing that is wherever you see c4 referenced just go in here scoop this up we'll just command C ctrl C on a PC go into this off here get rid of the equal sign I shouldn't have grabbed that and just drop in that formula and now you'll see even if I delete this this URL works fine because now we've embedded that substitute function so even if you ultimately do that it's really good to have these helper columns especially if you're kind of new to excel or formulas okay so now that we have that behind us we're gonna get a whole lot fancier and dynamically generate Google Analytics reports so you can use any number of reports in Google Analytics for this in fact arguably you could use any report in Google Analytics except for a custom report whoever is going to have access to this sheet here is going to need access to that custom report I'm going to demonstrate with the landing pages report because I really really like this report plus it has a little nuance to it that will be good to address so the landing page report is one of my favorites because it just tells you the URL that people are landing on and then what they're doing if they're spending money when they land on that page or what so it's one of my favorites but now what we're going to do is we're going to click on one of these URLs because we want to dynamically generate a link to the individual landing page report based on that URL here and so we're going to click through to the landing page report for a specific URL so we can kind of deconstruct this URL now this URL is very very long so typically what I'll do is copy it and I'll bring it into word and drop it and I don't like to work with a live URL because it's a little harder anytime you click on it it wants to go through to the actual URL so what we want to do here is we want to look for the variable factors and we actually have more than just the URL because in Google Analytics you also choose the date range so you can see here that I've allowed for that we're actually going to create a start date and end date and you'll see how all of this works together so the first thing we're gonna do is look for the URL so I pulled up the URL for my landing page fine text within text Excel if I look really closely you can see here that starts with blog so this is going to be the URL and this percent – these are the forward slashes so this is the part that's variable that's my URL then we don't want to include the percent 3a and this forward slash it took me a little while to realize this but this is extra so we're gonna have to account for this separately because this % 2f is also a forward slash so Google Analytics just adds an extra forward slash at the end of the URL so this is variable so I'm gonna go ahead and highlight this in yellow and now let's go ahead and look for the date so you can see in my report I have September 1st 2015 to September 30th and you can see here here is the September 1st and here's the September 30th so all of these elements here these are all static anything that's not highlighted and anything that's highlighted these are going to be your variable elements so what we're going to do is take these individual elements and drop them in here in what I call the URL stitching so we're gonna drop this in here that's going to be the first part then we're going to the start date so I'm actually from right now I'm just gonna type in start date and what I'm doing is I'm putting these in order so that way when I start concatenating them it's really easy all I have to do is just go down an order and I make sure I get everything so next we're going to have this little snippet here so I'm going to copy and paste that here and then I'm going to have the end date so there's the end date then we have another little snippet here I'll drop that in here and then we're going to have the landing page so I'll just type that in here this forward slash equals percent to F these are just little notes that help me keep everything straight because this can get super complicated super fast so and then finally we're going to need that final forward slash okay so now that we have the individual components let's start to build our start date and end date so in Google sheets you actually have a data validation option if you go to data and then validation for date which gives you a date picker and I like to actually put in a little help message double-click to pick T now you'll notice when we double click on that cell I get a date picker and then I'm just gonna copy and paste that in here and you'll see the little help message here at double-click 250 I still include instructions usually in here you know or above it double click to pick the date because it's not terribly obvious or you can shade the cell and then have a legend you know like maybe I could put like a light yellow shading on this and then a light yellow shading I sell above it urging people to pick a start and end date so once we have this then we can go ahead and pick September 1st and September 30th away we could pick any date we want but we'll just go ahead and replicate that landing page report that I have up but notice here when we go back to here this is the format of that particular date so we can't have the date looking like this so what we're going to use first of all here is a text function so what the text function allows you to do is select the number that or in this case a date that you want to convert to text and then tell Google sheets how do you want it formatted so we're going to select this cell here I don't have to lock it down because I'm not going to be clicking and dragging it and then the format that we need is year year year year month month day day so here we're going to choose your year your year year year we need lowercase wise to uppercase m's for month and then lowercase D for day that's specific to Google sheets oops I made a mistake we have to wrap this in quotation marks it's there we go and now we're going to do the same thing for the end date so just to get more practice and because I did it wrong the first time go in here yyy mm TD okay now it's going to be in the format that we need so the next thing we need to do and I created lots of helper columns in this case we need to fix the landing page so you'll notice that in Google Analytics by default it only includes the URI it doesn't have the full URL but typically when you're dropping URLs into a spreadsheet you actually have the full URL that starts with HTTP or HTTPS and so what we need to do is just pull this part out here now in my case it's pretty simple because I only have one subdomain however I'm going to show you how to do this if you have multiple subdomains so the first thing we're going to do here is we need to use the right function so the right function allows us to scoop out the very end of some kind of string so we're going to put in an equal sign I'm gonna start to type in right and it's asking us what the string is I'm just gonna select l4 and the number of characters so to get the number of characters we have to use a combination of two functions here one is the length function which just tells you the total number of characters represented in this URL minus the part of the URL that includes the protocol and the subdomain so HTTP colon forward slash four slash W analytics comm or if you had multiple subdomains whatever your subdomain is so we're going to figure out the length of this entire cell and subtract just this part here and that's going to leave us with the URI so let's start with figuring out the length and we want the length of this cell here and of course we don't want to lock these down because when we drag these down we want this to update I could lock down the L but I'm not going to be dragging these across so it's not really essential so we want the length of this – however many characters are in this now if you have separate subdomains this is going to be a variable number of characters in my case it's the same number of character so I could actually just type in however many characters that is but I'll show you how to do it in the event that your since variable so this is where we're going to use the search function because what the search function does is you tell Excel or Google sheets this is the character that I want to search for and we want this forward slash here because there's always going to be the third forward slash we want to know the position of that character there and then that's essentially what's going to be scooped out but then we're gonna have to add in one more character because we actually want that forward slash but that's gonna be a lot easier than identifying the M in analytics com so now we're gonna type in search and Google sheets is asking us what do we want to search for well we want to search for a forward slash and then where do we want to search for this well we want to search for it in l4 so I'm just going to use my left arrow key and where do we want to start so this is in brackets as you can see here because it's optional but in our case we need it because if we don't use it it's gonna start at this first forward slash here but we actually need the third forward slash so I always put in nine when I'm working with URLs and that's because if we count the characters here the HTTP is four characters and the colon and a forward slashes are three characters but if you have secure URLs which I do have some secure URLs there's going to be an S in there so we want to start after that lost forward slash so I mean we have lots of characters to play with here I could put in 10 I could safely put in 15 for most web sites but I just always put in 9 because I know that's gonna start at this the first or the second w depending on if I have an S in the HTTP so I'm just gonna put in 9 and then if we just use this you'll notice that we get it almost right except we need one more character we need that forward slash there and so we're gonna go in here and we're just going to add plus 1 but there are lots of times where I haven't been able to figure out if I need to add a character or take away a character so I'll just play with us until I get it right but I know in this case what I want to do is I want to say take this whole URL and scoop out this here but then add in one more character because I mean that /soe we'll see if that works and it does so then we would just double click to send it down and now we need to substitute these forward slashes with % 2f so we're going to do that using the substitute function again and the text to search is this here I could have used my left arrow just as well and we want to search for a forward slash and we want to replace it with % 2f and double click to send it down now we can start building the raw link so this is just going to be a series of concatenation so here you can see I want to start with this and then just work my way down so I'm going to go back select this out here f4 to lock it down and then choose k5 f4 to lock that down and in fact I think it'll be easier at least faster just to type them in in k-6 k7 ok 8 and then we need the landing page so we're going to get that by selecting this cell here and this one here and then I know that that last saw is k10 whoops I have two of these and let's click on this to see if it works and it is so now we'll double click to send that down just to be extra sure I click on another one to make sure that it works as well and it is and now we'll do the same thing we did before using the hyperlink function which also works in Excel I'm just going to select this and and then double click to send that down and let's select one of these to see if it works yeah we'll just click whatever and it did work so perfect and finally let's test our date picker to make sure that that works properly so we'll go back here and in fact go ahead and hide some of these columns basically all of our helper columns for your final product you don't want those showing but you don't want to delete them or everything's gonna break we'll also hide this one ok now let's go here let's select June 1st to September 30th in fact let's update this and now let's click on that last one that we had selected and June 1st to October 2nd so everything is copasetic and there you go don't mean you can use this for really any tool that generates a report URL now if you want to do this you in Excel one small difference is you over here in the date picker you don't have a date picker in Excel there are ways around it there's actually a form control option on a PC for a date picker that you don't have on the Mac but I'll link to a post on how to use that on the Mac I think there's an add-in that you can use to get a date picker but in the absence of a date picker someone could just enter the date you just have to make sure that this is formatted as a date so that they know how to format it so that if they put in a date it will go ahead and format it the way you want it whether it's it looks like it's using this format right now as long as this text function here updates correctly you're fine all right so that's all there is to creating dynamic URLs in Excel or Google sheets

2 thoughts on “How to Dynamically Generate URLs on the Fly in Excel or Google Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *