Correctly passing blanks in Add row to spreadsheet?

Is there a way to set a cell in a sheet row to an empty value?

I’m seeing some interesting behavior when I try to set a cell in a sheet row to an empty value. spreadsheets.values.append in the sheets api allows setting an empty cell by passing an empty string, but there doesn’t appear to be any way to convince the Add row to spreadsheet action to do that.

The action will pass the string (blank) when I give it an empty string (ie no character or whitespace between commas.) Passing a liquid variable set to the empty string or nil will also be converted to the string (blank). Is that an intentional design choice when building the value array for the api call?

Using "" will send the literal string "" to the cell. Two single quotes '' are passed literally and interpreted by the sheet (if a cell has the default number format) as a text escape followed by a single quote, resulting in a cell showing '.

The only way I’ve been able to get the appearance of empty cells is to pass a single apostrophe, ', but that’s still putting cruft in my sheet.

Help?

Hi @Rick_Levine

You can use =char(20) to make it blank. The cell will have that formula in it, but it will look blank in the sheet.

We don’t sent explicitly blank cells to the API for internal reasons.

Hope that helps!

Thanks, Dave!

I opted for sending the single quote, as it’s just a formatting hack, rather than adding a formula to lots of cells.

Please add a “feature” request? - allow data to pass through to sheets without reinterpretation.

Or, document the changes you do make. Here, probably. Maybe in the API section? “Flow calls the Google Sheets API to add a row to a spreadsheet, with these additional constraints …”

Rick