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





Google Apps Script - Create New File in Google Drive Folder

Using Google Apps Script ("GAS") can be an adventure in frustration.  A perfect example is when I tried to create a new Google Sheets file in a specific Google Drive Folder.  The sheet file was intended to house temporary files created when a particular script was ran.  The ability to store the file in a specific folder was needed to avoid the main folder from getting messy and difficult to administer.  Keeping the files in a specific folder makes clean up a lot easier.

Its pretty straightforward to create a file using the SpreadsheetApp service, but I learned you can't create a file directly in a specific folder without first creating the file in the main folder, copy the file to the folder you wanted and then deleting the old.  If you go through the DriveApp or DocsList chain it would appear that you can do this by specifying the mime type... except you cant without getting an error message.  This issue was addressed on the Google-Apps-Scripts-Issues blog here:  Issue 4080. After being kicked around a bit between users and the Google Project Team, Google officially stated that creating a spreadsheet file in a specific folder using DriveApp, DocList, or SpreadsheetApp couldn't be done.  However, it can be done with Advanced Drive Services.

Advanced Drive Services, like other Google advance services, must be manually enabled to turn it on.  It must also be enabled in the Google Developers Console.  Here are the steps to activate according to the documentation:
  1. In the script editor, select Resources > Advanced Google services....
  2. In the dialog that appears, click the on/off switch next to the service you want to use.
  3. At the bottom of the dialog, click the link for the Google Developers Console.
  4. In the new console, again click the on/off switch next to the service you want to use.
  5. Return to the script editor and click OK in the dialog. The advanced service you enabled will now be available in autocomplete.
Once activated, Advance Drive Services will be available in GAS by typing Drive and using auto-complete to navigate through the options.  Advanced Drive Services is not as intuitive as regular GAS services.  However, it does expand your capabilities.  Here's an example of a script I used to accomplish my original task:


function createSpreadsheetFileinFolder(){
var folderId = DocsList.getFolder('YOUR_FOLDER_NAME').getId()  
var file = {
    "title": "MY_SPREADSHEET_NAME",
    "mimeType": MimeType.GOOGLE_SHEETS,
    "parents": [
      {
        "id": folderId
      }
    ]
  };
Drive.Files.insert(file)
}

Of course, the above script can be applied for Google Docs or about any file type as long as you specify the right mime type.




Storing Items With Over 100K Characters in Cache


Google Apps Script limits the number of characters stored to 102,400. If you try to store a value that exceeds this limit you will get the following error:

