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

Creating a Time Series from Google Sheet

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I'm trying to create a time series chart from the data source of a Google Sheet. I cannot get the right layout for dimension, breakdown and metrics - just keep getting invalid or no options on dimension. Only options within the breakdown of dimension and metrics. Any suggestions on how to lay out the data in the sheet?

1 Expert replyverified_user

Creating a Time Series from Google Sheet

Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

Hi Katrina, 

 

I've had more problems with the date and time dimensions in Data Studio than probably any other feature. The native Google Analytics connection works well enough, but everything else is still pretty messy. 

 

The issue has to do with how Data Studio is processing the date column of your sheet. My guess would be if you open up the calculated dimension/metric dashboard, that the field will be formatted as "Text". If you tried to just reformat it manually to the correct date and time format, you'd get a pop-up notification that it doesn't recognize the format and might render unexpected results, which will result in it appearing as a time dimension, but still render an error in your chart. 

 

I initially tried to fix this with the TODATE(Date,'%Y/%m/%d','%Y%m%d') function, but it didn't work either. It processed as a date, but though all of my dates were from 2016 some of them were randomly processing as dates in 2015... 

 

I eventually solved this by simply switching the date format in my Google Sheet to a custom format that matches Data Studio YYYYMMDD, and then reconnecting and refreshing the sheet. 

 

Hope this works for you!

 

 

Creating a Time Series from Google Sheet

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Lindsey & Katrina,

 

I encounter the same issue here, and I cannot find a way around it. It seems that, as a french user of G Sheet, the date in my reports are set up in dd/mm/yyyy format, even if I switch the date format manually. In fact, the "format" option only modify the way it appears to the viewer, but the formula inside each cell is never modified and remains in the french format.

 

As a consequence I'm fully unable to use Data Studio with my Sheet reports :-( 

 

Re: Creating a Time Series from Google Sheet

[ Edited ]
Follower ✭ ✭ ✭
# 4
Follower ✭ ✭ ✭

hey guys,

 

a) create seperate columns in Google Sheets for YYYY / MM / DD. 

 

b) Both the MM & DDD cols formatted as =TEXT('1',"01") / =TEXT('2',"02") / =TEXT('3',"03").. and so on, so as to align formatting with that in Data Studio 

(attach screenshot, how I've formatted it in Google Sheets, and also DataSet settings in Data Studio and the chart settings)

 

Hope this helps!

 

GA-Spreadsheet_Plugin-T-Mob-1_-_Google_Sheets_and_Reply_to_Message_-_The_Google_Advertiser_Community.png

 

GA-Spreadsheet_Plugin-T-Mob-1_-_Date_Series_and_Refresh__reconnect__or_reset_a_data_source_-_Data_Studio__Beta__Help.pngreport_2.png

Creating a Time Series from Google Sheet

Follower ✭ ✭ ☆
# 5
Follower ✭ ✭ ☆

Hi

 

The date is working and formatting on my Google Sheet data source but why can't you do date range comparisons? Is the feature just not enabled from Google sheets?

 

Rob

Re: Creating a Time Series from Google Sheet

Follower ✭ ✭ ✭
# 6
Follower ✭ ✭ ✭

Hi Rob,

 

Conceptually speaking, I think as long as the Data Source has a valid date data the Date Comparison SHOULD work!

I tried in my example above (Data Source is Google Sheets, and it does do the comparison.

 

report_2.png

Creating a Time Series from Google Sheet

Google Employee
# 7
Google Employee

Hey guys,

 

To do date comparisons, the Date Range Dimension in your reports needs to be the YYYYMMDD format. But your actual date data in Sheets can be in another valid format. E.g. in sheet, my dates are in MM/DD/YY. But my Date dimension in the data source is YYYYMMDD. I can then use that as my Date Range Dimension in the chart or report. 

 

Hope that helps!

 

Rick