Is IMPORTXML frustrating you? Here is a better method to refresh public looks in Google Sheets

[drewgillson]

There’s an existing solution to import public looks into a Google Sheet, but it’s unreliable because the IMPORTXML function sucks. Here is a much better way! Navigate to “Tools > Script Editor” in your Google Sheet (more detailed instructions in this older post), and then paste this code into the blank script window:

var Looker = {
  
  looks: {
     // Replace this list of looks with descriptive names and public look URLs of your own:
    "Sales": "https://your.looker.com/looks/AbCdEfGhIjKlMnOpQrStUvWxYz12.txt?apply_formatting=true",
    "Customers": "https://your.looker.com/looks/AbCdEfGhIjKlMnOpQrStUvWxYz12.txt?apply_formatting=true",
    "Inventory": "https://your.looker.com/looks/AbCdEfGhIjKlMnOpQrStUvWxYz12.txt?apply_formatting=true"
  },
  
  refresh: function(look, url) {
    var response = UrlFetchApp.fetch(url, {method : "get", payload : "", "muteHttpExceptions" : true}).getContentText();
    
    if (response) {
      var records = new Array();
      var rows = response.match(/[^\r\n]+/g);
      for(var i = 0; i < rows.length; i++) {
        var columns = rows.split("\t", -1);
        records.push(columns);
      }
    
      var workbook = SpreadsheetApp.getActiveSpreadsheet();
      var lookSheet = workbook.getSheetByName(look);
      if (!lookSheet) {var sheet = workbook.insertSheet(look,workbook.getNumSheets())}
      else {var sheet = SpreadsheetApp.setActiveSheet(workbook.getSheetByName(look))}
      sheet.clear().getRange(workbook.getLastRow()+1,1,records.length,records[0].length).setValues(records);
      return records;
    }
  },
  
  refreshAll: function() {
    for (item in menuItems) {
      if (item <= 1) continue;
      var look = menuItems[item].name.replace(/Refresh /,"");
      Looker.refresh(look, Looker.looks[look]);
    }
  }
}

var menuItems = new Array({name: "Refresh All", functionName: "refreshAll"}, null);
for (var look in Looker.looks) {
  var fn = look.replace(/\W+/g, "");
  this["refresh" + fn] = eval('function() {Looker.refresh("' + look + '", Looker.looks["' + look + '"]);}');
  menuItems.push({name: "Refresh " + look, functionName: "refresh" + fn});
}

function refreshAll() {
  Looker.refreshAll();
}

function onOpen() {
  var e = SpreadsheetApp.getActiveSpreadsheet();
  e.addMenu("Looker", menuItems);
}

function onInstall(e) {
  onOpen(e)
}

Save the script and then click “Publish > Deploy as web app”. You’ll now have a “Looker” menu that you can use to create and refresh sheets for the public looks defined in your Looker.looks object.

If you want the data from your public looks to be refreshed automatically, you can click “Resources > Current project’s triggers” and create an installable trigger that runs the refreshAll function on a time interval in the background.

Hope this helps.