Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Last refresh date for sheets from Jira

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!

1 answer

0 votes

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:

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:

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you