[Retired] Google Sheet Refresh Script

The content of this article has been updated and migrated to a Looker Help Center article.

1 Like

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][s].indexOf(e)>-1){r.push(t.getCell(i+1,s+1))}}}return r}
  1. Save

  2. Reload Spreadsheet

2 Likes

Awesome function. One additional thing to consider, formatting is stripped in this function, but a slight change will maintain formatting.

replace

“.html?refresh=”

with “.html?apply_formatting=true&”

1 Like

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

Hey Dan,

Thanks for your question! If you’d like to persist the data in your Google sheet, I would recommend using Looker’s native Google Sheet Import capabilities. This script is meant to automatically update the sheet with fresh data upon opening but the native export will let the data persist.

Let me know if I’m misunderstanding your request.

Spencer

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.

Thank you for catching that @favrot - have updated the OP now.