Dynamically change file and sheet names in Google sheet output

Question

Hi,

I am trying to create a batch macro for dynamically changing files and sheet names based on year and entity within data. I was able to split the data in different sheets but no luck with files. Also do we have any workaround for changing write options configurations in case if file is not found and the tools needs to switch to 'Create new spreadsheet' option with filename received through control parameter. Snippet of batch macro attached.

Thanks in advance

Amol_Telore · Answer

Hi @prachikini19

To run the macro in batches, you will need to configure batch macro as shown below. Please check this part.

OllieClarke · Answer

@prachikini19 I think the reason you're struggling with files is that you'll need the workbookID as well as the file name, and that's harder to find.

I think you'll have to interact with the googleAPI to get out that ID, and from there you can check to see if the workbook exists or not. If not, then you can add a parameter to update whether you need to create a new file or not.

Hope that helps,

Ollie

Amol_Telore · Answer

Hi @prachikini19

Is your issue resolved? when I ran the workflow it seems to be working fine for me. I don't have access to google sheet connector but I tried to output data to excel files and I was able to generate multiple files as per your requirement.