Data studio is a really great tool we are using to report Google Analytics Ads etc.
In this post you gonna learn how you can import a xml feed to your Googe Data studio and data are updated , so you can grab almost real time data from any xml
What you will need :
- Data studio ( yes i know )
- Google Sheet
- The XML url
- A custom script with time trigger in Google Scripts
How this works
Data Sheet has an option to load data from XML. there is the function [importxml] that can do the work
for example if your xml is like that (test.xml in our example )
<data-set>
<record>
<BookingID>1</BookingID>
<Rooms>1</Rooms>
<BookingValue>100</BookingValue>
<Profile>Individual rates</Profile>
<Status>No Change</Status>
</record>
<record>
<BookingID>2</BookingID>
<Rooms>1</Rooms>
<BookingValue>200</BookingValue>
<Profile>Individual rates</Profile>
<Status>No Change</Status>
</record>
</data-set>
just put this on an empty Google Sheet lets say on the A2 cell
=IMPORTXML(“http://yourdomain.com/test.xml#”&J24,“//record”)
don’t remove the &J24 and change “record” to what is your xml item . In my example it is record as you can see from the test xml above
You can also add header to the first row for your help
please see a screenshot here ( in a2 cell we have the importxml
If everything was OK then you probably see your data from your xml.
Then you should go to your data studio and add a new source from Data Sheet
https://datastudio.google.com/datasources
How to auto refresh the Data in the Data Sheet
-Open the Data Sheet and go to Tools script editor
-create a new script with this Data
/**
* From: http://stackoverflow.com/a/33875957/1677912
* Adapted by Davis E. Ford, May 5, 2017
*/
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
// At this point, we are holding the lock.
var id = “yourgooglesheetid”;
var ss = SpreadsheetApp.openById(id);
var sheets = ss.getSheets();
for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
var sheet = sheets[sheetNum];
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var tempFormulas = [];
for (var row=0; row<formulas.length; row++) {
for (col=0; col<formulas[0].length; col++) {
// See https://regex101.com/r/bE7fJ6/2
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
if (formulas[row][col].search(re) !== -1 ) {
// Use a blank cell here, set it to whatever you want
sheet.getRange(“J24”).setValue(Math.round(Math.random()*100000));
}
}
}
// After a pause, replace the import functions
Utilities.sleep(2000);
for (var i=0; i<tempFormulas.length; i++) {
var cell = tempFormulas[i];
sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
}
// Done refresh; release the lock.
lock.releaseLock();
}
}
You can find the Google Sheet Id by the url of your Sheet
for example if the url is
https://docs.google.com/spreadsheets/d/18urzrwq3SuviRRxnYNJX7cghEM3IStIKh7xru8pYU7n0/edit#gid=0
the Sheet id is 18urzrwq3SuviRRxnYNJX7cghEM3IStIKh7xru8pYU7n0
-add a time trigger ( edit – current project triggers )
- That’s it! if you see a random number in cell J24 never mind!
