INDUSTRY RESEARCH

Dealernews Research By Don Musick

UNDER THE HOOD This Is Where The Fun Really Begins! Part II

Yikes! What does all this stuff mean? Not to worry, for us it will just be a copy and paste exercise! Obviously I don’t expect you to manually pound this code into the Apps Script Editor, so here’s a link to the code source where you can grab the necessary code and paste it into the Editor (code source). The code is easily copied to the clipboard by clicking on the “copy” icon in the code window as shown below.

Now let’s switch back to the Apps Script Editor in your browser and delete the three lines of code in the “Code. gs” page. Finally, right-click in the blank code window and select “Paste”.

With the GOOGLEMAPS Apps Script pasted into the Editor, you’ll notice that the name of the script shown in the Editor Tab and side margin still reads”Code.gs “. To give the script a more meaningful name, click the drop-down icon next to the margin title and select “Rename”. In this example we’ve renamed the script “Drive Time Calc”.

To save the Project and Apps Script code to Google Drive, you can select File>Save from the Editor menu or click on the “Save” icon on the Editor Toolbar. This ensures that the GOOGLEMAPS.gs script will always be associated with the “Test Retail” Google Sheet.

Once again, the procedure above is a lot to unpack, but fortunately the script link above also includes a “how to” explanatory YouTube video (the first video). If you’re really into understanding how the GOOGLEMAPS Apps Script works, you can check out the second video.

TAKE ME HOME COUNTRY ROAD

Returning to the “Test Retail” Google Sheet, we’ll add two new data columns that will work with the GOOGLEMAPS Apps Script. As outlined above the script requires a start address (customer) and end address (dealer). It also requires a third text parameter (return type) which tells the script what sort of data is returned by the Google Maps routing service. This parameter can take on any of four different values: 1) miles, 2) kilometers, 3) minutes or 4) hours. The script requirements are nicely summarized at the top of the GOOGLEMAPS Apps Script as shown below.

The two new columns are labeled “Drive Time (Min)” and “Drive Time (Hrs)” (only the first column will use the Apps Script). In the first row cell of the of the “Drive Time (Min)” column, the following formula is entered: =GOOGLEMAPS(k2,l2,”minutes”) as shown here. After pressing the Enter key, the cell will display “….loading” after which the shortest drive time between the two addresses will appear in minutes. The Google Maps routing service calculates the minimal drive time between addresses taking into account current traffic conditions. Consequently, drive times may vary depending on the time of day. Finally, the Apps Script formula can be copied and pasted into the remaining records as outlined above. Note that a simple formula of “=Drive Time (Min)/60” is used to calculate the last column. Again, I recommend copying both of these formula columns and pasting their corresponding text values to avoid repetitive queries to the Google Maps routing service which does have a daily quota.

Take a deep breath Padwan! If you’ve made it this far you’re ready to take the next step: “TO INFINITY AND BEYOND” (This is where the fun actually begins).

From his first motorcycles (a Honda S65 and an S90) when he was 16 to 50 years later, Don Musick has never stopped twisting the throttle. Although his accomplishments in the research arena have surpassed his MX career Don has over 25 years experience with major manufacturers in the Powersports and Automotive industries specializing in e-business solutions for retail distribution networks. His solution portfolio includes the development and implementation of manufacturer/dealer extranets, consumer-direct commerce portals, manufacturer/dealer e-channel integrations as well as development of web-based sales force automation tools. For most of his career, Don has been fascinated (his wife would say obsessed) with geographic market analytics, dealer location planning and sales territory optimization. He founded Genesys Technology Solutions (GenesysTech) http://www.genesystech.com/ to develop new tools and market intelligence products to help manufacturers understand the competitive landscape of their industries, recognize opportunities and grow their businesses. A Spartan to the core, Don earned a B.S. in Physiology and PhD in Biochemistry from Michigan State University. Contact: dmusick@genesystech.com