If you are designing a table in html there is a lot of tedious repetitive typing to do. A spreadsheet can be used to take a lot of the tedium out of the process. Previoulsy I have used spreadsheets to generate repetitive text and html in what I think was a relatively efficient way. I also thought that it was a very quirky method unlikley to appeal to other people. Until, that was, I read this blog post on the Zapier blog. Then I realised perhaps my quirky method might be of some interest to the mark up community.
So here is my contribution, it builds on Matthew’s post on the Zapier blog by showing how I would use Google Sheets to build a html table. The method is different to Matthew’s as it avoids the very long concatenate formula and makes it easier to make quick edits to the table formatting. It is more complex to set up, but much easier to work with once it’s done.
What follows are the steps to make the key parts of a html generating spreadsheet from scratch, but I have published a spreadsheet here which is based on the table that Matthew shared in his blog post. You can download and tweak it for your own use.
Step 1. Create a spreadsheet
Create the spreadsheet with two sheets: “Templates & Output” and “Row Details”
Step 2. Set up your templates in the “Templates & Output” sheet
In one cell set up a template of the html for the whole table, using a text placeholder (e.g. [rows]) for where you want the rows to go. I put my placeholders in square brackets but you can customise this to your own preferred style, just make sure they won’t be mistaken for html.
In another cell set up a template for the table rows, using text placeholders (e.g. [Name], [Description], [Link] etc) for the specific details of each row.
Step 3. Make a table for the unique details in the Row Details Sheet
Make a table with a header row, the header items are the placeholders in the templates. Each row of the table here will provide the unique details for each row in the html table. So if you were producing a table with details about a range of apps, each row will include the details of one app.
Step 4. Write a formula to replace the placeholders in the row template
In a column to the right of all the details write a formula using SUBSTITUTE(). This formula requires three arguments SUBSTITUTE(arg1,arg2,arg3).
* For argument one point to the template for table rows, this will form the base text of SUBSTITUTE(). This reference should be absolute because the layout of all rows is based on the same template (“A1” is a relative cell reference and “$A$1” is an absolute cell reference).
* Then use the formula to replace a placeholder with the detail from the table. Argument two should point to the placeholder text, i.e. the header of the table column. This argument should use an absolute row reference (“B$1” for example) because the placeholder text is a constant for all rows in the table.
* Argument three points to the detail in the table that the placeholder should be replaced with (e.g. “B2”).
Nest multiple SUBSTITUTE() formulae for as many placeholders as you have. If you had five unique details to place into your template for each row then you would need five nested SUBSTITUTE() formulae. E.g.
Where the absolute row references point to the headers of the table. Fill that formula down the length of the table.
Step 5. Concatenate the html for all rows
In one cell, can be anywhere I put mine in the Templates & Output sheet, concatenate all the html that your spreadsheet has generated for the rows. You can manually write a CONCATENATE() formula, or use ampersands “&”, or my favourite is to use JOIN() with an OFFSET() formula. The beauty of JOIN() is twofold:
1. I can easily concatenate all the html with a line break as a delimiter which gives me a tidier html output. You can certainly add a line break with CONCATENATE() but it requires a lot more work. NB Add a line break in a formula by typing CRTL+Return.
2. I can pass in a dynamically resizing array of items with OFFSET(). In my spreadsheet the OFFSET() formula only picks up the number of html items that correspond to a table row that I have actually filled in. If I used CONCATENATE() I would have to go into and edit the formula everytime I wanted to add or subtract rows from my table. But with JOIN() I can add five new rows to my Row Detail table and the html creator will automatically accomodate those extra additions – it is beautiful!
I know I haven’t fully explained how to use JOIN() or OFFSET() here – I didn’t want to add a lot more information than necessary. If you check out my shared spreadsheet hopefully you will be able to work out how I did it. If not and you really want to know more then add a comment to this post and I will add an update with an explanation of my method.
Step 6. Compile the html for the table
In a cell in the “Templates and Output” sheet use SUBSTITUTE() again to replace the [rows] placeholder with the concatenated html for the rows. That cell then provides the html for your table. Copy and paste it into your website / blog.
Setting everything up was a lengthy process but now your hard work will pay off. Should you need to tweak the formatting of the html table it is now really, really easy and fast to do. All you have to do is update the relevant code in your template. For example, if you wanted to make all of the columns a little wider you can simply change the width value in the row template and your html output will automatically update – no need to adjust a concatenate function and re-fill. And when you come to design your next html table you will already have a spreadsheet ready, needing only a few minor tweaks before it can be used.
Below you can see the output for one of my tables. The full table can be seen live on my website here. I hope that you found this method useful. If you need further clarification on setting this up let me know in the comments.
- With some logic formulae, such as IF(B2=“”,“”,SUBSTITUTE(…)), you can automatically hide irrelevant data from the concatenation stages. A formula like the one above checks to see if you entered anything in cell B2, if you didn’t it returns a blank cell, if you did it will return the output from the SUBSTITUTE() formula. ↩
- HTML table ouput.