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

How to encode time/hour/weekday in Google Sheets so that Data Studio will recognize the data type

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello. I have a large set of data in a Google Sheets worksheet, which includes a date and timestamp column. I can make as many additional calculated columns in it as I need. My goal is to get Data Studio to recognize the hour and the day of the week as dimensions. For the hour, I have tried adding formula columns with =weekday, =timevalue, =time, and =hour and all of those values display correctly in Sheets so I know I've used the formulas correctly, but they all come over to Data Studio as generic "text" and when I try to tell DS that, for instance, the Weekday is "Day of Week (D)" I'm told that "we couldn't recognise its format ... will cause unexpected behavior...".

I get the same error for all of my time/hour/weekday columns.

I have even tried to make text columns in Sheets that literally match as text strings the format the software and the help pages tell me (such as using YYYYMMDDHH for hours, or formatting the hours exactly as ##PM and ##AM), but nothing works.

Can someone please tell me exactly how I should have my columns in Sheets formatted for Data Studio to recognize one as the hour of the day (24-hour time is fine) and one as the day of week (either Sunday, etc. or numbered with 1=Sunday etc is fine)?

Thank you!

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Melissa B
December 2016

How to encode time/hour/weekday in Google Sheets so that Data Studio will recognize the data type

Google Employee
# 2
Google Employee

Hi Melissa,

 

 

It looks like you are on the right track, but that error message is confusing, I know. I would say you can ignore that and see if the data comes through into your charts the way you expect. 

 

For example, you can have a Date column in your sheet, with the timestamp format. But make the dimension type in your Data Studio data source Date (YYYYMMDD). Use that for your date range dimension (the dimension Data Studio will use to limit the time frame of the report).

 

For your other time and breakdown dimensions, you can do what you're doing (e.g. in Sheets, extract the various date parts with functions like Day, Weeknum, Month, etc.) into separate columns. In your data source, use the Date format options to set the type to what you want (but honestly, I think your charts might still work if you leave them as numbers for things like Day, Weeknum, etc. If Data Studio sets them to text, just change them to number.)

 

We are working on improving the date detection in the Sheets connector, so hopefully, that error message will go away soon.

 

Hope this helps!

 

Rick

 

 

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Melissa B
December 2016

How to encode time/hour/weekday in Google Sheets so that Data Studio will recognize the data type

Google Employee
# 2
Google Employee

Hi Melissa,

 

 

It looks like you are on the right track, but that error message is confusing, I know. I would say you can ignore that and see if the data comes through into your charts the way you expect. 

 

For example, you can have a Date column in your sheet, with the timestamp format. But make the dimension type in your Data Studio data source Date (YYYYMMDD). Use that for your date range dimension (the dimension Data Studio will use to limit the time frame of the report).

 

For your other time and breakdown dimensions, you can do what you're doing (e.g. in Sheets, extract the various date parts with functions like Day, Weeknum, Month, etc.) into separate columns. In your data source, use the Date format options to set the type to what you want (but honestly, I think your charts might still work if you leave them as numbers for things like Day, Weeknum, etc. If Data Studio sets them to text, just change them to number.)

 

We are working on improving the date detection in the Sheets connector, so hopefully, that error message will go away soon.

 

Hope this helps!

 

Rick