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

drewgillson
Participant II

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.

7 7 10.1K
7 REPLIES 7

Have any of you gotten the following error:

“Incorrect range width, was 1 but should be 6 (line 24, file “Code”)”

I’ve come across this myself - the offending line in the script is the following but I can’t see a reason why it would stop running. This behaviour occured after the underlying look returned no rows. Any suggestions?

sheet.clear().getRange(workbook.getLastRow()+1,1,records.length,records[0].length).setValues(records);

@Mihalis, you can try to wrap it in a try {} catch(err) {} for better error handling for empty Looks.

An example:

try {
  sheet.clear().getRange(workbook.getLastRow()+1,1,records.length,records[0].length).setValues(records);
} catch(err) {
  return "Result set was empty!"
}

Alternatively, you can use this import script or this script using the API.

Thanks a lot for the code! This works just fine and seems to be much more stable than the IMPORTXML function indeed.

The code currently opens all the listed Looks into separate tabs in the Gsheet. Is it possible to adjust the code so that we can choose where each Look should open? Ideally I would like to have several Looks next to one another in the same tab.

Thanks!

Nice tip!
Or you can also use IMPORTFRMWEB which gives superpowers to importing web data…
smart caching/refresh, use of rotating proxies for loading any website, many functions per page, …
https://gsuite.google.com/marketplace/app/importfromweb_imports_data_from_any_webs/278587576794

Hi everyone, I tried to implement the code but this error returned for me, I have been trying to debug it but it didn’t stand out to me anything wrong with the code above, I only changed the Looker links inside of “looks” section:
 

SyntaxError: Function statements require a function name (line 81, file "Code")

Line 81 is: 

this["refresh" + fn] = eval('function() {Looker.refresh("' + look + '", Looker.looks["' + look + '"]);}');

Hi everyone, I tried to implement the code but this error returned for me, I have been trying to debug it but it didn’t stand out to me anything wrong with the code above, I only changed the Looker links inside of “looks” section:
 

SyntaxError: Function statements require a function name (line 81, file "Code")

Line 81 is: 

this["refresh" + fn] = eval('function() {Looker.refresh("' + look + '", Looker.looks["' + look + '"]);}');

Same here same error… cannot figure out what is the problem