Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Google Sheet Output Too

SaoriYamguchi
8 - Asteroid

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

2 REPLIES 2
DavidP
17 - Castor
17 - Castor

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

DavidP_0-1635344489719.png

 

Workflow:

DavidP_1-1635344578420.png

 

Example workflow and macro attached

SaoriYamguchi
8 - Asteroid

Thanks!

Labels