Skip to content

Instantly share code, notes, and snippets.

@the-paulus
Created April 15, 2017 21:07
Show Gist options
  • Save the-paulus/d910472e9b9fbfc3bb1b55f53538bfc5 to your computer and use it in GitHub Desktop.
Save the-paulus/d910472e9b9fbfc3bb1b55f53538bfc5 to your computer and use it in GitHub Desktop.
Google Sheet script that will convert cells A2:B1000 into a downloadable XML file.
function doGet() {
// The sheet that contains the manufacturer information.
var manufacturerDataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Manufacturers");
// The first row of the sheet that contains the manufacturer's name and description.
var manufacturerAttributes = manufacturerDataSheet.getRange("A1:B1").getValues();
// Range of rows and columns that contain the manufacturer information.
var manufacturerData = manufacturerDataSheet.getRange("A2:B1000").getValues();
// XML document object.
var document = XmlService.createDocument();
// Root element of the XML output.
var root = XmlService.createElement('manufacturers');
// variable that holds the generated XML document.
var xmlOutput;
for(var i = 0; i < manufacturerData.length; i++) {
if(!isEnd(manufacturerData[i])) {
// Begin manufacturer tag.
var manufacturer = XmlService.createElement('manufacturer');
for(var j = 0; j < manufacturerData[i].length; j++) {
var tag = manufacturerAttributes[0][j];
var text = manufacturerData[i][j];
var manufacturerAttribute = XmlService.createElement(tag);
if(tag == "description") {
manufacturerAttribute.addContent(XmlService.createCdata(text))
} else {
manufacturerAttribute.setText(text);
}
manufacturer.addContent(manufacturerAttribute);
}
root.addContent(manufacturer);
} else {
break;
}
}
// Put everything together and output it.
document.addContent(root);
xmlOutput = XmlService.getPrettyFormat().format(document);
Logger.log(xmlOutput);
return ContentService.createTextOutput(xmlOutput).downloadAsFile("manufacturers.xml");
}
function isEnd(row) {
for(var i = 0; i < row.length; i++) {
if(row[i] != "") {
return false;
}
}
return true;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment