Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-query

Google script - limit query results - Form dropdown workaround

As we know, Google has placed a truly unreasonable limit of dropdowns in the forms. I have a dropdown which was supposed to have 1500+ choices. In order to overcome this, I was thinking about a very messy workaround of creating multiple forms. These forms will be identical, expect of this specific dropdown which I'll divide between the forms. The dropdown is to include a list of client=leads so I was thinking about splitting it based on the first letter of their last name. How do I phrase the query to retrieve just, for example, values which start with A-F letters?

Is there a way to have a dynamic single query that will split between 2-3 different columns, so that my dropdowns will easily be filled from these columns? Or, even better, to have the query limit results to (for example) 850 rows per column?

Thanks

Solution

  • Below are my suggestions, assuming your list were in A2:A.

    For two columns, limit 850 per:

    =ArrayFormula(QUERY({QUERY(INDIRECT("A2:A"&ROWS(A:A)-1),"Select * Skipping 2"),QUERY(A3:A,"Select * Skipping 2")},"Select * LIMIT 850"))

    For three columns, limit 850 per:

    =ArrayFormula(QUERY({QUERY(INDIRECT("A2:A"&ROWS(A:A)-2),"Select * Skipping 3"),QUERY(INDIRECT("A3:A"&ROWS(A:A)-1),"Select * Skipping 3"),QUERY(A4:A,"Select * Skipping 3")},"Select * LIMIT 850"))