Generating a Data Dictionary in Google Sheets

[michael]

Tutorial

Author @rducarrouge 

Last updated: 9 months ago

Please note that the content in this article is not officially supported by Looker.

New in Looker 7.8, you can use the Looker Data Dictionary from the Looker Marketplace if your admin has enabled certain Labs features.

Generating 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) {

 

    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.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.view.replace("_", " ") == current_sheet.toLowerCase()|| dimensions.view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase()) {

              result.push([dimensions.view,

                "Dimension",

                (dimensions.name.substring((dimensions.name.indexOf(".")+1), dimensions.name.length)).replace(/_/g, " "),

                (dimensions.label != null ? dimensions.label : (dimensions.name.substring((dimensions.name.indexOf(".")+1), dimensions.name.length).replace(/_/g, " "))),

                (dimensions.type != null ? (dimensions.type).replace("_", " ") : "String"),

                dimensions.description,

                dimensions.hidden, dimensions.view_label

                //, (dimensions.sql != null ? dimensions.sql : ""),

                //dimensions.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.view.replace("_", " ") == current_sheet.toLowerCase() || measures.view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase()) {

              result.push([measures.view,

                "Measure",

                (measures.name.substring((measures.name.indexOf(".")+1), measures.name.length).replace(/_/g, " ")),

                (measures.label != null ? measures.label : (measures.name.substring((measures.name.indexOf(".")+1), measures.name.length)).replace(/_/g, " ")),

                (measures.type != null ? (measures.type).replace("_", " ") : "String"),

                measures.description,

                measures.hidden

                //, (measures.sql != null ? measures.sql : ""),

                //measures.source_file

              ]);

            }

          }

        }

      }

      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();

  }

The results from the script will be each Sheet populated with the fields defined in the matching view name:

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.view.replace("_", " ") == current_sheet.toLowerCase()|| dimensions.view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase())

if (measures.view.replace("_", " ") == current_sheet.toLowerCase() || measures.view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase())

Additional Resources

• This Community post, Writing a Simple Data Dictionary to CSV Using the Looker API and the Python Requests Library, for a detailed workflow to create a CSV data dictionary with Python.

• The Help Center article Creating a Data Dictionary Using Looker's API, also details how to achieve this with Ruby.