Re: How to persist column data types / formatting when using Google Sheet automation?

8156 4
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast
I am trying to sync information that needs to stay intact but it seems that it is being converted to a number somewhere in the process of getting appended to Google sheets. The actual field in AirTable is "Single Line Text" and the corresponding column in Google Sheet was set to "Plain Text".
As an example, I have a field with value "030" in AirTable with an automation to create a row with that value. But once it gets sent over, the leading zero is truncated and becomes "30" instead in Google Sheets. I thought about appending some magic symbol such as "030@" and then removing the "@" symbol, but then that creates another issue because you would still need to go into the sheet manually and drag the formula to the newly created row. I checked the automation run log and it seems like "030" is getting sent to Google Sheets, so somewhere in between where it is getting sent to Google Sheets, the leading zero is getting truncated. Is there a way I can configure the Google Sheet to convert it to a number and keep it as a string when appending a row? Also, the formatting is getting messed up.
As an example, row 40 is the new row that was appended but formatting and data was messed up.
5 Replies 5

Hm, if, in Google Sheets, I set the format of the column to "Plain Text", new rows that get created in Sheets show up as "030", so perhaps you could try that?

Screenshot 2023-02-05 at 1.31.04 PM.png

I've already tried that...set to "Plain Text" before the new row was appended, but the leading 0 was still removed.

Weird!  Here's a gif of it working for me as a data point:

Screen Recording 2023-02-06 at 2.07.08 PM.gif

Interesting! Which automation are you using? Yours seems to be behaving differently than mine not only in the fact that the data seems to be getting transcribed correctly. It seems you have a bunch of blank rows already populated (ie rows 2-15+) and your action is correctly inserting in the first available slot, however, with the one I am using, if there are a bunch of blank rows, it will still create a brand new row at the bottom.

I am using the built in "append a new row to a spreadsheet" action.

I'm using the same action actually, here's a link to the base

Lemme know if you could use any more help testing stuff out; this feels like one of those puzzles that's going to be slightly frustrating to deal with heh

May want to open a support ticket too?