Helping Localize Looker with its API and Google Translate API!


(romain.ducarrouge) #1

With Looker 6.2, you can now use our beta feature to localize labels, group labels, and descriptions in your model.

Requirements

  • API credentials for Instance
  • All fields and views having the label and view_label defined.
  • Understanding that the translations suggested are pulled from the Google Translate API and some results may need manual revision to match the output you want.

How to use the code

  1. Go to Google Sheet
  2. Tools
  3. Script Editor
  4. Give proper permission for the Sheet
  5. Paste Code
  6. Update relevant variables
  7. Save & Run
  8. Go back to Sheet and call function: =GetLocale("my_model", "fr")
  9. Ensure you set a recognized language (check out this list)

Code

// Replace this with your base domain /////////////////////////////
var BASE_URL = 'https://YOUR_COMPANY.looker.com:19999/api/3.0';  // 
// 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 ran
    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.";
  }
}

Note :warning:ļø

Instead of storing the API credentials in the script directly, you can also use the following code from the repository [link to repo] to require the credentials to 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();
}

Output

As you can see above the translation is limited to what the Google Translate will output (field:ID) so there will be modifications to do on some output values but this script should help with most field labels!

Something went wrong?

  1. Are the values used for the variables all correct?
  2. Are you missing the API port (:19999)?
  3. The model name is case sensitive!
  4. The API credentials are associated with a user that does not have access permission to the model?
  5. The language to use in the second argument must be an existing 2 letter value: ISO_639-1 format!