In Google Sheets, we can use the ENCODEURL function to encode text for use in the query string of a URL efficiently. This web category function helps ensure that special characters are properly formatted and can be safely included in a web address. Let’s first understand the query string.
A URL consists of several parts, including:
- Protocol: The communication method (e.g.,
https://) - Domain name: The website’s address (e.g.,
example.com) - Path: The specific page or resource (e.g.,
/products/) - Query string: Optional data sent to the server (e.g.,
?q=search+term) - Fragment identifier (or hash): Optional reference to a section within the page (e.g.,
#section1)
In the URL https://infoinspired.com/#search/q=xlookup%20function, we can break down the components as follows:
- Protocol:
https:// - Domain name:
infoinspired.com - Path:
/ - Fragment identifier:
#search/q=xlookup%20function
The query string itself is part of the fragment identifier and consists of q=xlookup%20function.
The encoded text, such as the search term used in the query string, is xlookup%20function. We can use the ENCODEURL function in Google Sheets to dynamically generate this part of the URL within a Google Sheets formula.
Key points:
Query string vs. fragment identifier: The query string is processed by the web server, while the fragment identifier is handled by the browser.
ENCODEURL Function and Character Encoding
Syntax:
ENCODEURL(text)Argument:
text: The text to be encoded (required).
When special characters are present in your query string, encoding them is necessary to safely include them in a URL. The ENCODEURL function handles the character encoding aspect in Google Sheets.
The encoding convention involves representing each special character with a % sign followed by two hexadecimal digits.
Common Encodings:
- Space: %20
- Forward slash (/): %2F
- Ampersand (&): %26
- Equal sign (=): %3D
- Question mark (?): %3F
- Plus sign (+): %2B
- Other special characters have corresponding percent-encoded values.
Examples of ENCODEURL in Google Sheets
We can hardcode the text (part of the query string) within the formula or refer to a cell. Let’s start with a hardcoded example:
=ENCODEURL("North, South, and West") // Returns "North%2C%20South%2C%20and%20West"You can enter this text, i.e., “North, South, and West,” in a cell, for example, in A1, and refer to that using the ENCODEURL formula as follows:
=ENCODEURL(A1)If you want to encode multiple texts, use the ARRAYFORMULA function with the ENCODEURL formula:
=ArrayFormula(ENCODEURL(A1:A3))
How to Use the ENCODEURL Function in Real-Life Scenarios with Google Sheets
Let’s consider a scenario where you want to search this blog for various Google Sheets-related queries. Let’s see how to use Google Sheets to generate the URLs dynamically.
Steps:
- Enter the following URL in cell B2:
https://infoinspired.com/#search/ - Then, enter the search terms one by one in cells C2:C, for example, “xlookup” in C2, “map lambda function” in cell C3, and so on.
- Enter the following formula, where ENCODEURL is part of it, in cell D2 and drag it down.
=$B$2&"q="&ENCODEURL(C2)Formula Breakdown:
$B$2: The reference of the cell containing the base URL. Dollar signs are used to lock both its row and column, ensuring it stays fixed when the formula is copied."q=": A literal string used to specify the “query” parameter in the URL’s query string.ENCODEURL(C2): Encodes the text (search term) in cell C2, replacing special characters with their percent-encoded equivalents for URL compatibility.&(Ampersands): Used to join or concatenate the different parts together into a single text string.
Alternatively, you can use the following array formula in cell D2, which does not require dragging it down.
=ArrayFormula($B$2&"q="&ENCODEURL(C2:C4))
These are hyperlinks. Click on them to open the URL in your browser and search this site. You can use the ENCODEURL function in similar scenarios in Google Sheets.
Remember:
Monitor for any necessary updates to the formula or base URL, as website URLs and search functions might evolve. Adapt the formula to match your specific blog’s query string structure.
Conclusion
From the above examples, you can understand two important points about the use of the ENCODEURL function in Google Sheets.
Remember that ENCODEURL is specifically for encoding text within the query string of URLs, not entire URLs. Please refer to the formula in cell D2 above.
It’s not always necessary to encode pre-formatted, static URLs (commonly used for permanent links, and navigation menus), such as https://infoinspired.com/category/google-docs/.
If you want to see one more real-life use of the ENCODEURL function, please check my guide on pre-filling Google Docs Forms from Google Sheets.
