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

1
votes

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

2
sqlgoogle-sheetsgoogle-sheets-formulagoogle-query-language

2 Answers

3
votes

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.

1
votes

Issue:

QUERY function in google sheets accepts only one data type per column:

In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

and this is the reason your query returns empty cells for column H.

Unfortunately, I don't think there is an approach to convert only a single column in google sheets to a different data type within the query function. But I do know approaches where you can convert the full input matrix to another data type with the solely purpose of having a unique data type per column.

So now you have two options. Either you convert everything to a numeric value, but this will probably drop an error since text values like instagram can not be converted into a numeric type. Or you can convert to text the full array so you can at least get the desired/complete result back.

The sacrifice of this approach is that column H won't be numeric. But at least you have full the data in your sheet and you can explicitely convert only column H to numeric data type with the Values function.

Workaround to get the full data back:

=ArrayFormula(QUERY(TO_TEXT(IMPORTRANGE("sheet_name", "Staffing Data 0!A:AS")),
"SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13, Col37, Col2, Col3 WHERE Col2='instagram'"))

and then

=VALUES(H3:H)

to get the values of column H as numeric values.