User Guide

Getting Started

Help Center

Documentation

Community

Training

Certification

Articles in this section

See more

Generating a Data Dictionary in Google Sheets

Avatar
Romain
  • Updated
Follow

Why Do I Need a Data Dictionary in Google Sheets?

While Looker empowers more users to access and explore data, the meanings of defined fields or their sources can be a mystery to many users, leading to confusion and incorrect queries. One way to prevent this confusion is to define a data dictionary using the Looker API to populate field information into an easily accessible Google Sheet. This article describes this process. The solution below is particularly helpful when a description parameter is defined for fields in views.

The Script

This script is defined with a mix of Apps Script and Javascript, using the functions listed in this repository.

Following are some key elements to keep in mind when using the script:

  • The script is designed to generate a dictionary specific to one Explore. You will need to specify the Explore in if (explore == "explore_name"), as noted in the script below.
  • The script populates each specified view's information into a separate Google Sheet. A new sheet needs to be created for each view, and each Google Sheet name needs to match the name of the view.
    To populate the entire contents into one single sheet, please see the Populating the Entire Output into One Sheet section at the end of this article.
  • Values for model_name and explore_name are case-sensitive.

In this script, we use Google Sheet mapping to each view in an Explore. Each Google Sheet is named with the corresponding view name, and only data relevant to that view is displayed in the Google Sheet.

This script also caches the output of the API call for six hours after opening a sheet.

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

function LOOKER_GET_DATA_DICTIONARY(model_name) {
  //checks for previous cached entry
  var cache = CacheService.getScriptCache();
  var cached = cache.get("api_results");
  if (cached != null) {
    for (elem in cached) {
      results.push(elem)
      return results;
    }
  }
  // if nothing in cache, run the call
  try {

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

    // api call to the /lookml_models/{lookml_model_name} endpoint
    var response = UrlFetchApp.fetch(BASE_URL + "/lookml_models/" + model_name, options);
    var explores = JSON.parse(response.getContentText()).explores;
    var result = [];

    // defining the fields to retrieve for the Google Sheets
    result.push(["View Name", "Field Type", "Name", "Label", "Type", "Description", "Hidden"]);
                 // additional details if needed:
                 //, "SQL", "Source"]);

    for (var i = 0; len = explores.length, i < len; i++) {
      Logger.log(explores);

      var explore = explores[i].name;
      var explore_results = UrlFetchApp.fetch(BASE_URL + "/lookml_models/" + model_name + "/explores/" + explore, options);

      var connection = JSON.parse(explore_results.getContentText()).connection_name;
      var dimensions = JSON.parse(explore_results.getContentText()).fields.dimensions;
      var measures = JSON.parse(explore_results.getContentText()).fields.measures;
      var current_sheet = SpreadsheetApp.getActiveSheet().getName();

      // using this test to retrieve only data relevant to a specific explore
      // change explore_name with your explore
      if (explore == "explore_name") {

        // adding the data for the dimensions
        for (var j = 0; j < dimensions.length; j++) {
          // checks that only the fields from the underlying Looker view matching the name of the Google sheet are displayed
          if (dimensions[j].view.replace("_", " ") == current_sheet.toLowerCase()|| dimensions[j].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase()) {
            result.push([dimensions[j].view,
              "Dimension",
              (dimensions[j].name.substring((dimensions[j].name.indexOf(".")+1), dimensions[j].name.length)).replace(/_/g, " "),
              (dimensions[j].label != null ? dimensions[j].label : (dimensions[j].name.substring((dimensions[j].name.indexOf(".")+1), dimensions[j].name.length).replace(/_/g, " "))),
              (dimensions[j].type != null ? (dimensions[j].type).replace("_", " ") : "String"),
              dimensions[j].description,
              dimensions[j].hidden, dimensions[j].view_label
              //, (dimensions[j].sql != null ? dimensions[j].sql : ""),
              //dimensions[j].source_file
            ]);
          }
        }

        // adding the data for the measures
        for (var k = 0; k < measures.length; k++) {
          // checks that only the fields from the view matching the name of the sheet are displayed
          if (measures[k].view.replace("_", " ") == current_sheet.toLowerCase() || measures[k].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase()) {
            result.push([measures[k].view,
              "Measure",
              (measures[k].name.substring((measures[k].name.indexOf(".")+1), measures[k].name.length).replace(/_/g, " ")),
              (measures[k].label != null ? measures[k].label : (measures[k].name.substring((measures[k].name.indexOf(".")+1), measures[k].name.length)).replace(/_/g, " ")),
              (measures[k].type != null ? (measures[k].type).replace("_", " ") : "String"),
              measures[k].description,
              measures[k].hidden
              //, (measures[k].sql != null ? measures[k].sql : ""),
              //measures[k].source_file
            ]);
          }
        }
      }
    }
    cache.put("api_results", result, 21600); // cache for 6 hours
    //the maximum cache time is 6 hours (21600 seconds)
    //the default time is 10 minutes (600 seconds).
    return result
  } catch(err) {
    return "Something went wrong. " + err
  }
}

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) {
    Logger.log(err);
    return "Could not login to Looker. Check your credentials.";
  }
}

