This Analytics Edge wizard is used to write the results of an analysis to a Google Sheets worksheet. It always overwrites the entire sheet. It can can be added as a separate macro function, or as an option from the Write to Worksheet wizard.
Account – select a Google account to use. Google accounts are managed in the Options wizard.
Add – add a new Google account. 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.
NOTE: files adn folders must bve made accessible to the Add-in before they will appear in the listings. See /function/aec-google-sheets-open-with/
Date format – use this format for dates written to the Google Sheet. See codes.
Shared with me – show only 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.
Restrictions
Google Sheets is limited to 5 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 192,000 rows (5,000,000 / 26). If you need more rows, then you need to delete any extra columns.
Writing 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 cell limit — you just need to be patient.
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 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.
Updated for v10