CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formula
Justin
Justin

Reputation: 972

Create Dynamic Parameterised Query URLS in Google Sheets

Challenge:

I have a URL which has various query parameters Example Link

Parameters such as:

I want to be able to dynamically create the links based on criteria I supply in Google Sheets.

For example: if I decide I want to check minBedrooms=4 instead then I can just change a cell in Google sheets and it updates/formulates a new link.

One way I have managed to change a single parameter at the moment is by:

  1. Split the URL by "&"
  2. Using RegexReplace to find the number in the parameter e.g. 2 (minBedrooms=)
  3. Match the text to get the Index of the Split
  4. Use Index to get the full text of the minBedrooms=2
  5. Substitute the minBedrooms=2 for minBedrooms=4

I have added a Google Sheet which shows an example of one way I have managed to achieve this so far but it's far from elegant: https://docs.google.com/spreadsheets/d/1ZGt2vIOcgovSVG_j1ARffyfss-VQLnQrPPa_-2Iwyk4/edit?usp=sharing

I want to be able to have multiple parameters that I can change to create the new link

Upvotes: 0

Views: 839

Answers (1)

WARDEN
WARDEN

Reputation: 111

The middle step with the split parameters is unnecessary. Where you have dynamic URL use the formula:

="https://www.rightmove.co.uk/property-for-sale/find.html?locationIdentifier=POSTCODE%5E4046321&minBedrooms="&B2&"&maxPrice="&B3&"&radius=0.5&sortType=6&propertyTypes=detached%2Csemi-detached%2Cterraced&primaryDisplayPropertyType=houses&mustHave=&dontShow=&furnishTypes=&keywords="

You see how I replaced the values for minBedrooms and maxPrice in the URL? By replacing the number that was there with "&B2&" and "&B3&" your parameters are automatically put in the link, changeable at any time.

Edit: If you want to add more parameters, just add slots for them up top and substitute them in in the correct spots. Let me know if you have any questions.

Upvotes: 1

Related Questions