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

Error when Connecting Google Sheets data that is a Cell Reference

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I want to display a time series. I have data in 3 columns Date, Production1 and Production2. When I enter the actual numbers in those columns Data Studio creates the graph. However, If the sheet contains cell references it doesn't work. I need to use cell references to transpose the day into a form recognised by Data Studio.

 

Examples Of what doesn't work:

 

in Google Sheets:

='Production Data'1:1

=TRANSPOSE('Production Data'1:1)

=A1

=A2

=A3

 

 

 

 

Error when Connecting Google Sheets data that is a Cell Reference

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

Could you provide an example with the values you're seeing? I'm having trouble visualizing this.

Error when Connecting Google Sheets data that is a Cell Reference

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

google sheets - data for DataStudio using transpose

 

If I paste values into a new sheet the data pulls from google sheets and the graph works. If I pull from this sheet it doesn't.

Error when Connecting Google Sheets data that is a Cell Reference

Participant ✭ ✭ ☆
# 4
Participant ✭ ✭ ☆

Where does the daily production number come from? Is it referencing another worksheet?

Error when Connecting Google Sheets data that is a Cell Reference

Participant ✭ ✭ ☆
# 5
Participant ✭ ✭ ☆

Rather, I can see it's referencing the daily production sheet, but what's on there? Just a list of dates?

Error when Connecting Google Sheets data that is a Cell Reference

Participant ✭ ✭ ☆
# 6
Participant ✭ ✭ ☆

If your Daily Production sheet is dates and data in a format you download, what you could do is set up the sheet that feeds to DS with sumifs. For instance, if you drag down the dates to include all dates, then your columns B and C do a sumif of your download sheet to find the values on those dates:

 

=sumifs('Daily Production'!A:A,A2,'Daily Production'!B:B)

Error when Connecting Google Sheets data that is a Cell Reference

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Raw data Entered each day by adding a new column

 

Here is the raw data. Each day a new column is added and that days information is entered. To work with Data Studio I need to transpose the data with the headings in the first row. If I simply use the transpose function on a second sheet the data looks correct, but I get an error from Data Studio. However, if I copy and paste the values into a third sheet and use that as the Data Studio source it works.