Private Look Data Pulls Into Google Sheets

Using Google App Scripts and the looker API it is possible to dynamically pull in different looks into a single Google sheet. Below is sample code that requests user input for a look ID to pull. This is a great option to avoid public URL sharing and for cases where your looker does not allow incoming traffic from the public web:

function look_pull() {
   var ui = SpreadsheetApp.getUi();
   var look_info = ui.prompt('Look ID?', ui.ButtonSet.OK);
   var auth_data = {'client_id':'<yourapiclientid>', 'client_secret':'<yourapiclientsecret>'};
   var options = {
   'method' : 'post',
   'payload' : auth_data
   };
  var response = UrlFetchApp.fetch('https://<yourlookerapiendpoint>:19999/login', options);
  for(i in response) {
    //Logger.log(i + ": " + response[i]);
  }
  // Logger.log(response.getContentText());
  var dataAll = JSON.parse(response.getContentText());
  var token = dataAll.access_token;
  var json_auth = 'token '.concat(token)
  var options2 = {
    'method': 'get',
    'headers': {'Authorization': json_auth}
  };
  var look_endpoint = 'https://<yourlookerapiendpoint>:19999/api/3.0/looks/'.concat(look_info.getResponseText()).concat('/run/csv')
  var response2 = UrlFetchApp.fetch(look_endpoint, options2);
  // Logger.log(response2.getContentText());
  var csvData = Utilities.parseCsv(response2.getContentText());
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var LookerItems = [
    {name: 'Run a Look', functionName: 'look_pull'}
  ];
  spreadsheet.addMenu('Looker', LookerItems);
}

You choose when to pull down data:

A prompt for a look ID(result set return depends on the users permissions):

Old Results are cleared and new results are populated:

**

1 Like

Hi @brett_g

I am attempting to utilize the code you provided. I copied your script into the Google Script editor and changed the appropriate fields like the Client ID, Secret and Base URL to the appropriate values. However, when I run the script and submit a look number I get an error message that “Execution Failed: DNS Error” for line 9 of your script. I have verified that my Base URL is correct as I have been able to use Postman to access the API, run the look and then return the data. Any idea what could be going on?

Thanks!