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
model_name
andexplore_name
are case-sensitive. - The script populates each view's information into an individual Google Sheet. Each Google Sheet name will match a view name (to populate the entire contents into one sheet, please check out the note below to see the lines enforcing this behavior).
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
- 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.
- This Help Center article, Creating a Data Dictionary Using Looker's API, also details how to achieve this with Ruby.