Looker supports exporting a Look directly to Google Sheets. In this article, we present some extra Google Sheets functions that make the process smoother.

Basic Setup

You can find the basic setup for exporting a Look to Google Sheets on the Public Sharing, Importing, and Embedding documentation page. The process is:

1. Click the gear menu on the Look page, and select Edit Settings.
2. Turn on Public Access.
3. Save the change.
4. Copy the URL under Google Spreadsheet.
5. Paste the URL into the cell of the Google spreadsheet where you would like the upper-left cell of your data to appear, then hit Enter.

Custom Import Function

The URL above includes the Google Sheets' built-in ImportXML function. After receiving some reports of unreliability and long loading times, we developed a custom lookerFetchData function, which addresses some of these problems.

function lookerFetchData(url) {
  url = url.replace(/(\/\w+\.)txt|html|csv|gsxml(\?.*)?/, "$1csv$2");
  var csvString = UrlFetchApp.fetch(url).getContentText();
  var dataIn = Utilities.parseCsv(csvString);
  var dataOut = dataIn.map(function(row) {
  return row.map(function(val) {
  if (val == '') return '';
  var dateMatch = /(\d{4})\-(\d{2})-*(\d{0,})/.exec(val);
  while (dateMatch != null) {
  // first index is full match
  return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3] || 1);
  };
  if (val.match(/[-a-zA-Z]/)) {
  return String(val)
  };

  val = val.replace(/[^\d.]/g, '');

  if (val.match(/[0-9.]+/))
  return Number(val);

  return Number(parseInt(val));
  });
  });
  return dataOut;
}

If you have a field with a more granular timestamp format, such as yyyy-MM-dd HH:mm:ss, use the script below to export the full timestamp:

function lookerFetchData(url) {
  url = url.replace(/(\/\w+\.)txt|html|csv|gsxml(\?.*)?/, "$1csv$2");
  var csvString = UrlFetchApp.fetch(url).getContentText();
  var dataIn = Utilities.parseCsv(csvString);
  var dataOut = dataIn.map(function(row) {
  return row.map(function(val) {
  if (val == '') return '';
  var dateMatch = /(\d{4})\-(\d{2})-*(\d{2})_(\d{2})\:(\d{2})\:(\d{2})/.exec(val);
  while (dateMatch != null) {
  // first index is full match
  //return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3] || 1);
  return Utilities.formatDate(new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3], dateMatch[4], dateMatch[5], dateMatch[6]|| 1), "GMT", "MM-dd-yyyy HH:mm:ss");
  };
  if (val.match(/[-a-zA-Z]/)) {
  return String(val)
  };
  val = val.replace(/[^\d.]/g, '');
  if (val.match(/[0-9.]+/))
  return Number(val);
  return Number(parseInt(val));
  });
  });
  return dataOut;
  }

To use this script inside Google Sheets:

1. Navigate to Tools > Script Editor from the top section of the sheet.
2. Create a new blank project.
3. Paste the code above into the script editor and save — you may give it any name. Close the script editor and refresh the Google Sheet.
4. Replace ImportXML from the vanilla setup with lookerFetchData.

Auto Refresh Script

Finally, if you want your Google Sheet to automatically refresh and pull data from Looker, you can use the following script:

  function onOpen(){
    /// get current spreadsheet url and sheet
    var e=SpreadsheetApp.getActiveSpreadsheet();
    /// create menu -- refresh and refresh all under "Looker"
    var t=[{name:"Refresh This Sheet",functionName:"Refresh"},{name:"Refresh All Sheets",functionName:"RefreshAll"}];
    e.addMenu("Looker",t)
    Refresh(e)
  }
  function onInstall(e){
    onOpen(e)
  }
  function Refresh(e){
    /// current sheet if no active sheet passed
    var t=SpreadsheetApp.getActiveSheet();
    if(typeof e!=="undefined"){
    t=e
    }
    /// find which cell has our fetch function
    var n=t.getDataRange();
    var r=find("ImportXML",n);
    var y=find("lookerFetchData", n);
    for(var i=0;i<r.length;i++){
      /// add refresh time
    r[i].setValue(r[i].getFormula().replace(/(.gsxml[^"]*)"/,".gsxml?apply_formatting=true&apply_vis=true&refresh="+(new Date).getTime()+'"'))
   }
    for(var i=0;i<y.length;i++){
    y[i].setValue(y[i].getFormula().replace(/(.html[^"]*|.txt[^"]*)"/,".txt?apply_formatting=true&apply_vis=true&refresh="+(new Date).getTime()+'"'))
    }
  }
  function RefreshAll(){
    var e=SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for(var t=0;t<e.length;t++){
    Refresh(e[t])
    }
  }
  /// find all formulas that(used in refresh)
  function find(e,t){
    var n=t.getFormulas();
    /// n is now a list of coordinates with formulas (eg A1)
    var r=[];
    for(var i=0;i<n.length;i++){
    for(var s=0;s<n[i].length;s++){
          var x = n[i][s].toUpperCase();
          if(x.indexOf(e)>-1){
    r.push(t.getCell(i+1,s+1))
    }
    }
    }
    return r
  }

The steps to enable this are the same as with the Import function:

1. Navigate to Tools > Script Editor from the top section of the sheet.
2. Create a new blank project.
3. Paste the code above into the script editor and save — you may give it any name. Close the script editor and refresh the Google Sheet.