Google Sheets – QUERY Returning #VALUE Error with Empty Column

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

The below query returns #VALUE error when the column H is completely empty:

=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 
 WHERE H IS NOT NULL
 LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''")

How do I make it only ignore this query and not give the value error? Because this query is only for one month, I have code for the other 11 months and wanted to simply omit the months without values. The current fix I found is to put a 0 on each column, to make the query run.

Find below the copy of the spreadsheet, with the full query. The formula is on the ZDP08 sheet, cell A2:
spreadsheet cc

Best Answer

wrap it in IFERROR and add fake columns for false part

IFERROR(your-query-here; {""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""})

full formula in ZDP08!A2 cell:

={
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!A5;","; ".")&" 
WHERE H IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!A4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!A5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"', H*"&substitute('Input Longo'!B5;","; ".")&" 
WHERE H IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!B4;"dd/mm/yyyy")&"' '', H*"&substitute('Input Longo'!B5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!C5;","; ".")&" 
WHERE I IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!C4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!C5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"', I*"&substitute('Input Longo'!D5;","; ".")&" 
WHERE I IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!D4;"dd/mm/yyyy")&"' '', I*"&substitute('Input Longo'!D5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!E5;","; ".")&" 
WHERE J IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!E4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!E5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"', J*"&substitute('Input Longo'!F5;","; ".")&" 
WHERE J IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!F4;"dd/mm/yyyy")&"' '', J*"&substitute('Input Longo'!F5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!G5;","; ".")&" 
WHERE K IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!G4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!G5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"', K*"&substitute('Input Longo'!H5;","; ".")&" 
WHERE K IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!H4;"dd/mm/yyyy")&"' '', K*"&substitute('Input Longo'!H5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!I5;","; ".")&" 
WHERE L IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!I4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!I5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"', L*"&substitute('Input Longo'!J5;","; ".")&" 
WHERE L IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!J4;"dd/mm/yyyy")&"' '', L*"&substitute('Input Longo'!J5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!K5;","; ".")&" 
WHERE M IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!K4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!K5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"', M*"&substitute('Input Longo'!L5;","; ".")&" 
WHERE M IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!L4;"dd/mm/yyyy")&"' '', M*"&substitute('Input Longo'!L5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!M5;","; ".")&" 
WHERE N IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!M4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!M5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"', N*"&substitute('Input Longo'!N5;","; ".")&" 
WHERE N IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!N4;"dd/mm/yyyy")&"' '', N*"&substitute('Input Longo'!N5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!O5;","; ".")&" 
WHERE O IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!O4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!O5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"', O*"&substitute('Input Longo'!P5;","; ".")&" 
WHERE O IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!P4;"dd/mm/yyyy")&"' '', O*"&substitute('Input Longo'!P5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!Q5;","; ".")&" 
WHERE P IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!Q4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!Q5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"', P*"&substitute('Input Longo'!R5;","; ".")&" 
WHERE P IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!R4;"dd/mm/yyyy")&"' '', P*"&substitute('Input Longo'!R5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!S5;","; ".")&" 
WHERE Q IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!S4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!S5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"', Q*"&substitute('Input Longo'!T5;","; ".")&" 
WHERE Q IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!T4;"dd/mm/yyyy")&"' '', Q*"&substitute('Input Longo'!T5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!U5;","; ".")&" 
WHERE R IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!U4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!U5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"', R*"&substitute('Input Longo'!V5;","; ".")&" 
WHERE R IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!V4;"dd/mm/yyyy")&"' '', R*"&substitute('Input Longo'!V5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!W5;","; ".")&" 
WHERE S IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!W4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!W5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""});
IFERROR(QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"', S*"&substitute('Input Longo'!X5;","; ".")&" 
WHERE S IS NOT NULL
LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT('Input Longo'!X4;"dd/mm/yyyy")&"' '', S*"&substitute('Input Longo'!X5; ","; ".")&" ''");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""})}

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

Best Answer

Related Solutions

Google Sheets Query – Fix Date Formatting Not Working in Query Function

Google Sheets – Make All Cells in Column Links from Values

Update

Code

Explained:

Example:

Related Topic