AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

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

Date recognition with Google Sheets source

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello,

In our agency, we have several several websites to monitor, and for each one we made a Data Studio, each connected to a few Google Sheets.

The Google Sheets source were duplicated, so for each GDS report, the sources are identical.

 

But for a few of the identical Google Sheets sources, GDS doesn't recognize the date colums as date. On the source association, it recognize them as text. I manually changed them as date (YYYYMMDD), but when I want to use them as time dimension, the table display "No data".

 

I read this forum and found a few answers like "Set the date format as YYYYMMDD on google sheets, works for me", but I tried different date format (see below), and none of them are good.

 

But more : when I display the date field in the table, he recognizes it as a date and formats it as "YYYY-MM-DD", even if it's formated as "DD-MM-YYYY" on the Sheet.

 

And even more : I played with the data range : by default it's set on "last trimester" on our report, and one of the date is 21/03/2017, therefore, it should be displayed, but as told higher, it isn't. But, if I set a custom range as, for exemple, "01-01-2016 to 31-12-2017", it's shown. I tried different range, and "31-12-2016 to 31-03-2017" works but not "01-01-2017"...

 

Any idea ?

 

The date format I tried :

  • DD/MM/YYYY
  • YYYYMMDD
  • YYYY-MM-DD
  • DD/MM/YYYY HH:ii:ss
  • YYYY, MM, DD on différent fields (GDS can't use them as date range)
  • DDMMYYYY

Date recognition with Google Sheets source

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

When you have the date in GS set as YYYYMMDD, have you also edited your dataset in DS to specifically look for the YYYYMMDD format? Sometimes it defaults to a number format instead of a date format.

Date recognition with Google Sheets source

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Yes, of course, I should have mentionned it in the first place, but thanks for noticing.

Date recognition with Google Sheets source

Participant ✭ ✭ ☆
# 4
Participant ✭ ✭ ☆

The other problem I ran into in my GS is that the formula I was using to create the YYYYMMDD format from a real date was dropping initial 0s in front of the month or day number. This would then make the dimension not work in DS.

 

Also, I don't know if DS changes their date format for your location. I notice above that you said you tried YYYYMMDD, but in your paragraph you write the date in the European way (day first). Just wanted to mention it in case something in the date flips it around in GS.

 

Does your data need to be broken down by day? Or can it be broken by month/year? If the latter would work, you could calculate month and year off the YYYYMMDD and use that as your x-axis. 

Date recognition with Google Sheets source

Follower ✭ ✭ ☆
# 5
Follower ✭ ✭ ☆

Have you tried MM/DD/YYYY in google sheets? 


Give it a go.