Multiple Tab Combine Data

Question

I use Google Analytics to add to sheets. The specific query that I would like to run returns 70 thousand data rows. Since Google Analytics returns a maximum of 10 thousand. In time, I broke the queries into 7, where the initial index was 10 001, 20 001, etc.

So, now I have 7 data tabs, each of which contains 10 thousand rows.

I would like to combine them all into one big table. I looked into the =query() function and looked for it without success. I also looked at =join() , but also did not succeed.

Even if I were successful with these options, I’m not sure that they are the “best”.

Thanks to SO, I have limited experience with the Google Apps script (but collectively quite simple). I'm not sure if I should try to figure out how to do this with a script or if there is a convenient built-in function.

I also realize that I skipped 200k sheets per sheet.

For example, I have data already available among 7 tabs. It seems ineffective to bring them all in one big table from the moment of duplication.

My ultimate goal is to query the data as a group, using, for example, the function =sum(filter()) and / or =query() . I need to cut n this data in several ways, but currently it spreads over 7 tables.

• How can I join one big table
• Is there a good idea, and if not, is there a better way?

Answer 1

According to your description, I believe that you are not using join () correctly. This is not the same as connecting to the database, but rather concatenating the cells of a row into a row.

This is not only a 200K cell limit, but the execution time for each script can be one of the problems, depending on how much data you have on each sheet. If you have 20 columns in each row of a 10K sheet, you can easily reach the limit of 200K with each of them. In this case, I would like to draw your attention to some “Best Practices” . In particular, using Cache Service to reduce the time of your turn for the next calls. As mentioned in the documentation, the first call will take the same time, but all subsequent calls will be much faster.

With 7 sheets and a lot of data to be available, the Cache service will be useful, as it can be used to query Cached data for faster rotation. The default value for expiration is 10 minutes, but using putAll (value, expirationInSeconds) , you can get cached data in 21600 seconds (6 hours).

Write a function to cache all your data and use the service to get all the information you need. I feel in your case, this is your best choice. In addition, this may be a useful link for some frequently asked questions related to AppScript.

Answer 2

You are using the wrong task tool - import all sheets into Google Fusion tables , then use one common key field (create one before loading, if it does not already exist) to join them in a new joined table.

It will run much faster and you can filter the results and select columns using the view, and then load the output into a new spreadsheet, which you can also upload as a Google Sheet :)