With the code above, we can set a cell of our Google Sheet to call the function on a particular model:
=LOOKER_GET_DATA_DICTIONARY("model_name")

 

NOTE: Instead of storing the API credentials in the script directly, you can also use the following code from the repository to require that the credentials be entered in the UI on the Google Sheet:

// set credentials via prompt
function setCred() {
  var ui = SpreadsheetApp.getUi();
  var base_url_input = ui.prompt("Set your Looker API credentials", "Base URL (e.g. https://instance_name.looker.com:19999/api/3.1):", ui.ButtonSet.OK_CANCEL);
  var client_id_input = ui.prompt("Set your Looker API credentials", "Client ID:", ui.ButtonSet.OK_CANCEL);
  var client_id_secret = ui.prompt("Set your Looker API credentials", "Client Secret:", ui.ButtonSet.OK_CANCEL);
  var scriptProperties = PropertiesService.getScriptProperties();
  // assign them to scriptProperties so the user doesn't have to enter them over and over again
  scriptProperties.setProperty("BASE_URL", base_url_input.getResponseText());
  scriptProperties.setProperty("CLIENT_ID", client_id_input.getResponseText());
  scriptProperties.setProperty("CLIENT_SECRET", client_id_secret.getResponseText());
  // test the credentials with a /user call
  testCred();
}

// testing the existing creds
function testCred() {
  var ui = SpreadsheetApp.getUi();
  var options = {
    "method": "get",
    "headers": {
      "Authorization": "token " + login()
    }
  };
  try {
    var response = UrlFetchApp.fetch(BASE_URL + "/user", options);
    var success_header = "Successfully set API credentials!";
    var success_content = "Authenticated as " + JSON.parse(response.getContentText()).first_name +
      " " + JSON.parse(response.getContentText()).last_name + " (user " + JSON.parse(response.getContentText()).id +").
      Keep in mind that API credentials are script/spreadsheet bound. This is needed for the custom formulas to keep on working for other users.
      Hit 'Test' to test your credentials or 'Delete' to remove the currently set credentials.";
    var result = ui.alert(success_header, success_content, ui.ButtonSet.OK);
  } catch (err) {
    var result = ui.alert("Invalid credentials / Credentials not set!",
      "Doublecheck your base URL and your client ID & secret.", ui.ButtonSet.OK);
  }
}

// delete credentials from scriptProperties
function deleteCred() {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.deleteAllProperties();
}

Populating the Entire Output into One Sheet

As noted above, the script populates one Google Sheet per view. If you would like to populate the entire output into one single Sheet, remove the following sections from the above script:

if (dimensions[j].view.replace("_", " ") == current_sheet.toLowerCase()|| dimensions[j].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase())
if (measures[k].view.replace("_", " ") == current_sheet.toLowerCase() || measures[k].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase())

Additional Resources

Related articles