Last refresh date for sheets from Jira

Harpreet Kaur_mz7vra

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

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:

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

Hope this helps.

Regards,
Earl