Hi @ScottK 

Welcome to the Community! I'm sorry you’re having trouble with your Zap.

I’m unclear about the issue. What result do you expect from the Zap?

The value from the date every other time either a test is ran, or an actual order is received, shows a currency value when it should always show a date. 

I thought I was pretty clear on that through the screenshots and description.

Basically the date is coming out as 04/26/2023 the first time

then the date is coming out as $45,041.00 the 2nd time.

Date is correct 3rd time, then is a currency the 4th time. 

It is not a Google Sheets issue. The value in the test in Zapier shows a date the first time, then a currency value the 2nd time.

Hi there @ScottK,

I’m jumping in to see if I can help!

Can you tell me if there have been any changes made to the spreadsheet? The following changes to a sheet while a Zap is active can cause errors:

• Deleting existing rows or columns
• Adding rows anywhere other than to the bottom of your sheet
• Resorting the sheet 
• Renaming/adding/rearranging new columns

If you need to make any of those changes to your Google Sheet, you will want to turn your Zap off while you make the change, and then turn it back on again after.

This is also mentioned on our help article here: Common Problems with Google Sheets on Zapier

Hopefully, this helps! 

Hi @ScottK 

Good question.

We’d need to see screenshots from the Zap Runs DATA OUT tab: https://zapier.com/app/history/

It could be due to how the column/cell is formatted in GSheets that’s causing the issue.

An alternative would be to use Airtable instead of GSheets.

Airtable has standardized field types. (e.g. currency, date, dropdown, etc.)

It was a new sheet in a spreadsheet created prior to setting up the Zap.

Before publishing the spreadsheet, I tested out the Zap and it was successful. 

I then deleted the test row from the spreadsheet, then published it.

An order came in where it correctly added it with the properly formatted date.

A 2nd order came in and this time the date was not formatted correctly, and was instead displaying as a currency value.

I went back into the Zap and ran 2 consecutive tests on the action of creating a new row in Google Sheets. The result was again a correctly formatted first row date and a incorrectly formatted 2nd row date.

I decided to make a small change to the date formatter, thinking this might be the issue.

I tested it and it showed the date correctly.

I then ran the test again, not changing anything, just running the test again, and again it showed as a currency.

When I viewed the test results in the Zap itself, it had the value correct in the first test, and value incorrect in the 2nd test.

I have included a screenshot of the value in the 2nd test showing COL$B: $45,041.00

and another test showing COL$B: 04/25/2023

again, nothing has changed between the tests, just re-running the same test again.

I have also included a screenshot of a test ran of the date formatter showing the correct format. I ran this multiple times and the result is always the same. 

@ScottK 

Turn the Zap ON and test, then check your Zap Runs: https://zapier.com/app/history/

You can see the DATA IN and DATA OUT.

The DATA IN on the GSheet step would be how data is sent from the Formatter step to the GSheet.

The DATA OUT on the GSheet step would be how GSheet handled and returned that data.

If there is a mismatch, then the issue is with the GSheet itself (possibly how the column/cell is configured for data formatting) and not the Zap configuration.

I just set up a whole new sheet, with just the column names

Ran the test again, and it is still doing the same thing

Checking the history. It shows the first order did write the date correctly, but the 2nd order did not

I have included a “Data in” and Data out” screenshot of both the correct and incorrectly formatted date.

However, the date formatter on the order with the incorrect date value does show it formatted it correctly.

I do not see how it could be Google Sheets, if it’s a brand new sheet.

I even went as far as creating a whole new spreadsheet, put the column names in, and ran the test again twice. Again with the same result of the 2nd test incorrectly formatting the date.

I do not see tests in the Zap history, only actual orders that triggered the Zap

@ScottK

If the data pass in to GSheet is as expected (DATA IN), but the data returned from GSheet is not as expected (DATA OUT), then the issue is with how GSheets is interpreting and formatting the value.

This can happen especially with dates.

Want to call this out for the Formatter step.

The input contains milliseconds (e.g. .542)

The expected format does not expect milliseconds, which may be contributing to the issue.

An alternative would be to use Airtable instead of GSheets.

Airtable has standardized field types. (e.g. currency, date, dropdown, etc.)

Even adding the “sub-second” did not make a difference

How can you explain the fact that it is not every test? It’s only consistently every other test. I can run the test 10 times and 5 out of the 10 it is correct

If it was consistently putting in an incorrectly formatted date, i could argue that yes, it’s a Google Sheets issue, but if you look at the screenshot of me running the test multiple times, you will see it is every other row.

Also I highlighted the value for the first written row showing what the value was written “45041”

Airtable is not an option

Hey there @ScottK,

Thank you for the extra clarification here.

It looks like this is a spreadsheet-specific date format issue; it's likely that the column hasn't been fully formatted for dates. To address this issue you can select that column in your Google spreadsheet, and reformat it using the Format > Number menu options at the top of the Google spreadsheet.

Please give that a try, and keep us posted.

Thanks! 

I did try using AirTable and did get it to work correctly. 

I get that it might be a Google Sheets issue, but it honestly makes no sense.

AirTable is not an option only because my client does not want to use it. They understand Google Sheets and Excel and they are having difficulty getting used to it

@ScottK 

Try using a different date format for GSheets, for example: YYYY-MM-DD