How can I query, from a worksheet cell, the last date and time a =JIRA() function was run?

[Hank]

As a viewer of data returned by the =JIRA() function from Jira Cloud for Sheets, I need to see the date and time that the displayed data was retrieved from Jira so that I can be informed on the timeliness/"Freshness"/age of the data relative to the current date and time. 

Example
Cell A1 Last Query Date:      Cell A2 =somefunction()  Cell A3 =JIRA()

[JimmyVanAU]

Hi Hank,

Great question. This one took a little tinkering, and is a function of the Google sheet, rather than the Jira function itself.

Tweaking https://webapps.stackexchange.com/questions/31894/google-spreadsheet-timestamp/31942#31942, I came up with the following.

In your Google sheet, go to Tools > Script Editor.

Then in Code.gs, try:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { // only updates relevant sheet
  var r = s.getActiveCell();
  if( r.getRow() == 3 && r.getColumn() == 1 ) { // only checks for updates in cell A3
  var lastUpdatedCell = r.offset(0, -1);
   var time = new Date();
   time = Utilities.formatDate(time, "GMT", "dd/MMM/yyyy HH:mm:ss");
   lastUpdatedCell.setValue(time);
  };
 };
}

Then run the Jira function in A3 and it should update.

Cheers, Jimmy

[Hank]

For some reason the =Jira() function does not trigger onEdit. Even when I monitor any cell in the sheet. 

OnEdit only runs when a user interacts with the sheet.