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

The vanilla setup for exporting a Look to Google Sheets can be found in Docs. In a nutshell, the process is:

1. Click the gear menu on the Look page, and select “Edit Settings”.

2. Turn Public Access “On”.

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 some reports of unreliablility 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;
}

To use this script inside Google Sheets:

1. Head 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 pull data from Looker anytime the sheet is opened or refreshed, you can use the following script:

function onOpen(){
 var e=SpreadsheetApp.getActiveSpreadsheet();
 var t=[{name:"Refresh This Sheet",functionName:"Refresh"},{name:"Refresh All Sheets",functionName:"RefreshAll"}];
 e.addMenu("Looker",t)
}
function onInstall(e){
 onOpen(e)
}
function Refresh(e){
 var t=SpreadsheetApp.getActiveSheet();
 if(typeof e!=="undefined"){
 t=e
 }
 var n=t.getDataRange();
 var r=find("ImportXML",n);
 var y=find("lookerFetchData", n);
 for(var i=0;i<r.length;i++){
 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])
 }
}
function find(e,t){
 var n=t.getFormulas();
 var r=[];
 for(var i=0;i<n.length;i++){
 for(var s=0;s<n[i].length;s++){
 if(n[i][s].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. Head 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.