Google Sheet Output Too

[SaoriYamguchi]

I would like to dynamically create a sheet using the Google Sheet Output Tool.
What should I do? Do you use interface tools?

[DavidP · Accepted solution]

Hi @SaoriYamguchi 

Yes, the way to do this is by creating a batch macro where you can specify the new sheetname. There is a bit of a trick you have to be aware of though.

A typical batch macro has a data input, in this case the data you want to write to the sheet, and a control input, where you change the behaviour of one or more tools in your macro. In this case, we want to tell the Google Sheets Output tool to create a new sheet, and we want to specify the name of the sheet through the Control parameter.

The control parameter bit is pretty easy, you assume there will be a field with the name of the new sheet and it just updates the value of the existing sheetname.

You also need give the macro an example of the data that is going to come through the macro input, so you need to create a template with the same field names. But what if you want the data for the new sheet to have different fields? This will break the macro and cause an error.

There is a way though, to convert any dataset to a format that is consistent - the transpose tool will convert any data set to only 2 flieds: Name and Value. You need to add a Record ID before transposing and keep it as a key field. One last thing is to preserve the order of the field names, and you can do this will the tile tool.

You can now pass this data set to the macro and within the macro convert it back to it's original format with a crosstab, and use the dynamic rename to change the field names back to what they were originally, in the right order.

The final step is then to write the data to the New sheet, as specified in the Control Parameter. You have to make sure that the write mode is correctly set in the Google Sheets Output tool to write to an existing file.

Macro

Workflow:

Example workflow and macro attached

[SaoriYamguchi]

Thanks！