Automations Bringing Date-Timestamps from Google Sheets

[Mike_Williams2]

I am creating an automation to bring into AirTable records from a Google sheet that is being populated by Google forms. One of the fields is a Date-Timestamp field. However, even though the formatting is the same in both the spreadsheet and AirTable (namely, Date-Timestamp with the same formatting parameters), the Test Run yields an error that says, “Field ‘Date-Time’ cannot accept the provided value: Could not convert string to date.” Shouldn’t an AirTable automation designed to bring in a variety of fields from Google Sheets be able to bring in Date-Timestamp data without converting strings to dates? Is this a bug? Or am I doing something wrong? Thanks. Mike

[Mike_Williams2 · Accepted solution]

I just learned from AirTable support that, per an engineer, the date-time format used by Google forms is presently not directly compatible with the date-time formats of AirTable. In the meantime, I’ll figure out a way to make this a single-line text approach, instead.

[Bill_French]

Hi Mike, and welcome to the community!

Mike_Williams2:

the date-time format used by Google forms is presently not directly compatible with the date-time formats of AirTable

Which ostensibly makes the automation integration incomplete, or as some would say - busted. :winking_face:

Here’s how to get around it.

1. Build a script process in Google Apps Script that copies data from the Form target sheet into a new sheet.
2. In that process, transform the date column into a format that Airtable will accept.
3. Set that process up to run every five minutes.
4. Use the Airtable automation to pull from the copied sheet rather than the Google Form’s target sheet.

Separately, one must ask - is there a really good reason not to bypass all of the Google forms and sheets layers and use an Airtable form which would eliminate all of these issues and processing?

I suspect there’s good reason, but it’s worth knowing.

[ScottWorld]

Alternatively, you can use Integromat or Zapier to handle your automations for you. Both platforms offer a free tier so you probably won’t need to pay for them, and both platforms offer a built-in date/time formatting feature. (Note that Integromat has far more power, flexibility, and customizability than Zapier does, which is why I personally try to avoid Zapier whenever I can.)

Here’s the link on how easy this is with Integromat:

Integromat Support

Date & Time Functions

formatDate (date; format; [timezone]) When to use it: You have a Date value that you wish to convert (format) to a Text value (textual human readable representation) like 12-10-2019 20:30 or Aug 1...

(Note that the Integromat link contains an affiliate link of mine, so I would make a referral fee if you start paying for Integromat.)