Starting in Looker 7.4, you can export data directly to Google Sheets using the Google Sheets action from the Looker Action Hub rather than implementing the method described in this article. See the Looker Actions — Google Sheets Help Center article for more information.

If you're occasionally using Google Sheets to perform on-the-go calculations based on data in Looker, you have probably run into this issue: for the data to be available, you need to save the Look as public in order to export it into Google Sheets. While this is still a great way to bring data from Looker into Google Sheets (see the Public Sharing, Importing, and Embedding documentation), you can also use the Looker API for this. This is the cool part — you can do it for any Look you have access to without having to enable public access.

The Google Apps script has the built-in URL Fetch Service, which allows us to make HTTP requests straight from our spreadsheet.

In this example, we will define a custom function in Sheets that allows us to import the results from any Look, based on the API credentials of the user.

You can find the latest version of the script on Github.

The Steps

1. Open your spreadsheet and navigate to Tools > Script editor.
2. In a new project, copy and paste the full script below in the editor; you can remove the placeholder function myFunction().
3. At the top, change the base URL to your company domain for Looker, and add in your API credentials (client ID and secret).
   Note: It's important to set appropriate limits on the API credentials of the user tied to the script, because the same credentials will be accessible to anyone who accesses the sheet.
4. Click save and give the project a name.
5. Navigate back to your spreadsheet and start typing LOOKER_RUN_LOOK().

You should see:

  =LOOKER_RUN_LOOK(look_id, format, query_limit)

where:

• look_id ( number ) — The ID of the Look, for example, 345.
• format ( number, optional ) — The requested format. The number 1 returns data; the number 2 returns the underlying SQL. The default is 1 (data).
• query_limit ( number, optional ) — The query limit. Defaults to 5000 if left empty.

You now have the custom function available in your spreadsheet:

You will see the second parameter taking either 1 or 2 as input. In this function, 1 is used for the results, and 2 is used for the underlying SQL query.

Now, in this spreadsheet, you can query the results of every Look you have access to in Looker!

The Full Script

NOTE: The following script uses Looker API 3.1, which is the default API for Looker 6.12+. For those using Looker API 3.0, please see Migrating to a New API Version for tips on migrating application code to use a new Looker API version.

// Replace this with your base domain e.g. https://instance_name.looker.com:19999/api/3.1
var BASE_URL = 'https://instance_name.looker.com:19999/api/3.1';
// Replace this with your API credentials
var CLIENT_ID = 'XXX';
var CLIENT_SECRET = 'XXX';

/**
 * Returns the results or the sql of a Look
 *
 * @param {number} id The unique ID of the Look
 * @param {number} opt_format 1 for csv, 2 for raw sql - defaults to csv (optional)
 * @param {number} opt_limit the query limit - defaults to 5000 if empty (optional)
 * @return The Look results data
 * @customfunction
 */
function LOOKER_RUN_LOOK(id, opt_format, opt_limit) {
 try {
 var options = {
 "method": "get",
 "headers": {
 "Authorization": "token " + login()
 }
 };

// set formatting to either csv or the raw sql query since sheets is limited
 var formatting;
 // convert param
 switch (opt_format) {
 case 1:
 formatting = "csv";
 break;
 case 2:
 formatting = "sql";
 break;
 default:
 formatting = "csv";
 }

// set a custom limit
 var limit;
 if(opt_limit) {
 limit = opt_limit;
 // else use the 5k default
 } else if (opt_limit == -1) {
 limit = -1;
 } else {
 limit = 5000;
 }

// get request for the look
 var response = UrlFetchApp.fetch(BASE_URL + "/looks/" + id + "/run/" + formatting + "?limit=" + limit, options);

// if it's csv, fill it in the cells, if it's the query, use one cell only, if not specified, throw error
 if (opt_format == 1) {
 return Utilities.parseCsv(response.getContentText());
 } else if (opt_format == 2)
 {
 return response.getContentText();
 }
 else {
 return Utilities.parseCsv(response.getContentText());
 }
 } catch (err) {
 return "Uh oh! Something went wrong. Check your API credentials and if you're passing the correct parameters and that your Look exists!";
 }
}

function login() {
 try{
 var post = {
 'method': 'post'
 };
 var response = UrlFetchApp.fetch(BASE_URL + "/login?client_id=" + CLIENT_ID + "&client_secret=" + CLIENT_SECRET, post);
 return JSON.parse(response.getContentText()).access_token;
 } catch(err) {
 return "Could not login to Looker. " + err
 }
}

Other Potential Uses

Since we are using the Looker API, there are a lot more possible use cases.

This Github repository contains a few more functions that follow the same pattern. You can add custom functions yourself, too; for example, a basic HTTP GET request could be written as follows:

function LOOKER_OTHER_ENDPOINT(input) {

var options = {
 'method': 'get',
 'headers': {
 'Authorization': 'token ' + token
 }
 };

var response = UrlFetchApp.fetch("https://instance_name.looker.com/this/that" + input, options);

return Utilities.parseCsv(response.getContentText())
 }

Reference Material

• Google Apps Script — URL Fetch Service documentation
• Looker API documentation