Argument too large: value (line ##, file scriptfilename)

This script overcomes that limit by breaking the string into chunks of individual strings that are each under the limit. If the string is already under the limit the string will be stored under the assigned key name. If the string goes over the limit the key name is appended by an index number starting at zero. For example, if a string has 250,000 characters and the key name is table, the string will be parsed and stored in cache in three strings with key names of table_0, table_1, and table_2. An index reference is also saved with the extension _idx so the program will know how many keys to retrieve. This function may be used on strings or JSON objects. The function getCacheChunks(key) is used to retrieve keys stored in cache using this method. Google Apps Script also limits the file size of files stored in cache to 100K. If this barrier is exceeded, try using a limit smaller than 100,000 characters. The following script stores values in user cache.  If you want to store on the document or script level change CacheService.getUserCache() to CacheService.getDocumentCache()  or CacheService.getScriptCache() , respectively. See Google's CacheServices documentation for more information.

putCacheChunks


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
function putCacheChunks(str,key,lim,exp) {
  var cache = CacheService.getUserCache();
  if(lim === undefined){lim = 100000} 
  if(exp === undefined){exp = ''} else{exp = ',' + exp}
  var len = str.length  
  var num = Math.floor(len/lim)
  var chunk = ''
  if(num == 0){cache.put(key,str)}else
    for(var a = 0; a<=num; a++){
      if(a == 0){cache.put(key + '_idx',num + exp)}
      chunk = str.slice(lim * a,lim * (a+1)); 
      cache.put(key + "_" + a , chunk +exp)}
  }

Arguments:
  • str (string): the value to be stored.  If storing an object use JSON.stringify() before passing the argument to the function.
  • key (string): the key name assigned to the string to be stored in cache
  • lim (integer): optional --Limit on the number of characters in each value stored.  The default is 100,000 (this can be changed in the script on line 4).  If the 100k file size limit is hit, try using a smaller limit.
  • exp (integer): optional -- The expiration in seconds.  The length of time before an item in cache expires.  The default  is 600 seconds (10 minutes).  
Return Values
  • None

Retrieving Items Stored In Cache Using putCacheChunks()

If values were stored in cache using putCacheChunks() then use this function to retrieve them.  If the value was stored as one single key, the value will be returned as originally stored. If the value was stored under multiple keys because the number of characters exceeded cache storage limits, the function will retrieve the values assigned to each key as individual strings and glue them together.  The combined string will be returned. If the resulting string is a JSON object, the object will be returned.

getCacheChunks


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
function getCacheChunks(key){
var cache  = CacheService.getUserCache()
var solo = cache.get(key)
var str = ''
if(solo){
  str = solo
}else{var num = cache.get(key + '_idx');
      if(!num){return null;}
      for(var a = 0; a<=num; a++){
        str += cache.get(key + '_' + a)
      }}
  try
  {var obj = JSON.parse(str); return obj;}
  catch(err)
  {return str;}
}

Arguments
  • key (string): the key name assigned to the string to be stored in cache
Return Values
  • If the key name could not be found, or if the key expired, null is returned.
  • If the string can be converted to an object using JSON, an object will be returned.
  • Otherwise the string will be returned in-tact as one value.  


Thursday, January 17, 2019

Blogger as a Subdomain

I wanted to set up a technology blog connected to my website so users don’t have to leave to access it.  Having a blog connected to my website would also help with search engine optimization.

One way to do this is to host the blog on my server in a sub-directory such as:

bradsmithcpa.com/tech

There are several software programs available for this purpose, such as WordPress. I installed and tested several different blogging platforms. Each platform was quite capable, but each had one or more shortcomings. I had experience with Google’s Blogging platform, but didn’t want user’s to leave my website. However I learned I can achieve what I wanted by using a subdomain with Blogger.

Subdomains appear before a domain name separated by a dot. The most common subdomain is “www”. I envisioned a blog with “tech” as the subdomain, so the URL would be

tech.bradsmithcpa.com

It is possible to use Blogger with a subdomain.  This article shows the required steps to achieve this.

Step 1: Create new blog


Go to blogger.com, click New Blog.

Create new blog on Blogger
Create new blog

Step 2: Give your blog a title and address


Fill in title and address then click "Create blog!".  The address isn't important at this step because you will add a redirect to your website and subdomain later.

Give your blog a title and address
Give your blog a title and address

Step 3: Skip adding a google domain


Google gives you the option to create a new domain.  Click "No Thanks" to skip this step.
Skip adding a google domain
Skip adding a google domain

Step 4: Click Settings


After Google creates your blog, you will be directed to the Blogger administration page for the site you just created.  Click settings before adding content.

Blogger> - Click Settings
Click Settings

Step 5: Click +add third party URL for your blog


Select Basic, and under Publishing Click +add third party URL for your blog

Blogger>Settings>Basic:Publishing:Blog Address - Click "+add third party url for your blog
Click +add third party URL for your blog

Step 6: Add your desired web address


Under third party domain settings, add your desired web address.  It should be follow the pattern: subdomain.domain as shown in the image below, then click save. This will be the URL for your new blog once all the steps have been completed.

Blogger>Settings>Basic:Publishing:Blog Address - Add your personal web address
Add your desired web address

Step 7: Return to the editing screen


Blogger will provide additional setup instructions by clicking edit next to the web address you just created.

Blogger>Settings>Basic:Publishing:Blog Address -  Return to the editing screen
Return to the editing screen

Step 8: Add CNAMEs to your domain's DNS settings


This is the step where you are instructed to setup two CNAMEs on your website.  The CNAMEs are needed to point your website to Blogger. The next steps provide guidance on how to do that.

Please note it may take a few minutes for Google to display the CNAME instructions shown below.  If it doesn't appear immediately go back to the page shown on Step 7 and repeat until the CNAME instructions are displayed.

Google support explains "A Canonical Name or CNAME record is a type of DNS record that maps an alias name to a true or canonical domain name. CNAME records are typically used to map a subdomain such as www or mail to the domain hosting that subdomain’s content. For example, a CNAME record can map the web address www.example.com to the actual web site for the domain example.com."  For more information click the link below:


Blogger>Settings>Basic:Publishing:Blog Address - Add CNAMES to your domain's DNS settings
Add CNAMEs to your domain's DNS settings


The following show how to create CNAMEs with cPanel, but the steps would be similar for other website hosting platforms.

Step 9: Go to cPanel and click Zone Editor


Log in to your website's administration page.  My website uses cPanel as the administration front-end.  Once there, click Zone Editor under the Domains section.

Go to cPanel and click Zone Editor
Go to cPanel and click Zone Editor

Step 10: Click Manage for the desired domain


At the Zone Editor screen click manage next to the domain used for the blogger account.

Click Manage for the desired domain

Step 11: Click the drop-down arrow for + Add Record and select Add CNAME Record


At the Zone Records page for the selected domain, click +Add Record.  On cPanel you can specify the type of record by clicking the +Add Record button's down arrow and choosing CNAME as shown.

On Cpanel>Zone Editor, click 'add record'
Click +Add Record on Zone Records screen

Step 12: Complete Name, TTL,  Type, and Record for the CNAME you want to add


Add the first CNAME specified in Step 8 a follows:
  • In the Name field enter the desired subdomain. I used Tech in my example.  If you're using a platform other than cPanel the fields may be labeled as Label, or Host. Then,
  • tab to TTL and enter 3600,
  • tab to Type and enter CNAME,
  • tab to Record and enter ghs.google.com. If you're using a platform other than cPanel the fields may be labeled as Destination, Target, or Points To. Then,
  • click Add Record
Blogger requires an additional CNAME for security purposes.  Add the second CNAME specified in Step 8 as follows:
  • In the Name field enter the second name.  My example starts with sol7.  If you're using a platform other than cPanel the fields may be labeled as Label, or Host. Then,
  • tab to TTL and enter 3600,
  • tab to Type and enter CNAME,
  • tab to Record and enter the second URL. My example starts with gv-z and ends with googlehosted.com. If you're using a platform other than cPanel the fields may be labeled as Destination, Target, or Points To. Then,
  • click Add Record

cPanel>Zone Editor>Zone Records - Complete fields to add a new CNAME Record
Complete fields to add CNAME

If all the steps were performed correctly your new blog should appear at you desired web address (shown in step 7).  Please note that it may take 15 minutes to 24 hours for the changes to CNAME to populate to the Internet's domain name servers, so initially the web site may not display.  Give it time.  If it doesn't work after a while double check your CNAME records as follows:
  • Make sure the type is CNAME
  • Double check the name and URL fields
  • Make sure you created two CNAME records