Integrating Google Docs Spreadsheet with Google Maps

Suppose you want to keep a Google Docs Spreadsheet to track your mileage and travel for business expenses.

VIEW DIRECTIONS XML
You can use the Google Maps API as follows. Open Google Chrome and enter the following address:
maps.googleapis.com

If you look at the target anchor, you will see origin=%22…%22&destination=%22…%22. Currently, the first … is a Starbucks location at Berryessa and Lundy in San Jose, CA and the second … is a Starbucks location at Alum Rock and White in San Jose, CA. %20 is a space. You can change this as you would like, but this will show an xml formatted “DirectionsResponse”.

If you look at the xpath “//DirectionsResponse/route/leg/duration/value” and “//DirectionsResponse/route/leg/duration/text” you will see 499 and 8 mins which reflect the time. If you look at the xpath “//DirectionsResponse/route/leg/distance/value” and “//DirectionsResponse/route/leg/distance/text” you will see 6846 and “4.3 mi” which reflect the distance.

ADD MILES CALCULATION DIRECTLY IN GOOGLE DOCS SPREADSHEET
In a Google Docs Spreadsheet, you can place in A1: 3103 Alum Rock Ave, San Jose, CA 95127
You can place in A2: 1712 Berryessa Rd, San Jose, CA 95133
Then, in A3: =importxml(“http://maps.googleapis.com/maps/api/directions/xml?origin=” & A1 & “&destination=” & A2 & “&sensor=false&alternatives=false”,”//DirectionsResponse/route/leg/distance/text”)

Or alternatively in A3: =importxml(“http://maps.googleapis.com/maps/api/directions/xml?origin=” & G213 & “&destination=” & H213 & “&sensor=false&alternatives=false”,”//DirectionsResponse/route/leg/distance/value”)/1000*.621371

The value is meters, so divide by 1000 to get kilo meters and then multiply by .621371 since 1 kilo meter is .621371 miles.

This was from productforums.google.com.

Adding links is easy as well (see gearside.com and productforums.google.com).

VIEW PLACE PAGE (Can be used on your phone to navigate to the location by hitting directions)
Open www.google.com in Google Chrome.

ADD HYPERLINK TO LOCATION IN GOOGLE DOCS
=Hyperlink(“www.google.com/maps/place/” & A1, “GO”)

EXAMPLE SPREADSHEET


In A1: Date
In B1: Start
In C1: Nav
In D1: Destination
In E1: Nav
In F1: Distance
In G1: Time
In H1: Purpose
In A2: 07/31/2014
In B2: 1712 Berryessa Rd, San Jose, CA 95133
In C2: =Hyperlink("www.google.com/maps/place/" & B2, "GO S")
In D2: 3103 Alum Rock Ave, San Jose, CA 95127
In E2: =Hyperlink("www.google.com/maps/place/" & D2, "GO D")
In F2: =importxml("http://maps.googleapis.com/maps/api/directions/xml?origin=" & B2 & "&destination=" & D2 & "&sensor=false&alternatives=false","//DirectionsResponse/route/leg/distance/value")/1000*.621371
In G2: =importxml("http://maps.googleapis.com/maps/api/directions/xml?origin=" & B2 & "&destination=" & D2 & "&sensor=false&alternatives=false","//DirectionsResponse/route/leg/duration/text")
In H2: Appointment

This will calculate trip mileage and allow you to pull up recent locations to directly navigate when to recent locations.

Capture

Advertisements

A Simple Image Overlay Example

From jquerytools.org

This example uses jquery 1.2.7. Create a file “test.html” and edit with a text editor. Add the following code. You will need to add an image “myimage.png” to the same directory.


<html>
    <head>
        <meta charset="UTF-8" />
        <title>Image Overlay Example</title>
		<script src="http://cdn.jquerytools.org/1.2.7/full/jquery.tools.min.js"></script>

	</head>
    <body>
<p>
	<button class="modalInput" rel="#myID">Show Image</button>
	<!-- yes/no dialog -->
	<div class="modal" id="myID" style="display:none;padding:15px;border:2px solid #333;-webkit-border-radius:6px;">
		<image src="myImage.png" />
		<br>
		<button class="close">Close</button>
	</div>
</p>

<script>
$(document).ready(function() {
	$(".modalInput").overlay({
		// some mask tweaks suitable for modal dialogs
		mask: {
			color: '#ebecff',
			loadSpeed: 200,
			opacity: 0.9
			},
		closeOnClick: false,
		fixed: false
	});
  });
</script>
    </body>
</html>

Working with JQuery, JSFiddle and Highcharts

At www.highcharts.com, there are several possible javascript charts that can be used to visualize data. Click demo, highcharts demos. Click on edit in JSFiddle to go to jsfiddle.net. In order to get this example working, you will need to include three things: 1) JQuery, 2) Javascript and 3) HTML.

