Copy to Google Sheets

A new option for Analytics Edge is to copy the results to a Google Sheets sheet. This option comes with some limitations, identified below. The option is available in both the quick query Functions wizard, as well as in the Write to Worksheet macro wizard. Functionality is identical; click the Select/Modify button to launch the Google Sheets wizard.

When the Google Sheets selection wizard opens, it shows the available Google login accounts (empty at first) as well as the folders, files in a selected folder, and sheets in a selected file.

Add – to add a new Google login to the selector, click Add; A browser window will open for you to log into your Google account and accept the permissions request. When finished, you will return to Analytics Edge and enter a reference name for that account (Analytics Edge reference names are stored in the workbooks).

Delete – delete the selected account.

Shared with me – show folders that belong to other people and are shared with you.

Folders / filter – displays a list of folders for the selected account, filtered by the characters you enter in the filter box.

Files / filter – displays a list of files for the selected folder, filtered by the characters you enter in the filter box.

Sheets / filter – displays a list of sheets for the selected file, filtered by the characters you enter in the filter box.

Limitations

Google Sheets is limited to 2 million cells per sheet, but that number includes empty rows and columns. New sheets are given 26 columns (A to Z), so by default you are limited to about 75,000 rows (2,000,000 / 26). If you need more rows, then you need to delete any extra columns.

Copying data to Google Sheets can be slow; writing 50,000 rows of data could take 15 seconds or longer to complete. Larger sets of data are possible — up to the limit of 2 million cells — you just need some patience.

Write operations are limited, so Analytics Edge will write in blocks of 35,000 rows. If the sheet does not have capacity for all of the rows in the block, the operation will fail with an error message and partially written data. You do not need to add rows to the sheet beforehand – Analytics Edge will do that automatically, up to the 2 million cell limit.

Formatting in Google Sheets is your responsibility. Dates will be written as serial numbers – format the column as a Date to see the actual dates they represent.

The target Google Sheet will be cleared before the write operation starts. Any added data or formula columns will be removed when you refresh.