[Retired] Google Sheet Refresh Script

There is also an updated version of this below.

Steps:

1. 
   

   Open Script Editor
   
   

   
   


2. 
   

   Create a Blank Project
   
   

   
   


3. 
   

   Paste Looker Code (on a Blank Canvas)

   
   

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);for(var i=0;i<r.length;i++){r[i].setValue(r[i].getFormula().replace(/.html[^"]*"/,".html?apply_formatting=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].indexOf(e)>-1){r.push(t.getCell(i+1,s+1))}}}return r}

Is there anyway to make the data persist in the Sheet? It seems to clear the data and refresh the sheet when you open it

A small update to the script due to a change in the URL (.html becomes .gsxml):

for(var i=0;i<r.length;i++){

    r[i].setValue(r[i].getFormula().replace(/(.html[^"]*)"/,".html?apply_formatting=true&apply_vis=true&refresh="+(new Date).getTime()+'"'))

  }

should be

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()+'"'))

  }

I’m not a dev but that did work for me so someone else please feel free to update more thoroughly if it’s required.