Thursday, January 24, 2019

Google Apps Script - Create an XML Table From An Array

This Google Apps Script shows how to create an XML table from an array.  In this case, the array comes from a spreadsheet.  The spreadsheet and sheet objects are passed to the function.

createXml


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function createXml(ss,sheet) {
  var data = sheet.getDataRange().getValues();
  var ns = XmlService.getNamespace('foo','http://bradsmithcpa.com');
  var root = XmlService.createElement('base').setNamespace(ns);
  var table = XmlService.createElement('table');
   for (var j = 0; j < data.length; j++){
      var row = XmlService.createElement('row')
     for(var k = 0; k < data[0].length; k++){
       var col = 'col-' + (k + 1)
       var type = data[j][k].constructor.name
         if(data[j][k].constructor.name == 'Date'){        
           var vbl = XmlService.createElement(col)         
           .setText(data[j][k].toISOString().slice(0, 10));}
       else{var vbl = XmlService.createElement(col)
       .setText(data[j][k])}
       row.addContent(vbl)}
           table.addContent(row)
     }  
   root.addContent(table)
   
   var document = XmlService.createDocument(root);
   var xml = XmlService.getPrettyFormat().format(document);
   return xml;
 }

I included an element called base to give the option to create more than one table within the bounds of the base element.  On line 13 I added a check to see if the data type name was 'Date' so I could apply a date format accordingly.  Other checks could be added for numbers or boolean.

I ran the above script against a table of mortgage interest rates.  The XML output is shown below:

XML Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?xml version="1.0" encoding="UTF-8"?>
<foo:base xmlns:foo="http://bradsmithcpa.com">
  <table>
    <row>
      <col-1>Date</col-1>
      <col-2>Value</col-2>
    </row>
    <row>
      <col-1>1991-09-01</col-1>
      <col-2>8.67</col-2>
    </row>
    <row>
      <col-1>1991-10-01</col-1>
      <col-2>8.48</col-2>
    </row>
    <row>
      <col-1>1991-11-01</col-1>
      <col-2>8.33</col-2>
    </row>
    <row>
      <col-1>1991-12-01</col-1>
      <col-2>8.07</col-2>
    </row>
    <row>
      <col-1>1992-01-01</col-1>
      <col-2>8.01</col-2>
    </row>
  </table>
</foo:base>

Note that Google Apps Script does not support XSL transformations, at least not in any way I could find.  After much research on the matter I determined it would be best to write a custom script to apply my own transformation.  To make the XML HTML compliant, replace the row and column elements with TR and TD tags. See the script below I wrote for this purpose.

createHTMLtablesFromXML


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
function createHTMLtablesFromXML(){
  var ss = SpreadsheetApp.openById('Spreadsheet Id Goes Here');
  var sheet = ss.getSheetByName('Sheet Name Goes Here');
  var xml = createXml(ss, sheet);
  //count the number of rows
  var rows = (xml.match(/<row>/g) || []).length
  //replace all <row> values with <tr>
  var newtable = xml.replace(/<row>/g,'<tr>');
  //replace all </row> values with <tr>
  newtable = newtable.replace(/<\/row>/g,'</tr>');
  //count the number of columns
  var i = 0, j = 1                            
  while (j != -1) {i+=1; var str = 'col-' + i; 
   j = newtable.indexOf(str);} var cols = i - 1;
  //make col-# a class element, and replace values with <td>
  for(var i = 1; i<=cols; i++){
    var colid = 'col-' + i
    var expr = new RegExp('<' + colid + '>','g');    
    newtable = newtable.replace(expr,'<td class="' + colid + '">')
    var expr = new RegExp('</' + colid + '>','g');
    newtable = newtable.replace(expr,'</td>')
    var expr = new RegExp('<' + colid + ' />','g');
    newtable = newtable.replace(expr,'<td class="' + colid + '" />')
  }
 //add id element to rows
    for(var i = 1; i<=rows; i++){
    var rowid = 'rows-' + i

   newtable = newtable.replace(/<tr>/,'<tr id="' + rowid + '">');
  
  return newtable;
}

I included createXML in the above function.  The spreadsheet (line 2) and sheet (line 3) objects are passed to the createXML function (line 4) which returns the XML string to the variable, xml. The rest of the script substiutes html tags for the tags used in this XML example.  Class elements were assigned to each column by column number with the prefix, col- (lines 12-24).  Id elements were added for each row by row number with the prefix rows-  (lines 26 - 30).  The class and id elements were added so CSS styling could be applied to the table.  The function returns newtable. The variable, newtable, contains a string that can be added to a text file to create an html page as shown below:

HTML Output


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<head>
<style>
table {
    border-collapse: collapse;
}
td {
    background-color: linen;
    border-style: solid;
    border-width: thin;
    border-color: black;
    padding: 2px 6px;
}

td.col-2 {
    text-align: right;
}

#rows-1 td {
  font-weight: bold;
  background-color: maroon;
  color: white;
  border-color: white;
  text-align: left;
}
</style>

</head>
<?xml version="1.0" encoding="UTF-8"?>
<foo:base xmlns:foo="http://bradsmithcpa.com">
  <table>
    <tr class="alt" id="rows-1">
      <td class="col-1">MORTGAGE15US</td>
      <td class="col-2" />
    </tr>
    <tr class="alt" id="rows-2">
      <td class="col-1">lin</td>
      <td class="col-2">Percent</td>
    </tr>
    <tr class="alt" id="rows-3">
      <td class="col-1">M</td>
      <td class="col-2">Monthly</td>
    </tr>
    <tr class="alt" id="rows-4">
      <td class="col-1">1991-08-01</td>
      <td class="col-2">2014-10-01</td>
    </tr>
    <tr class="alt" id="rows-5">
      <td class="col-1">15-Year Fixed Rate Mortgage Average in the United States©</td>
      <td class="col-2" />
    </tr>
    <tr class="alt" id="rows-6">
      <td class="col-1">Freddie Mac</td>
      <td class="col-2" />
    </tr>
    <tr class="alt" id="rows-7">
      <td class="col-1">date</td>
      <td class="col-2">value</td>
    </tr>
    <tr class="alt" id="rows-8">
      <td class="col-1">1991-09-01</td>
      <td class="col-2">8.67</td>
    </tr>
    <tr class="alt" id="rows-9">
      <td class="col-1">1991-10-01</td>
      <td class="col-2">8.48</td>
    </tr>
    <tr class="alt" id="rows-10">
      <td class="col-1">1991-11-01</td>
      <td class="col-2">8.33</td>
    </tr>
    <tr class="alt" id="rows-11">
      <td class="col-1">1991-12-01</td>
      <td class="col-2">8.07</td>
    </tr>
    <tr class="alt" id="rows-12">
      <td class="col-1">1992-01-01</td>
      <td class="col-2">8.01</td>
    </tr>
  </table>
</foo:base>

CSS Styling was added at the top of the file.  The following is what the HTML page looks like rendered through the browser:

Rendered HTML Page





on
Labels: ,