Localizing Looker with the Looker API and Google Translate API

  • 10 May 2022
  • 0 replies
  • 103 views

Userlevel 3
Badge

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

 

While you can localize labels, group labels, and descriptions in your LookML model, this Community Post addresses how to localize Looker using the Looker and Google Translate APIs.

Requirements

  • You must have API credentials for your Looker instance.
  • All fields and views must have the label and view_label parameters applied.
  • The suggested translations are from the Google Translate API — you may need to manually revise the translations to achieve your desired output.
  • The model in question is configured in Looker.

How to use the code

  1. Open Google Sheets.
  2. From the menu bar, select Tools > Script Editor.
  3. Assign proper permission for the Sheet.
  4. Paste the code.
  5. Update the relevant variables.
  6. Click Save and Run.
  7. Go back to the Sheet and call the function =GetLocale("my_model", "fr").
  8. Ensure that a recognized language is set (check out this list).

The code

// 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_KEY'; //
var CLIENT_SECRET = 'YOUR_API_SECRET'; //
///////////////////////////////////////////////////////////////////

// function below will be called to remove possible duplicates
function rmDuplicatesFrom(arr) {
var check = {};
var result = [];
var j = 0;
for(var i = 0; i < arr.length; i++) {
var item = arr[i];
if(check[item] !== 1) {
check[item] = 1;
result[j++] = item;
}
}
return result;
}


function GetLocale(model_name, language) {
try {
var options = {
'method': 'get',
'headers': {
'Authorization': 'token ' + login()
}
};

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

// looping through explore to get all the info
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 dimensions = JSON.parse(explore_results.getContentText()).fields.dimensions;
var measures = JSON.parse(explore_results.getContentText()).fields.measures;

// adding data for explore label
result.push([explore.label]);

// adding the data for dimensions' label
for (var j = 0; j < dimensions.length; j++) {
var label = (dimensions[j].label).replace(dimensions[j].view_label, "").trim();
var locale_dimension = LanguageApp.translate(label, 'en', language);
if (label != null) {
result.push([
'"'.concat(label).concat('" = "').concat(locale_dimension).concat('";')
]);
}
//handle the view_label values
var view_label = (dimensions[j].view_label);
var view_locale_dimension = LanguageApp.translate(view_label, 'en', language);
if (view_label != null) {
result.push([
'"'.concat(view_label).concat('" = "').concat(view_locale_dimension).concat('";')
]);
}
}

// adding the data for measures' label
for (var k = 0; k < measures.length; k++) {
var label = (measures[k].label).replace(measures[k].view_label, "").trim();
var locale_measure = LanguageApp.translate(label, 'en', language);
if (label != null) {
result.push([
'"'.concat(label).concat('" = "').concat(locale_measure).concat('";')
]);
}
//handle the view_label values
var view_label = (measures[k].view_label);
var view_locale_measure = LanguageApp.translate(view_label, 'en', language);
if (view_label != null) {
result.push([
'"'.concat(view_label).concat('" = "').concat(view_locale_measure).concat('";')
]);
}
}}

// keeping things ordered
result.sort();

// removing duplicate values
var unique = rmDuplicatesFrom(result);

// Add comments for time script is run
var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy");
unique.unshift(["// Created on ".concat(curDate)]);
unique.unshift(["// Content for .strings file:"]);

return unique
} 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.";
}
}
Warning: Instead of storing the API credentials in the script directly, you can also use the following code from the repository to require the credentials to be input 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();
}

Output

The script will output a table of field labels localized in the language you have selected:

This script should help with most field labels — however, as you can see above, the translation is limited to what Google Translate will output (field:ID). Therefore, some modifications may be necessary for some output values to match your needs.
 

Troubleshooting errors

 

If you receive any errors, make sure to check for any of the following conditions:

  • Are the values used for the variables all correct?
  • Are you missing the API port (:19999)?
  • Is the model name referenced correctly? Model names are case sensitive.
  • Are the API credentials associated with a user who does not have permission to access the model?
  • Is the second argument in the proper format? The language to use in the second argument must be an existing two-letter ISO_639-1 format value.

0 replies

Be the first to reply!

Reply