google sheet query optimize syntax in where clause with many columns
Hi my gsheets query studying are in progress and I'm looking for optimization of formulas that can be become very long with many datas. In this case I can't find a method to optimize the lenght becaue of the syntax, I think...
I tryied this formula =query({A:G};"select Col1 where "Col"&arrayformula(TEXTJOIN(" OR Col";;SEQUENCE(1;6;2)&"="&""&I2&""))")
Unfortunatly with arrayformula and Text join doesn't work, but copy/paste the result of that formula works properly.
Here's the link to the sheet
Solution
You may try:
=query({A:G};"select Col1 where Col"&join(" OR Col";index(sequence(1;5;2)&"="&I2)))
- How can I use Google Sheets Apps Script to Auto Generate today's date when an edit is made to a given column?
- IsDate ArrayFormula
- how to show instruction with image after install google workspace extension
- Formula to convert matrix to rows in Google Sheets
- Regular expression to extract valid cell references from a spreadsheet formula
- Is Google Sheets less or more accurate than Microsoft Excel?
- google sheet merge two column into one column with prefix words
- Get highest occurring row number of key?
- If checkbox checked set values
- Clarification on using the forEach() function
- Why is this ARRAYFORMULA + VLOOKUP() + REGEXEXTRACT() with multiple conditions not working?
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- Add title row with ARRAYFORMULA in Google Sheets
- Rank based on which column is being sorted by in a Table
- Auto border to a Dynamic Data
- How can I determine if a respondent's email address is unique in a list?
- Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY?
- Google Sheets - returning Nth result with multiple matching criteria
- Can a label in a Google Sheets Query be dynamic; can it reference a variable?
- Sum a number of range values; process and add to array
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Why does my Google Service Account not see files shared with it, but can see files created with it?