Appending a row to Google Sheets

[tomm]

OK, this is a really strange one and I have no idea what caused it.

I created a form that I wanted to be used to both add data to my Retool database table and and the same data to an existing Google Sheet. Here is what I did to append the row to Google Sheets:

When I submitted the form data, it added to the Retool database just fine. It also wrote the data to the Google sheet, but instead of starting in column A where ship_date is, it started writing in column W, which is actually the pymt column. So the rows appended look like this:

Did I set something up incorrectly, or miss something? Any help would be appreciated.

[Tess]

Hey @tomm,

I've had some similar reports here

Ideally, we could set the range manually, but our team hasn't been able to prioritize that feature request yet. One workaround is to use the rest api instead of our integration, but I'm curious if we can pinpoint exactly why it is happening here.

Do any of your columns not have labels? Is there any discrepancy (trailing spaces, lower/upper case mismatches, etc) in your query data vs the spreadsheet column names?

[tomm]

Hi @Tess,

Here is what I am currently using to try and add data to the Google Spreadsheet. This is the form:

And this is the query:

I have also tried using the following code in the "Values to append" field:

[ { "ship_date": {{ form19.data.ship_date }}, "notes": {{ form19.data.notes }}, "load_time": {{ form19.data.load_time }}, "depart": {{ form19.data.depart }}, "dealer": {{ form19.data.dealer }}, "delivery_add": {{ form19.data.delivery_add }}, "carrier": {{ form19.data.carrier }}, "driver": {{ form19.data.driver }}, "trailer": {{ form19.data.trailer }}, "model": {{ form19.data.model }}, "add_ons": {{ form19.data.add_ons }}, "color": {{ form19.data.color }}, "zone": {{ form19.data.zone }}, "status": {{ form19.data.status }}, "serial_num": {{ form19.data.serial_num }}, "sales_order": {{ form19.data.sales_order}}, "invoice": {{ form19.data.invoice }}, "customer_name": {{ form19.data.customer_name }}, "destination": {{ form19.data.destination }}, "contact": {{ form19.data.contact }}, "delivery_contact": {{ form19.data.delivery_contact }}, "pymt": {{ form19.data.pymt }}, "comment": {{ form19.data.comment }} } ]

Both seem to effectively enter the data in the spreadsheet, but here is a screenshot of how it looks in the spreadsheet:

Something I just thought of while getting those screenshots, is it possible that the fact that some of the cells in the new rows are already populated with drop downs be causing the issue? I'll test and see.

[tomm]

@Tess,

I tested my theory and it still didn't work, so I don't what would be causing it.

[Tess]

Thanks! I have a similar set up and haven't been able to reproduce this bug

If you can share an export of the spreadsheet & app, happy to test

[Tess]

Generally speaking, it's always helpful to try hardcoding your input for troubleshooting. If you append only [{ship_date: '2024-12-13Т15:34:19.355-0500'}], does that work as expected?

[tomm]

@Tess, I went in and created a whole new spreadsheet to test with and the data went in with no problems, so it must have been the spreadsheet I was working with.

Thanks for your help though.

[Tess]

Interesting! Glad it's working now

[tomm]

Well, it was working yesterday. Now I'm getting an "Empty table update" error.

[Auke_Booij]

Hey, so from my experience (see also the thread linked by @Tess), the "Table detection algorithm" by Google Sheet's API (*) is very sensitive to empty rows. So just put a character such as . in the first column of every empty row and that might fix it.

(*) yeah sorry this is a thing and it's pretty complex and the correct solution to it is manually specifying the ranges, but as said this isn't getting prioritized