Problem/Motivation
When sending submission metadata to Google Sheets, the Webform module sends submission created, submitted, and changed times as unix timestamps. Google Sheets does not understand these as a datetime format, and so the format cannot be changed using the Google Sheets cell format. As timestamps are just large numbers, humans are generally unable to read them and third-party systems are unable to parse (for example Google Data Studio).
Proposed resolution
The forthcoming patch adds user settings to the Webform Google Sheets Handler to allow for these timestamps to be converted before being sent to Google Sheets. The design pattern is modeled after the date format options in Views: the site-wide configured date formats are pulled into a select menu, with a 'custom' format option appended. This relies upon core's DateFormatterInterface and EntityStorageInterface, and both are added to the instance of the handler class.
User interface changes
A checkbox is added to the handler configuration form to enable date-time conversion:
When checked, a drop-down select menu appears with the sites configured date-time formats. If the "custom" option is selected, a textfield appears to enter a custom PHP datetime format string:
The description/help for these new fields read, respectively:
"Webform submissions include unix timestamps for created, completed, and changed date-times. Check this box to convert these to a date-time format Google Sheets recognizes."
"A user-defined date format. See the PHP manual for available options."
Data model changes
Three new variables are added to the configuration schema:
- convert_date: a boolean to store whether or not this feature is enabled.
- date_format: a string to store the selected date format name.
- custom_date_format: a string to store the user-entered custom date format.