Currently, IMPORTXML function is not working in google sheets, so you need to have some workaround to scrape XML, for example from a distributer and make some changes. Fully, dinamically to be able to follow the price changes. Therefore comes in place the absolutely best feature Google App Script.
The following code will fetch XML, and parse, based on g: parameters. But, if some of the xml columns does not have g: prefix, that doesn’t a problem.
The main concept comes from this article and code (https://2018cl.clxinternal.com/adops-tools/google-apps-script-customizable-fast-xml-parser/) But many changes are made.
The first one, is that this code creates a menu point, where you can just click and instantly download the xml, parse and load into the data sheet.
You can change the followings:
- Picked sheet
- Picked URL – XML
- Columns (at OPT_childNamesArray)
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('XML Download')
.addItem('XML Download', 'downloadxml')
.addToUi();
}
function downloadxml() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('data');
var url = "https://example.com/google-shopping.xml";
var xml = UrlFetchApp.fetch(url).getContentText();
var document = XmlService.parse(xml);
var root = document.getRootElement();
var items = root.getChild('channel').getChildren('item');
var total = 0;
for (var i = 0; i < items.length; i++) {
total += Number(items[i].getText());
}
var totalElement = XmlService.createElement('total').setText(total);
root.addContent(totalElement);
xml = XmlService.getPrettyFormat().format(document);
var OPT_childNamesArray = ["id","title","description","link","image_link","brand","google_product_category","condition","availability","price","shipping_weight","size","identifier_exists"];
var OPT_Namespace = XmlService.getNamespace("g", "http://base.google.com/ns/1.0");
var exportRows = [];
for (var x=0; x<items.length; x++){
// Iterate through items in the XML/RSS feed
var currentFeedItem = items[x];
var singleItemArray = []; // use to hold all the values for this single item/row
// Parse for specific children (requires names and namespace)
if (OPT_childNamesArray){
for (var y=0; y<OPT_childNamesArray.length; y++){
// Iterate through requested children by name and fill rows
var currentChildName = OPT_childNamesArray[y];
if (OPT_Namespace){
if (currentFeedItem.getChild(OPT_childNamesArray[y],OPT_Namespace)){
singleItemArray.push(currentFeedItem.getChildText(OPT_childNamesArray[y],OPT_Namespace));
}
else {
singleItemArray.push(currentFeedItem.getChildText(OPT_childNamesArray[y]));
}
}
else {
if (currentFeedItem.getChild(OPT_childNamesArray[y])){
singleItemArray.push(currentFeedItem.getChildText(OPT_childNamesArray[y]));
}
else {
singleItemArray.push("null");
}
}
}
exportRows.push(singleItemArray);
}
// Parse for ALL children, does not require knowing names or namespace
else if (!OPT_childNamesArray){
var allChildren = currentFeedItem.getChildren();
if (x == 0){
// if looking at first item, create a header row first with column headings
var headerRow = [];
for (var h=0; h<allChildren.length; h++){
headerRow.push(allChildren[h].getName());
}
exportRows.push(headerRow);
}
for (var c=0; c<allChildren.length; c++){
singleItemArray.push(allChildren[c].getText());
}
exportRows.push(singleItemArray);
}
}
// Paste the generated md array export into the spreadsheet
if (OPT_childNamesArray){
sheet.getRange(2,1,exportRows.length,exportRows[1].length).setValues(exportRows);
}
else if (!OPT_childNamesArray){
var maxRangeLength = 0;
var currentRowIndex = 1;
for (var x = 0; x<exportRows.length; x++){
if (exportRows[x].length > maxRangeLength){
maxRangeLength = exportRows[x].length;
}
sheet.getRange(currentRowIndex,1,1,exportRows[x].length).setValues([exportRows[x]]);
currentRowIndex++;
}
}
}