Formula to interpolate values into a template string containing placeholders

The OP asked for string interpolation similar to Python f-string. The goal is that a user only needs to enter a single string to define a template, but within that string there is specially marked "placeholder" to indicate where to substitute a value. The placeholder can be a key enclosed in curly braces, e.g. {TICKER}. Google Sheets didn't have such a formula.

Fast forward to 2025, Sheets still doesn't have it, but does offer array-based formulas and iteration mechanism like REDUCE. Combined with REGEXREPLACE, we can define a Named Formula that simulates simple interpolation.

Formula. The formula below takes three parameters:

• template: a string that contains placeholders enclosed in curly braces
• keys: a string to define the placeholder key (or single-row array if multiple placeholders)
• values: a value corresponding to the key (or single-row array if multiple placeholders)

Create a Named Function TEMPLATE with these three parameters, so named and in that order, then enter the formula definition:

= REDUCE( template, keys, LAMBDA( acc, key,
  LET(
    placeholder, CONCATENATE( "\{", key, "\}" ),
    value, XLOOKUP( key, keys, values ),
    REGEXREPLACE( acc, placeholder, TO_TEXT(value) ) 
  )
 ) )

Example with one placeholder. Your example uses a custom Apps Script function called ImportJSON, but your question is more about the string interpolation, so I will just focus on how to generate the URL based on the value of A2 (the cell containing string "BTC"). In a cell enter:

= TEMPLATE(
    "https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids={TICKER}",
    "TICKER", A2
)

The result should be https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=BTC

Example with multiple placeholders. You can give arrays as the keys and values arguments for multiple placeholders:

= TEMPLATE( 
  "My name is {name} and I am {age} years old.",
  { "name", "age" }, { "Lan", 67 }
)

Confirm the result is: My name is Lan and I am 67 years old.

How it works? The REDUCE formula takes the string template as an initial value, and iterates through the values of keys. In each iteration:

• It constructs a regular expression to match the placeholder named by the key, e.g. "\{name\}".
• It looks up the value corresponding to the key, e.g. "Lan".
• It uses REGEXREPLACE on the template to match all instances of the placeholder and replace each one with the value.
• It passes the resulting string, which may contain other placeholders (e.g. "My name is Lan and I am {age} years old.") to the next iteration.

Named Function. If you just want to use this, you can import the Named Function TEMPLATE from my spreadsheet functions. See the documentation at this GitHub repo for more details.