How to connect and use Looker Studio with Google Sheets 2023
When you start working with Looker Studio you may ask yourself “How do I connect a Google Sheets to Looker Studio?” I am here to explain! Keep up with the video tutorial to get a better understanding:
Google Sheets tips
I have a tab in Google Sheets that I need to connect to Looker Studio.
First of all, you need to choose a tab with data you want to present. I want to present Sales data in this case.
Important things: there should be a title in the first row, the tab shouldn’t have merged cells, Google Sheets needs to recognize the data in the table as numbers (consequently, there has to be no errors and blank spaces). If you don’t have data – present it as 0 but never as a blank space). By the way, I have an article about errors fixing – check it out.
In my case, I imported my data to Google Sheets and Transaction revenue was imported in text format. If you have a money amount that is presented in format – $1000, it can be recognized as text. What you need to do is to click “123” in the menu, and change the format from US Dollars to number. There is another way as well – just remove dollar signs everywhere. For your convenience, all the numbers in Google Sheets are aligned to the right, and other formats – to the left, so that is how you can distinguish when something is wrong.
NOTE: always make sure all the data in the column needs to be of one format. For example, in the Channel column there should not be any numeric fields as well as in number fields there should not be any text fields.
Connecting the tab
Having created your blank dashboard in Looker Studio, in the menu click Resource – Manage added data sources, or in another menu you can simply click Data. Here we have a list of connectors that we can use to add our data source. Among Google connectors find Google Sheets and click it. There you will see all your Sheets – Owned by me, Shared with me, Url to the Sheets or so on. After selecting your Sheets, you need to find your tab. Mine is called Sales Data so it makes no problem to find it. Next to each tab you have an icon, clicking which opens this tab in the Sheet in another browser tab.
We can use the first row as a header and I recommend you to use it. Also you can include hidden or filtered cells – usually I do include it. Another thing – we can use a specific data range that you will need to input manually in format A1:A or A:G.
It may seem really easy but everywhere are pitfalls and tricks.
Looker Studio Tricks
Let’s create a simple table.
The dimensions are customer type and channel and the metrics are transaction revenue, orders quantity and item quantity. We can play around with some data types. In Manage added data sources, we can see what we have – column name, column type, default aggregation type and description.
Here we can change the name of our data source, the fields and set them up. For example, surely channel and customer type has to be text, but it is better to present transaction revenue in currency. Why is this important? If the type of the field is number – you can do some calculations if you need, but if it is text, you cannot do it.
These are the types of aggregations you can choose from. It is really useful and important.
Date, as you can see, also can look various ways.
Besides that, when you add a data source, you can for various reports that often comes really handy. But if you need, you can set it up to use it only in this report or also the changes made to the fields will not be applied to this data source in other reports. Check it in Embedded.
Here we also have data credentials – ownership and viewers; data freshness, that you can also set up to get fresh data once in some time depending on your needs. For example, every 15 minutes, every hour, every 4 or 12 hours. In addition, you can add fields and parameters. Check out my article about parameters in Looker Studio here. One more interesting function here is filter by email and I also have an article about it, so check it out!
When you’re ready, click Done. Now let’s check out how our table looks.
If you want to get to know more about Looker Studio, I invite you to my Data Visualization in Looker Studio course. Start your journey in data visualization with Gaille Reports. You can get to know more about it here.
Let me know if you liked the article and don’t forget to subscribe to the newsletter!
Check out more articles of mine in my Medium.