Exporting Attribute Pivoter to Google Sheets

Hi ​@kylewicks,

There is a way to proceed from you post AttributePivoter point. Assuming the data is as shown in the second screenshot:

• Place a AttributeCreator. Have it make an attribute based on the attribute value of the second column. (I am going to assume there are no weird values in the second column), with the value of the third column
• Sample the first row of each municipality with a Sampler. (Group processing on the first column, Sampling type set to First N features. Leave Sampling rate at 1)
• Follow it up with a FeatureMerger. Connect the Sampled port of the Sampler with the Requestor and the NotSampled port with the Supplier. In the settings of the FeatureMerger, Join on the First colum and make sure Process Duplicate Suppliers is checked.
• You should now have a single row per municipality with the correct columns. 
• Expose the columns with a AttributeExposer for better visibility and operational options.
• Assume this is one of the datasets (Customer or Company), do the same for the other dataset.
• Merge both datasets with a FeatureMerger or FeatureJoiner and fix attribute names with a AttributeManager.
• After that you can start writing. It might be possible to use an empty template as shown in the first screenshot, but that depends on the format.

There might be another way to do this but that requires us knowing how the data looks like pre pivoting.

Good luck!

Hey! Thanks for the response...I tried that process and it still isn’t working. This is what shows after the feature merger...and for some reason all 0.75 diameter values are gone.
 

To add further context, the first column in the second screenshot of the original post the columns are TOWN, DIAMETER, and TOTAL.

Is there any other information I can provide that would help?

Thank you so much for this. That ended up working, I’ve never used the attribute exposer before, but I think that was key to getting it to work. I really appreciate you taking the time to help me out!

Hate to ask you another question, but do you think it’s possible to get both sets of data (customer + company) into the same spreadsheet using template formatting? I have a feeling it would be very annoying or difficult…please let me know if you have suggestions. Otherwise, I may just change it to be separate tabs for each.
 

Hi,

Good to know it worked. As for you next question, there are some steps you can take. But I must preface this with the knowledge that I haven't written to google sheets yet so this purely what is written in the documentation and what I know of from Excel format. Here's what you can try:

• Make a template file like you have in the first screenshot in Google Sheets, and make a backup. 
• In FME, rename UNK, 0.75, 1, 1.5, 2, 3, 4, 6, etc. of the customer table to customer_UNK, customer_0.75, etc. You can do this with the BulkAttributeRenamer. Do not rename TOWN. 
• Do the same for the company table by bulk renaming them to company_UNK, company_0,75, etc. Do not rename TOWN.
• Dont worry about the attribute names as we are not going to write them
• Feature merge the company and customer datasets with a FeatureMerger or FeatureJoiner based on TOWN. This should place both tables next to eachother like you last screenshot.
  Order it futher with a AttributeManager
• Add the Google sheets Writer. You will have to fill in the connection and Spreadsheet Name/URL yourself. However Schema Generation should be something like this: 
   

• 

  You will need to test this yourself, but I think it will start writing the data from row 6 and will not write down the attribute names. 

• In the feature type parameters, make sure the following is set:

  • Feature Operation: Insert

  • Worksheet handeling: Truncate Existing (most likely, but try this out)

I hope this works. I can't edit the workspace as I don't have a Google Sheets webconnection at hand.
Good luck!

Thank you so much, this all worked. I really appreciate it.

Now I’m trying to figure out how to add another section on the page for a separate business unit’s data that’s essentially the same set up, but comes from a different gdb.