Data Studio
4.7K members online now
4.7K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Insert "Date Last Updated" Cell into Data Studio

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I'm looking to insert a cell from Google Sheets into Data Studio that displays the date that my Google Sheet was last updated. The problem I'm having is that when I use the Scorecard element, I need to select both a dimension and a metric, but I can't convert my date range to a metric. Is there any workaround to this? I'm hoping that sometime in the future Data Studio will include the capability to add a single cell from Sheets, but until then I'm afraid I may be out of luck. 

Insert "Date Last Updated" Cell into Data Studio

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Shelly A

 

You are right, there isn't a way to simply include the contents of a cell, though this would be really useful!

 

Could you possibly make another tab on your Sheets doc, with a spoof dimension and your updated date as the metric, reference this cell in the correct place on your existing sheets, and add this as a new data source with just these two measures? It's a bit of a work around, but it should function?

 

Hope that helps,

Ben

Insert "Date Last Updated" Cell into Data Studio

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks for the response, Ben! I've tried exactly what you suggested with creating a dummy dimension. The problem is that Data Studio refuses to recognize a date as a metric. It will only accept it as a dimension unless I convert the date to a number, which defeats the purpose. 

 

I think I'll just have to be patient until they build the single-cell functionality!

Insert "Date Last Updated" Cell into Data Studio

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hi,

 

I think I found a workaround for that. Probably it's too much, but I really need it to be part of the Dashboard.

Add to the source table 2 dummy columns:

1) for LastUpdatedAt date and time;

2) Column called _ with " " (space) values in it (can also be any 0, etc).

 

I made it by adding to my BigQuery query the following:

 

SELECT

[rest of your query here]

CONCAT(STRING(CURRENT_DATE()),' ',STRING(CURRENT_TIME()),' UTC') AS LastUpdatedAt
, " " AS _

FROM

[rest of your query here]

 

*Note I used separate functions on Date&Hour, so it won't return milliseconds when casting to STRING. Also added Server TimeZone. You might decide you don't need it. Then you can simply use CURRENT_TIMESTAMP() function (For some reason, it shows timeZone in BQ output, but not in the spreadsheet I am using in between Data Studio and BQ).

 

When you add these columns in Data Studio, make sure it recognises these 2 columns as Text (for the date) and Number (for empty column with aggregation on Min/Max/Sum).

On your Dashboard, insert "Table" and choose LastUpdatedAt as Dimension and _ as Metric.

When Table appears, minimize the second column (with metric, showing 0) by grabbing delimiter line and moving it to the right.

Go to View Mode, make a screenshot of the background next to the place where you put the Table.

Go to Edit Mode and insert image, choose your screenshot, put it over the second column of the Table.

 

Done.

Hope it helped

 

Insert "Date Last Updated" Cell into Data Studio

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi

 

Thank you Elena

It is done. Perfect!

I put on my dashboard a table with the update date, the max of the date in my data (as date), and 1 for each line (as number/sum). So In the 1 shown line table, I have the update date and the number of projects.