I’m trying to use the examples given in the article:
https://ics.agical.io/?subject=Meet%20{{company.Account Owner First Name}}&organizer=Sandy&reminder=45&location=Sandy%27s%20Desk&dtstart=2016-10-26T15:00:00-04:00&dtend=2016-10-26T16:00:00-04:00&attach=http://www.example.com/
Does it return a JSON?
Apologies for these VERY basic questions. I’m new… 
BTW - this tool is still asking for ISO date, which I still cannot create from the output of the “event picker”.
You don’t need to return a JSON. Construct the URL using a Construct URL column directly, and that URL alone should work. You don’t need to fetch anything from an API, if that’s what you’re asking.
Regarding ISO, if you convert your event picker columns to a text column I think that should be enough. The underlying data written to those start and end columns is still ISO.
No luck… 
What happens if you point your event picker to a non-user specific text column, or create new user specific text columns. Just wondering if something is stuck because you had selected user specific date type columns initially.
Same…
And same here as well
Have you tried clearing the columns and start picking times again with the newly created ones?
And what do you get when you try toISOString with those?
I guess I don’t understand the reasoning behind it, but if I write a date to a column that’s in a google sheet, then it will store the date in ISO format. If the column type is a date, then it’s formatted nicely. If the column type is text then it shows as ISO.
When I try to do the same thing using a glide table, then it seems to store the date in a nicely formatted way regardless if the column type is text or date.
I honestly don’t know what’s happening on the back end for there to be a difference between google sheets and glide tables. I always assumed that the ISO format was the true underlying format regardless, unless you manually typed in a date instead of using one of the pickers.
Maybe there is something deeper here and glide tables can better handle dates, so it’s being stored as a true Date data type in the database, unlike google sheets, where there is no true datatable data types, so they had to force it to store in ISO format to work around all of the weird issues we had a couple of years ago where dates stored in google sheets would get weird depending on your regional settings. In reality, google sheet columns are all just text columns and glide has to make a best guess interpretation of the data type, based on content, either automatically, or by you setting the data type in the data editor. Glide writing the ISO format was probably a workaround on glide’s part to handle that weirdness with dates back then. Glide tables are better suited for working with dates, since I assume it’s true database underneath, instead of a google sheet.
Doesn’t help with your case though. Your best bet would probably be to rebuild the ISO format yourself by using a bunch of math columns and a template column to piece the separate parts together.
But it seems that it is NOT written in ISO in my case, whereas you’re saying (as well as many others here) that it does write to ISO. Is there something I’m doing wrong? Something to configure?
Like I explained above…I’m assuming you are using a glide table as opposed to a google sheet. I could not get the ISO format in a glide table. But, I could get the ISO format when writing to a google sheet. Like I said, glide tables probably handle dates differently and actually store dates as dates instead of storing dates as text, like it has to do with google sheets.
Again, you will probably have to recreate the ISO formatting yourself if you are going to keep using glide tables, or you can use a google sheet as your data source instead.
That’s what I’ve seen also, and it has its own set of troubles when working with dates!
I understand, but isn’t it a bug (or at least an issue) that we cannot convert this value to ISO using any of the built-in tools? Even a JS column should work, but I can’t that to covert as well
breaking it down with math column seems like an overkill
ISO format was never a “feature”. It was a hack job to fix the multitude if issues that we used to have a couple of years ago, when working with dates and google sheets across multiple regions/locales.
The google sheet locale would fight with the user’s locale and totally screw up dates. Glide changed it so the ISO format would be written to the google sheet, since it’s internationally recognized. It was to fix a problem brought on by google sheets. It was not meant to be a format for the end user to view.
So, no, I don’t consider it a bug, because glide never meant for the ISO format to be something that we work with as the end user. It was to fix a back end issue.
It would help if you shared which built in tools or JS code you have attempted to use. If you didn’t want to break it down and piece an ISO timestamp together in a template, then I would think you could still reliably get away with a single math column to get a single number, and a JS column to inject the missing pieces. Either way, I don’t think it would be that big of a deal to just build out the ISO format.
Higher up this post we debated which JS would work. As far as I can tell, none of these tools can accept the Glide table date as an input (p1 in the JS column).
Here is a math column and a javascript column to build it out.
Use the math column to convert the date into a number.
YEAR(Date)*10000000000
+
MONTH(Date)*100000000
+
DAY(Date)*1000000
+
HOUR(Date)*10000
+
MINUTE(Date)*100
+
SECOND(Date)
Use the javascript column to split it up and piece it together with the extra characters inserted to build the ISO format.
var str = p1.toString();
return str.substr(0,4) +
'-' +
str.substr(4,2) +
'-' +
str.substr(6,2) +
'T' +
str.substr(8,2) +
':' +
str.substr(10,2) +
':' +
str.substr(12,2) +
'.000Z';
Unless someone knows of the math formula to pull out the milliseconds, the best I can do is get accuracy down to seconds. Hopefully you don’t need that.
That’s PERFECT!
Thanks a million!