In a recent project, I wanted to use Google Sheets as a content management system: the source for a print layout and a website. I wanted the content to reside in a single, easily editable location.
In order to achieve that outcome, InDesign requires the data in an XML file. But first, I had to convert the JSON data in Google Sheets to XML. This would allow me to customize the tag names to leverage the custom mapping styles feature within InDesign, which would make it a breeze to update the InDesign document as the content of the book changed.
The concept I came up with was to export the XML tree in the DOM, then save that text as an XML document. I used AngularJS for the web application, and thus, I used JavaScript to create the XML feed.
A valid InDesign XML feed needs to have a single root level node. Inside that main node, you can have nested child nodes. For example, if you are creating a catalog of toy stores, you might want your root node to be named “Toy Stores,” with each store having a name of “store.” Inside the “store” node, you can place all the information about the store, such as the phone, address, website, hours, etc.
<toyStores>
<store>
<storeName> Toy Store 1</storeName>
<storeLocation>1234 Main St.</storeLocation>
<storePhone>(123) 456-7890</storePhone>
</store>
<store>
<storeName> Toy Store 2</storeName>
<storeLocation>5678 State St.</storeLocation>
<storePhone>(123) 456-7890</storePhone>
</store>
</toyStores>
If you have a more complex data structure, it’s helpful to sketch out the structure of the feed you want on a piece of paper. This will help you make sure you’re outputting the content in the right order.
We can create a data structure to match our XML structure by using Angular’s ng-repeat and JavaScript’s document.createElement. Then, add tags before and after that content. The XML node should have an open tag followed by the content, and finally, a closing tag (matching the open tag).
We can also store each node for each store. As shown below, we can create a variable xmlStoreList which will be the output of the XML feed we create. We are creating the variable and appending the root tags to the variable. If you were to output this, you would see the opening and closing tags of the XML feed we are going to build.
var xmlStoreList = document.createElement("div");
xmlStoreList.appendChild(document.createTextNode("<?xml version='1.0' encoding=“UTF-8”?>"));
xmlStoreList.appendChild(document.createTextNode('<toyStores></toyStores>'));
Once the structure has been created, we will want to output it to the DOM. This can be done by finding an element we want to target in the HTML, then calling the append function to show the content in the DOM.
In the example, we have a div with an id of XML. We can ask the code to append the xmlStoreList variable (created above) to the specific div to be displayed on the page.
var element = document.getElementById("xml");
element.appendChild(xmlStoreList);
The output of the browser’s DOM is copied and pasted into a text editor and saved as an .xml file to be imported into InDesign. Be sure to use an XML validator to check your work for errors in your data.
There you have it. You have created your own custom JSON-to-XML file via AngularJS.
<!DOCTYPE html>
<html lang="en">
<head>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular.min.js"></script>
</head>
<body>
<div ng-app="myApp" ng-controller="agencyCtrl" id="xml"> </div>
<script>
var app = angular.module('myApp', []);
app.controller('agencyCtrl', function($scope, $http) {
$http.get("https://spreadsheets.google.com/feeds/list/19j5EnO9U-4LNnQi29JdpaMFJ_KUedphbKh-C4PJT328/1/public/values?alt=json").then(function(response) {
$scope.myData = response.data.feed.entry;
// console.log(response.data.feed.entry);
var xmlStoreList = document.createElement("div");
xmlStoreList.appendChild(document.createTextNode(""));
xmlStoreList.appendChild(document.createTextNode(''));
angular.forEach($scope.myData, function(store) {
xmlStoreList.appendChild(document.createTextNode(''));
xmlStoreList.appendChild(document.createTextNode(
'' + store.gsx$store.$t + ' '));
xmlStoreList.appendChild(document.createTextNode(
'' + store.gsx$address.$t + ' '));
xmlStoreList.appendChild(document.createTextNode(
'' + store.gsx$phone.$t + ' '));
xmlStoreList.appendChild(document.createTextNode(' '));
});
xmlStoreList.appendChild(document.createTextNode(" "));
var element = document.getElementById("xml");
element.appendChild(xmlStoreList);
});
});
</script>
</body>
</html>
hi Bryan,
please explain how to insert a hyperlink into xml associated with a label. For example “click here” and under an hyperlink. When we export the publication in .pdf, the text “click here” must contain the link vs. http://www….
I hope I explained.
You can help us?