Why Do I Need a Data Dictionary in Google Sheets?
Looker enables more people to access data and run ad-hoc queries, but sometimes the meaning of the field or its source is unknown to the end user and leads to incorrect queries. One way to prevent this is to define a data dictionary using the Looker API to populate this information in Google Sheets. This script is particularly helpful when a description parameter is defined for the fields in the views.
The Script
This script is defined in a mix of Apps Script and Javascript, using the functions listed on this repository.
Some key elements to keep in mind when using the script below are:
- The
model_nameandexplore_nameare case sensitive. - The set up is to populate each view’s information in the Google Sheet with the tab name matching the view name (to not use this, please check out the comments below to see the lines enforcing this behavior).
In this script we are using a Google Sheet mapping to each view in the Looker Explore. Each Google sheet has the name of the Looker view, and only data relevant to that view is displayed in the Google Sheet.
This also puts the output of the API call in cache, so the call does not get run on each opening of the Sheets, but on the first run of the Sheet and then gets cached for 6 hours.
// Replace this with your base domain
var BASE_URL = 'https://your_company.com:19999/api/3.0';
// 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 the credentials be inputed 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://yourdomain.looker.com:19999/api/3.0):", 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();
}
Additional Resources
- We also have this post using Python to create a .csv data dictionary.
- A similar post to achieve this in Ruby is here.