Last refresh date for sheets from Jira

[Harpreet Kaur]

Hi, I've scheduled the data refresh to hourly in Google sheets using Jira Cloud for sheets. Now, I need a column in sheets that will tell me the date of the last refresh. Is it something that can be done?

Thanks!

[Earl McCutcheon · Accepted solution]

Hello @Harpreet Kaur ,

I believe the easiest way to accomplish this would be to implement a =NOW() formula in an unused cell to give you a date and time.  By default, the google spreadsheet will have the calculation settings set to update all the formulas on a sheet whenever there is an "on change" event (any edits are made or data is refreshed in a cell),  so any time the document refreshes there will be an update to the on change event and the =now() timestamp will update to the most recent time the document was refreshed.

This article gives some details on how to adjust the behavior a bit too, that may be helpful for your configuration if you wanted to adjust the refresh interval further:

• https://spreadsheetpoint.com/auto-refresh-google-sheets/

A possible downside is that Using the above method will trigger a timestamp on any edit made to the page, not just data import, so it would be best suited for a read-only page that was used for data dumps only,  if it is an active page where constant edits are made this would trigger a time stamp on any action performed by a user.

Alternatively, you could look into a custom Script to trigger the formula when another cell is populated with data, I found an example script in the following StackOverflow thread:

• https://stackoverflow.com/questions/11458470/automatic-timestamp-when-a-cell-is-filled-out

but using something like the following is a great starting point and adjust the scrip for your use case:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 4 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

Hope this helps.

Regards,
Earl