Connecting Google Sheets to Looker: 4 Best Methods

Introduction

Google Looker is one of the best BI tools available and getting very popular. Backed by Google’s technology, it integrates well with other Google Cloud offerings, making it a viable option for your needs. One of the easiest methods to store your data is by using Google Sheets. Thus, pairing these two tools can be extremely beneficial for your business. In this blog post we will discuss various methods to connect Google Sheets to Looker.

Method 1 : Using In-built Features and Options

Looker has a feature named “Google Sheets Action” , using it one can select Google Sheets as a possible destination when sending or scheduling your “Looks” or “Explores”. Before sending to Google sheets, you can process data via horizontal and vertical scaling or overwriting any formulas to dynamically update a sheet and load data.

• Vertical Scaling : By default, a new Google sheet has 1000 rows in it. If your data in Looker has more than that, Looker will manage the length of the sheet on its own  and expand it vertically to add more than 1,000 rows.
• Horizontal scaling: By default, a new Google sheet has 26 columns in it, named A-Z. If you Looker data has more than 26 columns, you can manually expand your Google sheet to the number of columns you want, say 48, and Looker will manage the data transfer easily.
• Overwriting: If you’re updating an existing Google sheet with Looker data and that sheet has some existing formulas you want to keep, just write those formulas on a separate tab of the same target Google Sheet to preserve them. Looker will then post the data to the first cell of the first tab, in your destination Google sheet, preserving the logic in your formulae.
  
  This action allows you to schedule your transfer and specify if you want to overwrite existing sheets or create new sheets, with every data delivery. 

Sometimes you would want that the sheet fetches up-to-date data from Looker, only when you want to. For this to happen, you can add some custom Google functions inside your Google Sheet. 

E.g. Choose the cell where you want the Looker data to appear and select the “script editor” option from Tools menu. You can specify the function “LookerFetchData(url)” along with the embedded visualization/image/table to go with it. 

Another function you can use in your sheets is “ImportXML”. 

The above methods have a serious flip side, you have to save the Look with “public access” in order to bring it into Google Sheets. 

Method 2 : Using Looker APIs

Another method to connect Looker with Google sheets is through the Looker API. In the Google Apps Script, there is a feature called URL Fetch Service, which allows a sheet to access other resources on the web via their URL. Using the script editor in your sheet, you can provide parameters to the inbuilt LOOKER_RUN_LOOK function and the data/SQL will be fetched into your sheet. You must know some basic programming to be able to use this properly. 

Another issue to be careful about is that you will have to provide your Looker credentials for the function to run and these credentials will be available to anyone who has access to this sheet. 

E.g. Develop a Google Apps Script which uses Looker API, to fetch data from Looker. 

Inside your sheet, go to Tools > Script editor and copy-paste the script below:

function LOOKER_MY_ENDPOINT(input) {
var options = {
 'method': 'get',
 'headers': {
 'Authorization': 'token ' + token
 }
 };
var response = UrlFetchApp.fetch("https://Your_Looker_Instance_Name.looker.com/xyz/abc" + input, options);
return Utilities.parseCsv(response.getContentText())
 }

Method 3:  Using Script Editor and MySQL

So far we have seen how we can move data from Looker to Google Sheets. Suppose you want to input data into Looker from your Google Sheets. A simple way to do this is to write a script that will first push data into a MySQL database. Your MySQL database should have predefined tables with the correct columns and data types to absorb your Google Sheets data.
Write a script, using the “Script Editor”, option that will define a database URL, read your sheets columns+data in CSV format, and then export it to the database.
Or create an INSERT for every row of your spreadsheet and execute it on your MySQL table.

Even a non-technical user can use this predefined script function to export Google Sheets data into MySQL. Be careful though, your database username and password would be visible to all non-technical users if they open the source of your script.

Method 4: Using Hevo, a Fully Managed Platform

If you want to avoid the above manual scripting methods, Hevo provides you with an automated way to move data from Google Sheets to cloud data warehouses such as Redshift, BigQuery, Snowflake and more.  
Hevo can also automate many of your pre-processing tasks, like aggregation/localization/sifting,  and prepare your data to be readily consumed by your warehouse.

Now you can transfer data from Google Sheets to your target Destination for Free using Hevo!

Check out what makes Hevo amazing:

SIGN UP for a 14-day free trial and see the difference!

Conclusion

To conclude, we have discussed some manual and programming methods of to and fro data transfer from Google Sheets to Looker and tried to find a better way to accomplish the same via a powerful and reliable tool- Hevo. You can now transfer data from sources like Google Sheets to your target destination for Free using Hevo!

visit our website to explore hevo[/hevoButton]

Have any further queries? Let us know in the comments section below.