My organization has a platform that allows users to collect spatial data. We have a forest plot survey that allows users to collect information about forest characteristics. The form is intense, with many nested layers, many-to-one relationships, and repeating sections.

I have developed a workbench that reads the JSON from our organization website and ‘unpacks’ it into it’s individual tables, all with id’s to perform the necessary joins down the road. Previously I was just writing all the tables to their own sheet in an excel document, but now my managers are asking me to populate the data into their preferred format, so they have given me an excel table to show what they want the output to look like. It’s not a data friendly format. Essentially, they want all the data to populate onto a single sheet where the sheet has multiple instances of different fields names in the same column. Think like multiple tables stacked on top of each other in one sheet.  

My initial idea was to write all the raw data into a second sheet and then reference it into the pre-formatted tables on the main sheet. Even this is turning out to be overly complicated. I have managed to get the writer to write using the template, and I matched all the filed names so I would suspect that the raw data sheet would match the data to the field names. However, what ends up happening is that the fme excel writer writes two rows of field names, meaning that it is ignoring the formatting in the template and just writing the tables plus field names under the formatted areas.

Another problem that I am not sure how to solve is that the number of rows will vary by recorded plot. So any given time that I run this workbench the number of rows in the formatted tables need to either expand or contract, meaning that I have to set it up so the tables on the main sheet adjust themselves dynamically to fit the data. Not even really sure where to begin with this one.

Any helpful suggestions would be appreciated.