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
1.8K members online now
1.8K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Problem with Date Range using Google Sheet

Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

I've been using a Google Sheet as a data source for several months with no issues. On Monday, I added some new columns and changed a few of the existing ones. I refreshed my fields and accepted the changes but I was still getting errors on my dashboard that stated that changes had been detected and I needed to refresh my fields. No matter how many times I refreshed my fields, or reconnected the data source (or restarted my browser, or logged out and back in), I couldn't seem to get rid of this error message.

 

So I removed the data source and added it again. I added the new data source back to the dashboard elements that had previously given the error message but now instead of the error message, it said No Data. If I removed the date range dimension, it would give a value so it's clear that it is reading the data from the sheet but for some reason has stopped recognising the date dimension.

 

In Google Sheets, this column is formatted to YYYY-MM-DD and the field in Data Studio is set to Date (YYYY-MM-DD). This is that same as when the sheet was working perfectly so I don't know why this would cause issues now.

 

I've spent two days trying to get this to work. I've created a new report with just the sheet as a data source, I've added GA as a second data source (and this does report data based on the date range correctly), I've refreshed and reconnected so many times. I've checked each individual element and ensured that it's using the correct data source, dimension and metric.

 

I'm finding this very frustrating! Any ideas?

 

Thanks!

Chris

Problem with Date Range using Google Sheet

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Where do you get your data from originally? From the GA plugin in sheets? 

Problem with Date Range using Google Sheet

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

No, I'm using GA directly where I take data from that.

 

We discovered disparity between what we see in GA compared to what we actually receipt in the backend so the data on the sheet is exported from our backend (and then tidied up a bit).

Problem with Date Range using Google Sheet

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

I had a similar problem with our of my report where the date format was the same in DS as in my Sheets's data source. However, I discovered that I needed to fix the date format on every other sheets of the spreadsheet where I took my data for the main sheet - aka the data source (I'm doing a lot of referencing data).

Problem with Date Range using Google Sheet

Follower ✭ ☆ ☆
# 5
Follower ✭ ☆ ☆

Ah, I'm only working off of one sheet and there's only one date column that I've definitely checked and rechecked the format of. Thanks for the suggestion though Smiley Happy

Problem with Date Range using Google Sheet

Explorer ✭ ✭ ✭
# 6
Explorer ✭ ✭ ✭

Hmmm. What does your chart looks like? You tried with different date formats and different chart types?

Re: Problem with Date Range using Google Sheet

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆

So, here's how it looks like currently:

 

 GDataStudio1.jpg

If I remove the date range dimension, it does pull in data but obviously not too helpful without the date range.

 

GDataStudio2.jpg

I've also got line graphs that are using this data source but they just look like this:

GDataStudio3.jpg

Oddly though, if I remove the data range dimension from this, it starts listing the dates as December but there's no data for December in the sheet so it's reading the date format somehow but not as anything useful.

GDataStudio4.jpg

Not sure if that really helps to be honest but there you go!

Problem with Date Range using Google Sheet

Follower ✭ ✭ ☆
# 8
Follower ✭ ✭ ☆

Hi Chris can you please share a google sheet which was working for you?

Problem with Date Range using Google Sheet

Explorer ✭ ✭ ✭
# 9
Explorer ✭ ✭ ✭

And what about if the date filter is set to a report-level? Just asking 'cause on a few later threads. it was the problem. 

Problem with Date Range using Google Sheet

[ Edited ]
Follower ✭ ☆ ☆
# 10
Follower ✭ ☆ ☆

@Albert Z: The only difference to the sheet from when it was working to now when it's not working is that I changed two column headers and added a few more columns. There hasn't been any changes to the date column or the other data that was already working fine. I'm not sure how to share the sheet itself without data protection issues but if I can contact you directly to discuss, that could help!