Batch macro to output to Google Sheets multiple tabs based on field

Question

Im trying to output some formatted reporting to Google sheets and I think I roughly know the approaches I can take but would appreciate if someone could join the dots for me.

I have concluded that rendering to excel and converting to Google Sheets is a possibility but would be my last resort.

I am looking at using the Google Drive Output tool, outputting to GHEET format:

The field that is used to create the tabs is called 'Location' and there are 3 locations: USA, UK, Germany

Im using the Google Drive Output tool to overwrite the Gsheet tab

Question 1) My understanding is if I want the data for each location on separate tabs I would need to use a batch macro, how would I do this with the above?

Question 2) When I do output, the text is overwritten and all formatting is lost in the gsheet too. What is the best way to get the text formatted?

Outputting from a formatted table using the table tool doesn't work. I've heard there is an approach using a 'blob' template, does this work with Gsheets? If not what other options are there?

Thanks

AkimasaKajitani · Answer

Hi @Carluccio555

Answer 1 ) When you want to write multiple sheets at the same time, you don't need to use Batch macro. Instead of it, you can use Data Range option by Field.

You have to add the field which has the sheet name to write into. For example, please see the following data.

Answer 2) GoogleDrive Output tool seems that it remakes the sheets, because the all format is cleared. I can't be sure. If you use append option, the result is the same.

And I think that the blob approach don't work in Spreadsheet. It is only for local Excel files.

I think another possible approach is using Spread Sheet API v4.

https://developers.google.com/sheets/

Carluccio555 · Answer

It turns out Overwrite is no good for what I need as I thin it will wipe the preceding tabs as it loops through the macro