Search code examples
sqlgoogle-sheetsgoogle-sheets-formulagoogle-query-language

How do I modify my Google App Query formula to convert empty cells in every row of a specific column to be numeric format?

Im trying to filter a data from another spreadsheet. This is the query of what I'm trying to filter:

QUERY(IMPORTRANGE("sheet_name", "Social media posts!A:AS"),"SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13, Col37, Col2, Col3 WHERE Col2='instagram'")

Output of the original query. Col37 is column H. Please check column H in image, which has empty cells.

Apparently, the cells in every row of column H are not consider to be numeric. I can't compromise the original sheet. So, how do I modify my query to convert empty cells in every row of column H to be in numeric format like with this function =n(H3:H)

enter image description here

Solution

  • Workaround:

    You may be able to split up the queries into three arrays and join them back using {array1,array2,array3}:

    • The first array for all columns before Col37
    • The second array for Col37. Here, you try to coerce all empty cells to 0 using double unary operator --
    • The third array for all columns after Col37

    Snippet:

    =ARRAYFORMULA({
  QUERY(IMPORTRANGE("sheet_name", "Social media posts!A:AS"),"SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13 WHERE Col2='instagram'"),

  IFERROR(--QUERY(IMPORTRANGE("sheet_name", "Social media posts!A:AS"),"SELECT Col37 WHERE Col2='instagram'"),QUERY(IMPORTRANGE("sheet_name", "Social media posts!A:AS"),"SELECT Col37 WHERE Col2='instagram'")),

  QUERY(IMPORTRANGE("sheet_name", "Social media posts!A:AS"),"SELECT Col2,Col3 WHERE Col2='instagram'")
})

    Performance Considerations:

    • Using 4 queries is expected to hit performance rather than a single query

    • However, I expect all IMPORTRANGE to be cached avoiding 4 different calls.

    • Although you mentioned you are unable to modify the source sheet, Modifying the source sheet is a better solution: Modify the source sheet formatting and make empty spaces 0 in the source sheet and a single query in the destination sheet.