Determining the proper URL to use in posts is usually easy, until it isn't. Here's one way I handled it when it wasn't.
Introduction
Typing a URL repeatedly is a hassle. Even copying and pasting a URL repeatedly (say, 100 times), only to modify the variable part at the end, is a hassle.
One way to deal with this is to split the URL into 2 parts: a prefix which stays constant, and a stub which is variable.
Setting up the Table
Into one cell goes the URL prefix. It doesn't matter which cell is used, but it should be at a location which makes sense. In this case, the URL prefix https://inleo.io/@magnacarta/re-leothreads- goes into cell B1.
| @ | A | B | C | D |
|---|---|---|---|---|
| 1 | URL Prefix | https://inleo.io/@magnacarta/re-leothreads- | ▓ | ▓ |
| 2 | ▓ | ▓ | ▓ | ▓ |
(▓ = empty cell)
Figure 1: Displaying the URL Prefix
The prefix by itself is useless without the stub. So in Cell A3 goes the table heading "Stub"; in Cell B3 goes the table heading "URL"; and in Cells A4 through A7 go a few stubs:
| @ | A | B | C | D |
|---|---|---|---|---|
| 1 | URL Prefix | https://inleo.io/@magnacarta/re-leothreads- | ▓ | ▓ |
| 2 | ▓ | ▓ | ▓ | ▓ |
| 3 | Stub | URL | ▓ | ▓ |
| 4 | 224xzxuxx | ▓ | ▓ | ▓ |
| 5 | fvsbyhvy | ▓ | ▓ | ▓ |
| 6 | lsrnkn4m | ▓ | ▓ | ▓ |
| 7 | 3ckki7avf | ▓ | ▓ | ▓ |
Figure 2: Add a table showing fields for Stub (filled) and URL (empty).
Simple Formula Mechanics
How do we combine the URL Prefix with the stub to get a unique URL? The quickest way to join 2 pieces of data (to be treated as text) is to use the & character. So in cells B4 through B7 we enter formulas:
| @ | A | B | C | D |
|---|---|---|---|---|
| 1 | URL Prefix | https://inleo.io/@magnacarta/re-leothreads- | ▓ | ▓ |
| 2 | ▓ | ▓ | ▓ | ▓ |
| 3 | Stub | URL | ▓ | ▓ |
| 4 | 224xzxuxx | =$B$1 & A4 | ▓ | ▓ |
| 5 | fvsbyhvy | =$B$1 & A5 | ▓ | ▓ |
| 6 | lsrnkn4m | =$B$1 & A6 | ▓ | ▓ |
| 7 | 3ckki7avf | =$B$1 & A7 | ▓ | ▓ |
Figure 3: Populate the URL field with formulas.
In Excel-style spreadsheets, all formulas begin with =.
The text for the URL Prefix is in cell B1. Since this value has to stay the same in all formula cells, we rewrite B1 as $B$1. B1 is a relative reference, so we turn it into an absolute reference by adding the $ to the column letter and row number making up the cell address.
Once the formula is entered into Cell B4, it is copied down the table column. The stub portion of the formula changes as it gets copied down the table in Column B.
Thanks to the & in the formulas, this is what we see on screen in the table:
| @ | A | B | C | D |
|---|---|---|---|---|
| 1 | URL Prefix | https://inleo.io/@magnacarta/re-leothreads- | ▓ | ▓ |
| 2 | ▓ | ▓ | ▓ | ▓ |
| 3 | Stub | URL | ▓ | ▓ |
| 4 | 5w1v3e | https://inleo.io/@magnacarta/re-leothreads-5w1v3e | ▓ | ▓ |
| 5 | xiqrq | https://inleo.io/@magnacarta/re-leothreads-xiqrq | ▓ | ▓ |
| 6 | 7kpqwv | https://inleo.io/@magnacarta/re-leothreads-7kpqwv | ▓ | ▓ |
| 7 | 7x4ao7 | https://inleo.io/@magnacarta/re-leothreads-7x4ao7 | ▓ | ▓ |
Figure 4: Replace formulas in URL field with resulting URLs for instructional purposes.
The formulas didn't disappear. Wherever the cell cursor is located, the formula will be shown inside the Formula Bar. So if the cell cursor surrounds Cell B7 (https://inleo.io/@magnacarta/re-leothreads-7x4ao7), the Formula Bar will show =$B$1 & A7.
Now when a URL needs to be inserted inside a thread or a piece of text in a post or article, we can just copy the formulated URL from Figure 3 and place it into the content we're working on.
Applications of This Formula
By itself, the final version of the above table is useful. If other data is needed-- such as datetimes and actual zapfic entries-- then formulas can be pieced together to combine these pieces of data. Think of a formula to create a Markdown template which combines fixed pieces of text with variable datettimes, stubs, and zapfic text.
That's It! Or Is it? Not So Fast...
The final version of the above table looks trivial, with just 4 stubs. Once the table contains 20 stubs, the table containing formulated URLs begins to look useful.
| Case Study: GHAWG |
|---|
| As of 2024-April-22, the spreadsheet used to track the zapfic entries for the 1st daily zapfic serial for LeoThreads at InLeo contained 430 entries. Each entry has its own entry number, datetime published, stub, and zapfic text. These pieces of data can be combined in different ways to produce strings of Markdown code. Regardless of which formula is used to generate the Markdown code, the formula itself is simply alternating pieces of fixed and variable data connected by the & mentioned near the start of the post.Where the variable data is used, it's either a value from a table (such as "stub") or it's a calculated value resulting from some function or formula (for example, the choice of URL Prefix to use based on datetime of "stub"). |
What I just described is all I should have had to do since 2023-March-05.
So What Happened?
For reasons I won't get into, things became a bit more complicated on 2023-June-02 when the URL prefix changed for me:
| Date Range | URL Prefix |
|---|---|
| Through 2023-June-01 | https://inleo.io/@magnacarta/re-leothreads- |
| Since 2023-June-02 | https://inleo.io/@magnacarta/re-magnacarta- |
Figure 5: Showing the URLs to use depending on date of zapfic entry
Even here, I could still use the above formulas as long as the table was arranged neatly. Just use the 1st URL prefix for older dates and the 2nd prefix for later dates. Find a cell to store the 2nd URL prefix, then modify the table formulas by replacing $B$1 with the cell containing the 2nd URL prefix.
However, should another such change happen, and then I have 3 URL prefixes to deal with, then it become a real problem to determine the proper URL prefix. Yes, it can be done; no, it wouldn't be simple any more.
Other considerations
1. Life Happens
We need to adjust what we do when life happens. At the time I began GHAWG, LeoThreads was the microblogging platform for LeoFinance. Later in 2023, LeoFinance was rebranded as InLeo, and this included a switch from the leofinance.io domain to the inleo.io domain.
2. Out of Sequence Entries
Although most zapfic entries were published in sequential order, some were added later to fill in gaps earlier in the serial. This includes gaps before the date 2023-June-02.
3. Re-sequencing the Entries Later
When the zapfic serial reaches its conclusion, the zapfic entires written out of sequence need to find their proper locations. Once in place, their datetimes will be used to determine which URL prefix gets used.
One Way To Handle a More Complex URL Prefix Situation
Although the URL Prefix is the basis for the formula prefix & stub, decision making enters the picture to determine which URL Prefix gets used.
Any number of ways to handle this decision making can be used, but most will involve using an IF() function-- IF(condition_to_test, return_if_true, return_if_false). Even a simple IF() function makes the formula bit hard to read.
Perhaps the easiest approach is this one:
- Redefine the URL prefix to use only the part which doesn't change;
- Then add the variable part of the earlier URL Prefix;
- Then add the stub.
Going back to Figure 5, this means the URL prefix is shortened to https://inleo.io/@magnacarta/re-. That means that the formulas in the URL field go from the simple prefix & stub format to the more complex prefix & IF() & "-" & stub format.
Even though the 2nd "-" doesn't change, to make the IF() functions more readable I shifted it into the return_if_true and return_if_false parts of the IF(). So the final IF() follows this format: prefix & IF() & stub. Figure 6 below shows a more complex table:
| @ | A | B | C |
|---|---|---|---|
| 1 | URL Prefix | https://inleo.io/@magnacarta/re- | ▓ |
| 2 | ▓ | ▓ | ▓ |
| 3 | Stub | URL | Date |
| 4 | 224xzxuxx | =$B$1 & IF(C4>DATE(2023,06,01),"magnacarta-","leothreads-") & A4 | 2024-04-08 00:08:36 |
| 5 | fvsbyhvy | =$B$1 & IF(C5>DATE(2023,06,01),"magnacarta-","leothreads-") & A5 | 2024-04-08 00:10:42 |
| 6 | lsrnkn4m | =$B$1 & IF(C6>DATE(2023,06,01),"magnacarta-","leothreads-") & A6 | 2024-04-09 00:00:33 |
| 7 | 3ckki7avf | =$B$1 & IF(C7>DATE(2023,06,01),"magnacarta-","leothreads-") & A7 | 2024-04-10 00:03:51 |
| 8 | xiqrq | =$B$1 & IF(C8>DATE(2023,06,01),"magnacarta-","leothreads-") & A8 | 2023-03-06 02:40:24 |
Figure 6: Determine proper URL to use based on value in "Date" field.
Since most stubs are associated with zapfic entries made after 2023-June-01, the condition_to_test part of the IF() tests for newer dates. If the date is newer, it will return magnacarta- immediately. Only if the date is older-- as in Cell C8-- will the IF() return leothreads-.
Advanced Text Processing-- Not Needed This Time, But Available
Although in this case I didn't need to go that far, I could have handled my problem by using the built-in worksheet functions to create a formula for some advanced text processing.
Among these worksheet functions are REPLACE(), LEN(), and FIND(). The resulting formula using these functions would have taken the original URL prefix from Figure 1 to manipulate it before returning the "proper" URL prefix.
Next time I return to this topic, I'll explain in more detail how each text function works and how it relates to the IF() function needed to return the proper URL.
Thanks for taking time to read this post. If you have feedback or comments, please leave a reply. If you found the post useful, please give it an upvote, a reblog, or both. Tipping tokens are welcome as well.
This is
[!!]See you next time for another edition of GHAWG Behind the Scenes!
[!!] -- Graphic signature was designed by @ahmadmanga