If you haven’t been following along, I’ve been documenting my journey to a 1,000+ page programmatic site in WordPress using Google sheets.
The build is heavily reliant on Google Sheets to generate dynamic content based on templates.
The content there is a little more advanced, as it is leveraging rules to determine which content template is used.
I thought I’d take it back to basics, and create a dynamic content generator that was a bit simpler.
A straight-up data-to-content text replacement.
Dynamically generating text in Google Sheets
The data set
The generator starts with your data set.
Each column represents a different piece of data.
You wouldn’t need to use every single of pieces of data you put in the spreadsheet, however, you will need to ensure any data point you do want to use is there.
Each of the columns is a variable name, and that will be taken by the generator and replaced out.
The dynamic text template
On the second sheet, is a cell where you write out your text template.
You just write out the text you’d like to include, in the format you want it, and include the variables wherever you’d like them.
Next to the text template is a list of all your variables.
These are just a list of every header name from the first sheet, and it’s a great list to help you remember what you have to work with, rather than needing to flick back and forth between your data set.
Since it’s formula driven, you can’t copy/paste the variables. However, if you’re not adding more columns to the dataset you could paste the raw data so that you could just copy them in.
The content generation formula
You’ll find the actual formula that does the replacement on the main generator sheet.
The formula might look a little daunting, but it’s just a large nested substitute.
Each heading has “<” and “>” added on either side to convert it to something to use in the text templates.
The formula will then take these variables, and substitute them for the value in that column.
It will then repeat the process.
To add a new variable in;
1. Insert a column before the contentOutput and fill in your data.
2. Add an additional SUBSITUTE( at the front of the list
3. Copy the data after the 2nd to last bracket, and paste it after the last bracket
4. Modify the cell references to instead reference your new column
So if you inserted one into the current template, the formula would go from;
=SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(Template!$A$2, “<“&$C$1&”>”,$C2), “<“&$D$1&”>”,$D2) ,”<“&$E$1&”>”,$E2), “<“&$F$1&”>”,$F2), “<“&$G$1&”>”,$G2), “<“&$H$1&”>”,$H2), “<“&$I$1&”>”,$I2)
To become;
=SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( Template!$A$2, “<“&$C$1&”>”, $C2),”<“&$D$1&”>”,$D2), “<“&$E$1&”>”,$E2), “<“&$F$1&”>”,$F2), “<“&$G$1&”>”,$G2), “<“&$H$1&”>”,$H2), “<“&$I$1&”>”,$I2) ,”<“&$J$1&”>”,$J2)
Note the blue for the new modifications.
Since the new column of data was J, we copied the end and then changed the I to a J.
Content template preview
A preview column has also been added to the text template tab.
Once your generation formula is set up with all your variables, you will be able to see a live generation example as you edit your template.
This will allow you to see exactly what the text template looks like when you substitute some of your real data into it.
The example will randomly select from all of your examples every time you update the cell.
A great way to ensure you’re accounting for different outputs in your template, as you craft it.
Access the Google Sheet
You can access the dynamic content generation Google Sheet at the below link.
Let me know if you have any questions!
Feel free to leave a comment, I’d be happy to help out.