0

I have a Google Sheet where data is uploaded from a Google Form in one page and workers input data directly in another page. The data is combined into one table via scripts. I am trying to create a dashboard to monitor the data, and currently have several pivot tables that feed charts and formulas. There are currently ~7000 rows to process.

What are some ways to optimize the spreadsheet to handle thousands of rows of data? For example, is there a way to change the recalculation cycle to only refresh every 2-3 minutes? The only calculation settings I can find are in the "Spreadsheet Settings" menu and only affect the TODAY(), NOW(), and RAND() functions.

In another question, I asked specifically about whether pivot tables or the SORT(FILTER()) combo is faster in this context, but wanted to ask for more general help too.

Improve this question
0

1 Answer 1

Reset to default
0

The easiest solution is to break apart your spreadsheet into separate spreadsheets, you can use importrange or google app scripts to periodically update the information.

It will look like this

(a) Data entry spreadsheet (b) Data processing spreadsheet (c) Data display spreadsheet

Then chain them all together with importrange and it will dramatically help your loading times. Breaking apart the data processing into multiple sheets may also be necessary. This will be the only way to effectively "prevent autocalculation" on every change. Now when you make any changes in (c) it won't force calculations in (b) or (a).

Improve this answer
4
  • Thanks for the suggestion. I have tried separating the data processing to a different sheet, but found the importrange function was not reliable with large amounts of data. It would work for a few days, then the importrange function would just stop updating.
    – Brian
    Dec 12, 2019 at 18:39
  • I just learned about BigQuery and Google Data Studio, which sound like they're designed for handling large datasets, so maybe I'll look into using those instead or in conjunction with Google Sheets.
    – Brian
    Dec 12, 2019 at 18:40
  • @Brian I am having no problems with importrange with way over 10k rows. There is a few issues you want to consider 1) have a seperate sheet containing all your importrange locations 2) anytime you make a copy of the workbook you will need to go back into that sheet to "enable" the ranges for security reasons 3) one time in a 12 month period I had an issue where I needed to go into all the formula's and hit enter but this issue wasn't isolated to importrange as I have seen it with any reference formula as well.
    – CodeCamper
    Dec 12, 2019 at 20:26
  • @Brian see my new answer in the duplicate question that closed this answer.
    – CodeCamper
    Dec 13, 2019 at 3:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.