Blue API icon on purple background

Using the MyGeotab python API to create custom reports

Published on October 22, 2018 in Productivity by Kaylan Mettus |  3 minute read


Learn how to customize the data in your MyGeotab fleet reports using the MyGeotab Python API. Read how to pull in data and create a report.

Every MyGeotab database comes with a set of built-in Excel reports that allow users to access and analyze their data. For customers who want to change these reports, we allow our templates to be adjusted, downloaded and reuploaded.


However, the raw data available within the stock report may not contain the data points each user needs, at which point you may need a seperate custom report.


Using MyGeotab Python Application Programming Interface (API), customers and resellers can easily pull in the data they want and add it to a spreadsheet. The general workflow outlined below will work with any of our APIs. (Read the Beginner’s Guide to APIs here). Python also has different libraries available that make the process easy and simple to use for everyone.


See Also: Excel Tips for Fleet Reporting: Top Formulas and Functions

Getting Started with the MyGeotab Python API

You will need to have Python installed on your computer. Geotab currently supports Python 2.7.9+, 3.4+ and pypy 4+.


You can install our API easily with pip:


$ pip install mygeotab


The source code as well as more examples can be found on the git repository.


A good development environment makes writing, testing and running scripts easier. A few options include:

  • IDLE (includes Python install, a shell and editor for writing and running code)
  • PyCharm (JetBrains’ Python IDE, with all the features you’re familiar with from IntelliJ IDEA)
  • Atom (portable, customizable and useable for a wide variety of languages)
  • Spyder (Python only, geared towards data science projects)

To get the most out of your experience, it is recommended you use other Python libraries to make working with your data easier. 


Here are some libraries you can install:

  • Openpyxl (for reading and writing Excel files, keep in mind this will not work with older .xls file extension)
  • Xlrd (another general option for working with Excel files)
  • Pandas (a popular option for data science/big data projects, it makes working with data frames easy)
  • GGmatplotlib (this can be used to generate graphs for insertion into spreadsheets)

Pulling in Data

Once you’ve set up your development environment, you’ll need to decide what data you’re going to display. You can find documentation on available SDK objects and searches on our API reference page.


Here’s an example of polling for raw odometer data:


results = api.get("StatusData", search = {"fromDate": "2018-09-01T00:00:00.000Z",

"diagnosticSearch": {"id": "DiagnosticRawOdometerId"}})


The overall structure of Get and other calls will be the same as our Javascript API.


Note that to moderate the size of data returned, nested objects will only contain the ID, and no other fields from that object (take a look at our building block approach). For example, if you are trying to pull duty status logs, the driver ID will be populated but another Get call would be necessary to attain the driver’s name or license number.


logs = api.get("DutyStatusLog", search = {"fromDate": "2018-09-01T00:00:00.000Z",

"deviceSearch": {"id": device['id']}})


for log in logs:


driver = api.get("User", resultsLimit=1, search={"id": log['driver']['id']})

Creating Your Report

Now that you have your data, it’s time to create a spreadsheet. There are a lot of options to consider such as how many columns and sheets you want, if you need any chart object and the file type you would like to use. Depending on your answers, some libraries may be easier to work with than others.


For example, Pandas has a very simple method to write a data frame to a .csv file.


df = pandas.DataFrame(logs)

df.to_csv(r"C:\Users\kaylanmettus\Desktop\myCSV.csv")


Openpyxl has built-in support for creating simple charts and inserting them in one of your sheets. You can find out more information on Openpyxl here.


In general, the structure of an Excel spreadsheet object is as follows:

  • The workbook is the overall file.
  • Each workbook contains one or more worksheets.
  • Each worksheet contains a grid of cells that will contain your data values. A worksheet may also contain a chart object that is anchored on a cell.

Making a report from scratch provides a lot of flexibility in both the design of the spreadsheet and the way it can be used.

Another factor to consider is when and how your script will be run. Do you need to pull data and email the report every day? In that case, you might want to automate this process and set your script to run as a Windows service or with Windows Task Scheduler.


Or, do you want to reduce the number of files you’re maintaining on a regular basis? If so, store one spreadsheet and append your daily, weekly or monthly numbers to the existing one instead of generating new reports.


You can use Openpyxl to do this:


book = openpyxl.load_workbook(workbookFilePath, data_only=True)

sheet = book["mySheet"]


for user in users():


sheet.append(user)


If you need your script to be portable and available to others within an organization, consider using a Python notebook instead of code stored on your local machine. You can also find more Python examples on the github page.


To learn more about custom reporting, watch this video tutorial on the basics of custom reports and go to the Geotab Marketplace to browse additional reports available for download.


If you have any questions, ideas or suggestions, visit the SDK forum or comment down below.


Read more from Developers:

Validating Data on the Geotab GO Device

Get Started With the MyGeotab API Wrapper


If you liked this post, let us know!


Disclaimer

Geotab's blog posts are intended to provide information and encourage discussion on topics of interest to the telematics community at large. Geotab is not providing technical, professional or legal advice through these blog posts. While every effort has been made to ensure the information in this blog post is timely and accurate, errors and omissions may occur, and the information presented here may become out-of-date with the passage of time.

Get industry tips and insights

Sign up for monthly news and tips from our award-winning fleet management blog. You can unsubscribe at any time.

Republish this article for free

Other posts you might like

Construction worker looking over at something

Routes to riches – Geotab Routing and Optimization drives operational efficiency and cost management

Geotab's Routing and Optimization software blends economic intelligence with operational strategy, reshaping fleet management for improved cost and resource efficiency.

February 15, 2024

A lineup of white trucks in a parking lot

How to rightsize your fleet in five simple steps

Fleet rightsizing is an important strategic exercise that ensures your assets are sufficiently utilized and that you have the right type of vehicle, at the right locations, available at the right time.

December 18, 2023

No idling sign on

A complete guide to fleet idling: Understand, detect and stop true idling

Idling increases fuel consumption, CO2 emissions, and maintenance costs. Learn how to control it to lower your fuel spend and make your fleet more sustainable.

December 15, 2023

A fuel nozzle filling a truck

Strategies to increase fuel efficiency and manage fuel costs

Discover how to reduce fuel costs when gas prices are high.

November 2, 2023