API POST - DueDate Formatting Issues

[Morris Coyle]

Hi All,

I am (way over my head) creating a Google Apps Script that will create an issue on completion of a Google Form.  I have almost everything working but cannot get the Due Date (or start date) to parse successfully.

When I force it via the script to use a made up date using "duedate": 2019-07-24T00:00:00.000Z  it works. However I seem incapable of taking the date from my google sheet and formattng it correctly.  

Has anyone got any ideas or had the same issue?

[Andy Heinzer]

I found a similar result from a stackoverflow post over on https://stackoverflow.com/questions/30492832/iso-8601-string-to-date-in-google-sheets-cell

However in that case, they want to do the opposite and convert these ISO8601 date/time/timezone values into more readable date/times.   However it gave me an idea for how we could use some of the functions built into google sheets to do something similar but in reverse in order to create these values that Jira will understand.

I would be interested to know more about what date values you have right now in your sheet.  Such as what format are you using to look at the date/time now?  In my testing I used a date value of yyyy-mm-dd for cell A1.  If you're using something else, the formula below might need to be tweaked a bit.

In my example, I had a date value in one column A, and I entered in a generic Timezone value to column B of the same row, so it looks like this:

A	B
2019-07-24	T00:00:00.000Z

Then in the Column C cell of the same row, I entered a formula of:

=CONCAT(MID(A1,1,10),B1)

This uses two different spread sheet functions to first pull a string from cell A1 (which is what MID does, we know will always be exactly 10 characters in length), and then CONCATENATE that with the timezone value in B1.   The result ends up being 2019-07-24T00:00:00.000Z in cell C1.  If all these entries have the same timezone, I would just set the same value for all of column B.

I hope this helps.

Andy