I need help with changing how Boolean values appear in my Google Sheets without modifying the original data.
I have a spreadsheet that pulls data from BigQuery and one column contains Boolean values that show up as “true” and “false”. In another sheet tab, I run queries on this data but want the Boolean column to display as “Yes/No” or “✓/(empty)” instead of the default true/false format.
Adding extra helper columns isn’t ideal since the dataset is already large. Is there a way to format or transform these Boolean values during the query process itself? Looking for a solution that changes the display without creating additional columns in the source data.
there’s another way - use ARRAYFORMULA with IF. try =arrayformula(if(A:A=true,"✓",if(A:A=false,"",A:A))) in a new column on your query sheet. works great when you can’t modify the original BigQuery pull but need different display values. i’ve done this when clients want fancy formatting but the data source has to stay unchanged.
You can fix this with custom number formatting in Google Sheets - no need to mess with your source data. Select the Boolean column, then go Format > Number > More Formats > Custom number format. Enter "Yes";"Yes";"No" for Yes/No display or "✓";; for checkmark/empty format. This works great when you’re importing data but want it to look different. I use this all the time with API imports where the source sends standard Boolean values but stakeholders want something more readable. The formatting sticks even when data refreshes, so you set it once and you’re done. Way simpler than messing with queries if SQL isn’t your thing.
Hit this same problem last month with BigQuery survey data. Just use a CASE statement in your query instead of messing with cell formatting later. SELECT CASE WHEN boolean_column = true THEN 'Yes' ELSE 'No' END AS display_column works great and doesn’t touch your original data. If you’re pulling through Data > Data connectors, just modify the SQL query there. The Boolean conversion happens during import so you skip all the extra processing in Sheets. Been doing this for months - way more efficient than formulas or custom functions afterwards.