Create a new file named “highcharts.html” and edit it with a text editor like Notepad or Notepad++. Copy the following template:


<html>
<!--INCLUDE_JQUERY-->
<script type="text/javascript">
<!--INCLUDE_JAVASCRIPT-->
</script>
<body>
<!--INCLUDE_HTML-->
</body>
</html>

Insert code to include JQuery on the line after the INCLUDE_JQUERY marker. In this JSFiddle example, they reference JQuery 1.9.1 so you would add the following:


<!--INCLUDE_JQUERY-->
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>

You might have to google “Include JQuery 1.9.1” or such for other versions since JQuery 1.4 is included with the following:


<!--INCLUDE_JQUERY-->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>

Next, insert the Javascript code on the line after INCLUDE_JAVASCRIPT and the html code on the line after INCLUDE_HTML. This will give you the following html file:


<html>

<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script type="text/javascript">
$(function () {
        $('#container').highcharts({
            title: {
                text: 'Monthly Average Temperature',
                x: -20 //center
            },
            subtitle: {
                text: 'Source: WorldClimate.com',
                x: -20
            },
            xAxis: {
                categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                    'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
            },
            yAxis: {
                title: {
                    text: 'Temperature (°C)'
                },
                plotLines: [{
                    value: 0,
                    width: 1,
                    color: '#808080'
                }]
            },
            tooltip: {
                valueSuffix: '°C'
            },
            legend: {
                layout: 'vertical',
                align: 'right',
                verticalAlign: 'middle',
                borderWidth: 0
            },
            series: [{
                name: 'Tokyo',
                data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]
            }, {
                name: 'New York',
                data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
            }, {
                name: 'Berlin',
                data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
            }, {
                name: 'London',
                data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8]
            }]
        });
    });
</script>
<body>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
</body>
</html>

You can then open this html file in firefox to see the basic chart demonstrated by the demo.

Pulling out the actual variables from the highcharts code, we create variables for chartTitle, chartSubTitle, yAxisTitle, monthsArray and seriesJSONArray. This might yield the following code:


<html>
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script type="text/javascript">
var chartTitle = 'Financial Tracking';
var chartSubTitle = '2014';
var yAxisTitle = 'Totals';
var monthsArray =  ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'];
var seriesJSONArray = [];
seriesJSONArray[0] = new Object();
seriesJSONArray[0].name = 'Debt'
seriesJSONArray[0].data = [-61958, -57724, -54632, -53689, -34209, -36329];
seriesJSONArray[1] = new Object();
seriesJSONArray[1].name = 'Net Worth'
seriesJSONArray[1].data = [-41794, -38026, -33297, -31256, -25154, -26260];

$(function () {
        $('#container').highcharts({
            title: {
                text: chartTitle,
                x: -20 //center
            },
            subtitle: {
                text: chartSubTitle,
                x: -20
            },
            xAxis: {
                categories: monthsArray
            },
            yAxis: {
                title: {
                    text: yAxisTitle
                },
                plotLines: [{
                    value: 0,
                    width: 1,
                    color: '#808080'
                }]
            },
            legend: {
                layout: 'vertical',
                align: 'right',
                verticalAlign: 'middle',
                borderWidth: 0
            },
			series: seriesJSONArray
        });
    });
</script>
<body>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
</body>
</html>

This displays the following.

Financials