Wait to load google sheets data before running script

Question

I have multiple users who submit daily progress information via a Google form at different work sites.

All the data gets dumped into a single sheet -> Call it the master sheet

I have created separate sheets that use the query function to filter data pertinent to each site -> call it the single site sheets

Each month, I have a google apps script that creates a pdf summary by month for responses at each site. This has been working fine for some time, but recently, the script runs and creates a pdf without any data.

When I have tried to troubleshoot this I think I see the issue. Now that the master sheet has collected over 1000 rows of data, the query function for each single site sheet takes a few seconds to load. During the load time, the script runs without any data in the single site.

Is there a way to ensure the relevant data has completely loaded before the app script runs on that sheet? Maybe with a setTimeout or some on.load function?

Messy way of doing it would be to get the last row of the sheet and make sure it has a value displayed using getDisplayValue() before running the export portion of the script. Someone else may have a cleaner suggestion though. — ross
I read the documentation and considered Utilities.sleep(), but I'm not familiar with how or where to call this method in my script. — user1837608
How are you running the script? Is this a monthly trigger or do you run it manually? — ross
The scripts are grouped as one function for each month, and the relevant function is indeed linked to a monthly trigger. If I run it manually, it always works since I can't open the script to run until the worksheet is loaded. Must be some difference between automated trigger vs manually triggered. — user1837608

Ken Adams Ken Adams · Accepted Answer · 2019-06-13T16:26:16

well I would not use formulas to bifurcate the data here. Instead, i would create a script to throw the data to respective sheets and put it onFormSubmit trigger. This way, the data would be static on the target sheets and your PDF generating script will do just fine.

Note: formulas with larger set of data would always cause problems with script execution one way or the other.

P.S. Don't forget to use SpreadsheetApp.flush(), very important part, check the documentation on support articles of GAS.

Wait to load google sheets data before running script

1 Answers