Having trouble with the query formula for a report in Google Sheets

Question

Here's the formula I have. Please evaluate and let me know if you see any issues.

=(query(importrange("sheetLink","FormResponses"),"Select Col1, Col4, Col5 Where Col3 Contains '"&EmployeeName&"' And Col4 >= '"&ReportStartDate&"' And Col4 <= '"&ReportEndDate&"' And Col8 = '"&JobName&"'",-1))

Any help you could give will be greatly appreciated.

Try removing the ampersands and provide a range in the sheet "FormResponses". — JPV, Dec 31 '18 at 08:21
I think the query itself does look OK if EmployeeName etc. are named ranges — Tom Sharpe, Dec 31 '18 at 13:01
@Tom Sharpe they are named ranges. I'm getting a #N/A error, but the yield remains the same even with a wildcard. — Ros Posey, Jan 01 '19 at 22:27
Can you share a copy of your spreadsheet, so that we can see the actual data? — JPV, Jan 02 '19 at 07:34
Assuming col4 contains dates, remove the single quotes around StartDate en EndDate. — JPV, Jan 02 '19 at 07:36

Tom Sharpe · Answer 1 · 2019-01-02T11:15:35.330

0

You have to do this to make sure the dates work properly:

=query({A:H},"Select Col1, Col4, Col5 Where Col3 Contains '"&EmployeeName&"' And Col4 >= date '"&text(ReportStartDate,"yyyy-mm-dd")&"' And Col4 <= date '"&text(ReportEndDate,"yyyy-mm-dd")&"' And Col8 = '"&JobName&"'",1)

edited Jan 02 '19 at 11:15

answered Jan 02 '19 at 10:13

Tom Sharpe

30,727
5
24
37

score 0 · Accepted Answer · edited Jan 03 '19 at 04:21

The actual function that worked properly with the IMPORTRANGE function is as follows:

(query(IMPORTRANGE("1qDKiRFFFnX5ORvleTIAoEcxD0WDjGopvA5UqcWVOfNs","FormResponses"),"Select Col1, Col4, Col5, Col6, Col7, Col8, Col10 Where Col3= '"&EmployeeName&"' and Col4>= Date '"&Text(DateValue(ReportStartDate),"YYYY-MM-DD")&"' and Col4<= Date '"&Text(DateValue(ReportEndDate),"YYYY-MM-DD")&"' Format Col4 'MM/DD/YY', Col7 '$#,##0.00'",-1))

Having trouble with the query formula for a report in Google Sheets

2 Answers2