I created a simple report to track the funds raised for our primary school. Just a pie chart that picks data from a Google Sheets being maintained by the treasurer. There is now a request to add a timestamp on the screen (the report is being shared by taking a screenshot and sharing in various social media platforms to report progress).
After some Googling I couldn't find a function that returns current time. Something like =now() in Sheets. However, it was recommended to try create the time in the source data. So I created a field that stores the value of the current time in Google Sheets using this function:
=(now()-date(1970,1,1))*86400
I also set the Sheets to refresh every minute. The Sheets works perfectly.
On the Studio side I added a calculated field to display the time in my preferred format:
TODATE(Amount,'SECONDS','%H:%M:%S on %d %b %Y')
Again, this conversion works perfectly.
The problem is that the time refresh doesn't work on the report side. No matter how many times I refresh the data it still doesn't pick the updated time from the source. Yet the source sheet has the updated time.
As far as I can tell, the worksheet time update has to be triggered manually for the report to be updated.
That negates the whole purpose of the timestamp.
Sharing the report directly from Google Studio is not a practical option for now. Still, I have shared the report.
