This Google Sheets function will help you generate pre-filled Google Form links without nested SUBSTITUE functions.
Watch the video tutorial to learn more.
/**
* Replaces multiple occurrences of specific values in a string with new values.
* @returns {string} The modified string with replacements made.
*
* @customfunction
*/
function MULTI_SUBSTITUTE(text,...opts) {
for (let i = 0; i < opts.length; i += 2) {
const searchValue = opts[i];
const replaceValue = opts[i + 1] || ""; // Default to empty string if no replacement provided
// Create a global regular expression for case-insensitive search (flags 'gi')
const regex = new RegExp(searchValue, 'gi');
// Replace all occurrences of the search value with the replacement value
text = text.replace(regex, replaceValue);
}
return text.replace(/ /g, "+");
}