Jira Cloud for Sheets - How to get the columns from the board and the date the issue entered the col

[José Arrecio]

Hi

I am using Jira Cloud for Sheets, and I can connect to Jira and extract a JQL query.

I want to know how to get the columns from the board and the date the issue entered the column, lets say I have a column "ready for production" and issue #5 entered int 18/10/2021 and issue #6 entered it 11/10/2021. Id like to get the name of the column as the table ´s header and the dates as the cells.

Thanks

[Earl McCutcheon]

Hello @José Arrecio ,

In JQL results used by the Jira Cloud for Google Sheets (Official)  app the values returned are the field value only and do not contain any history items about the field.  So the value returned is the current value only.  

If you wanted to get field history items you would need to look into custom scripting using the API with something like a call on an issue and expand the changelog via:

• GET /rest/api/3/issue/{issueIdOrKey}?expand=changelog

Another option would be to create an automation rule to track the desired values in some way.  I am thinking one method would be to create a text field type, custom field to track details about the history of the other field, and populate the values via Automation rules.  you could possibly do something like:

• When: Value changed for 
  • "ready for production"
• Create Variable
  • Create variable
    • tmpValue
    • {{issue.ready for production}}
  • *This step will copy the previous value of the custom field, and make it a temporary value as the next step will overwrite the field by inserting the temporary value while appending additional values specified
• Then Edit issue Fields:
  • Choose the New custom field you created for tracking the history of the field
  • set the field with smart values to something like the following:
    • {{tmpValue}}
      Field "{{issue.ready for production.name}}" updated by "{{initiator.displayName}}" on "{{now}}"

This would populate text that includes the field previous value and the name of the user that triggered the change and a timestamp in the format:

previous value of the field inserted here from tmpValue avariable
Field "Ready for production"  updated by "Example User" on YYYY-MM-DDThh:mm:ss.0+0000

Then when setting up your sheet you would have the txt values in one column containing these details Additional details on the concepts used for the example automation rule above can be found in the following sections of the automation documentation:

• What are smart values?
• Smart values - general
• Smart values - text fields
•  Smart values - date and time functions
• Automation Actions: Create variable 

Regards,
Earl