Retrieve filtered range in one range?

[Chris Larkin]

Hi -

I've seen examples on how to add a filter to a range in Google Apps Script (Sheets), but all do the opposite of what we're suggested to do: the rows are queried 1 at a time in a for loop.

Is there a way to request a range at once, reducing the number of methods that have to be executed?

Thanks in advance for reading! 

Christopher

[Michael O'Shaughnessy]

I am a little curious as to where you have read "what we're suggested to do"...  I do agree that the more "service" calls that are made the "slower" the script will run.  However, I would argue that it would not be noticeable until your are dealing with a large amount of data.

It is very easy to "grab" the data from a sheet as a 2d array then use "array.filter()" to filter the data for what you are looking for.  Now, I do admit that if you are "pulling the data 1 row at a time" from the spreadsheet this will take a long time.  The same goes for if you are writing to the spreadsheet one row at a time.

Here is alink to a spreadsheet that has some basic code that pulls info from the student tab, filters it for a specific period number, then writes all to another tab. 

https://docs.google.com/spreadsheets/d/1O9jBypgUW05UsSLYtYhkhMjHmFZ9CqEbt01usGWUB9Y/edit?usp=sharing

Now, you might want to take advantage of some spreadsheet functions, specifically the "query()" function.  You could run the query on your data filtering as you need, then in your script grab the queried data.  There is an example of this in the spreadsheet I shared as well.

Hope this helps!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a444cf6c-7e7f-434a-a571-5865a91f5708%40googlegroups.com.

[Chris Larkin]

Such great points - thank you!

You solved my problem - I can pull the range then filter the array.

It is a huge amount of data - going a row at a time exceeds the 30 min per script limit in Google Ads and I’m trying to avoid the pattern where you log progress then when the hourly script runs again, you pick up from the last logged record.

Thanks for pointing me in the right direction!!

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/mefYO455UnE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLjEbZjRjN1UpQtO2J-D%2B_8aKMgPg__LE4Sxk%2Bc0dELEDA%40mail.gmail.com.