I’m working with a QUERY function that extracts information from a separate sheet for creating driver assignment reports. The issue I’m facing is that there are too many columns which makes the printed version difficult to read when trying to fit everything on one page width.
I want to combine several columns (specifically columns G through M) into a single column using either a hyphen or comma as a separator between the values. Is there a way to achieve this concatenation while still using the FILTER and QUERY combination? I need the final result to have fewer columns so it prints better on our daily route sheets.
Been fighting these same spreadsheet issues for years. Manual formulas work but break whenever your data changes.
You’re asking Google Sheets to act like a database when it’s not. Those nested ARRAYFORMULA tricks get messy and bog down with lots of employee data.
I fixed this for our logistics team last year. Set up a Latenode automation that grabs data from the source sheet, handles column concatenation, applies your date and driver filters, then spits out a clean report.
Runs every morning before shifts start. Takes 30 seconds vs the 10 minutes our dispatcher wasted wrestling with formulas. When we switched separators from hyphens to pipes, it took 2 minutes to update instead of rewriting complex formulas.
Bonus - you can create multiple report formats from the same data. Wide for screens, narrow for printing. Can even email results straight to drivers.
Way more solid than crossing your fingers that QUERY formulas won’t break when someone adds a column.
try ARRAYFORMULA with CONCATENATE in your query: =QUERY(your_filter_data, "select Col1,Col2,Col3 where...") then wrap parts with CONCATENATE(Col6,"-",Col7). but honestly, it’s easier to just create a helper column that concatenates those columns first, then reference it in your query.
Nope, you can’t use CONCATENATE directly in QUERY’s select clause - Google Sheets just doesn’t support it. I’ve hit this wall tons of times building executive dashboards where space was tight.
What actually works is preprocessing your data first. Use ARRAYFORMULA to build the concatenated column, then query that result. Try something like =QUERY(ARRAYFORMULA({Employee_Data:A:F, Employee_Data:G:G&"-"&Employee_Data:H:H&"-"&Employee_Data:I:I, Employee_Data:N:Z}), "select * where Col2='"&D2&"' and Col1='"&D3&"'").
Basically, you’re building a new array where one column has your merged G-M data, then filtering that. It takes some tweaking with column references but gets you exactly what you need for clean printing.
Google Sheets formulas turn into a nightmare when you’re doing complex data stuff like this. You’ll end up with crazy nested functions that are impossible to debug.
I’ve hit this same wall with reporting at work. Trying to concatenate inside QUERY just doesn’t work well - you’re mixing function types and Sheets hates it.
For our daily ops reports, I ditched spreadsheet formulas completely. Built an automation in Latenode that grabs the data, handles all the column merging and formatting, then dumps clean results into a fresh sheet ready to print.
Best part? Set it to run every morning automatically. Your route sheets are always ready without fighting formulas. When requirements change (they always do), you just tweak the automation instead of rewriting spreadsheet hell.
Latenode crushes this kind of data transformation way better than forcing Sheets to do heavy lifting. You can concatenate, filter, format, even spit out different report layouts for different teams.
You’re trying to concatenate columns G through M in your Google Sheets data before using the QUERY function to create a report. The QUERY function doesn’t directly support concatenation within its SELECT clause, and your current approach using nested functions within QUERY is proving difficult. You need a more efficient way to combine these columns into a single column separated by hyphens before filtering and querying your data to produce a more readable printed report.
Understanding the “Why” (The Root Cause):
The QUERY function in Google Sheets is powerful for data manipulation, but it has limitations. Trying to perform complex operations like string concatenation directly within the SELECT clause often leads to errors or unexpected behavior. It’s more efficient to preprocess your data—performing the concatenation before the QUERY function—to simplify the query itself and improve performance, especially with larger datasets.
Step-by-Step Guide:
Preprocess Data with ARRAYFORMULA and JOIN: Instead of trying to concatenate within the QUERY, use ARRAYFORMULA and the JOIN function to create a new array with the concatenated column before applying the FILTER and QUERY. This approach separates the concatenation logic from the filtering and querying, making your formula much cleaner and easier to understand. The JOIN function is specifically designed for concatenating elements of an array, which makes it much more efficient than repeatedly using the & operator.
{Employee_Data:A:F, ARRAYFORMULA(JOIN("-", Employee_Data:G:M)), Employee_Data:N:Z}: This creates a new array. It combines columns A:F from Employee_Data, the newly concatenated columns G:M (using JOIN to separate values with hyphens), and columns N:Z from Employee_Data. This is your pre-processed dataset.
FILTER(...): This filters the newly created array based on your existing conditions (Employee_Data:A:A=D3 and Employee_Data:B:B=D2).
QUERY(...): This applies the QUERY function to the filtered data, selecting only the desired columns. Notice that the concatenated column is now Col7. Adjust column numbers as needed to match your data.
Verify Column References: Double-check that the column numbers in your QUERY’s SELECT clause (Col1, Col2, etc.) accurately reflect the columns in your newly constructed array. Remember that the concatenated column (G:M) is now Col7.
Test and Adjust: Test your updated formula with a small sample of your data to ensure it produces the correct results. Then, apply it to your full dataset. Adjust the formula and the column indexes if needed.
Common Pitfalls & What to Check Next:
Incorrect Column References: The most common mistake is using incorrect column numbers (Col1, Col2, etc.) in the QUERY after creating the new array. Carefully count the columns in your array to determine the correct references.
Data Types: Ensure that columns G through M contain data that can be concatenated without errors (e.g., no unexpected characters).
Error Handling: For a more robust solution, consider adding error handling to your formula to gracefully manage potential issues, such as missing data or incompatible data types.
Large Datasets: If you have an extremely large dataset, this approach may still be slow. Consider optimizing your data structure or exploring alternative methods like using a database for more efficient data processing.
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!
Had the same headache last month. Don’t fight QUERY’s limitations - just use JOIN in your filter: =FILTER({Employee_Data:A:F, ARRAYFORMULA(JOIN("-",Employee_Data:G:M)), Employee_Data:N:Z}, conditions) then query that result. Way simpler than the crazy nested formulas everyone else is suggesting.
Understanding the “Why” (The Root Cause):
Step-by-Step Guide:
Common Pitfalls & What to Check Next:
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!