The Onfleet import tool is a powerful way to get a large amount of delivery information into your dashboard with a single, batched (and in our opinion, magical) action. With an organized spreadsheet and a few clicks, dispatchers can import up to 5000 deliveries all at once.
Let’s take a look at a few tricks, that we will outline below, that will allow you to get the most out of our import feature in terms of speed and accuracy.
1. Convert street addresses into latitude and longitude coordinates with simple Google Sheets script
Onfleet best practices always include utilizing the latitude and longitude (lat/long) coordinates for an address instead of the text or human readable format. Having said that, we realize that most of you are humans, thus the majority of point of sale or customer facing software systems being used produce these human readable, text based addresses (i.e. 201 Market Street, San Francisco, CA 94111 versus –122.3965314,37.7927468).
There are a number of ways to programmatically convert text based addresses into the corresponding lat/long coordinates. For beginners, we suggest using this simple Google Sheets script (step-by-step guide) or something similar. Once installed in a workable Google Sheet, you can simply paste your text based addresses in one column and convert them with the plug-in that utilizes the script installed above.
By putting these lat/long coordinates into your Onfleet import sheet, the overall import speed and accuracy will be increased.
2. Convert the delivery windows to Unix timestamps
Unix time is a universal system to describe a point in time from the starting reference of January 1, 1970 (trivia question). This is different than the normal, timezone based, times that most people are more used to using. By utilizing Unix time within your Onfleet import sheet, dispatchers can avoid any issues that might arise from overlapping timezone conversions.
This Unix conversion can be executed right in your Excel or Google Sheet with the following steps:
- Within your import sheet, be sure to input the completeAfter and completeBefore columns to create a delivery window for each delivery.
- Create columns named unixCompleteAfter and unixCompleteBefore.
- In these unix columns, put the following formula to convert the time zone based times created in step one into Unix:
3. Utilize Zapier to programmatically import from a Google Sheet
The previous two tricks help to streamline the manual CSV import tool that, while useful (and still magical), will have your dispatchers importing tasks manually at the start of each and every day. The most powerful way for your business to interact with Onfleet is through an integration with our state of the art REST API.
With that said, here at Onfleet, we realize not all businesses are ready for that sort of investment. Until you’re ready to invest in the engineering work to take your business to the next level, we suggest utilizing a tool called Zapier to programmatically create tasks within your Onfleet dashboard and eliminate the manual import processes outlined above.
The general flow for an incredibly simple Zapier integration with your Onfleet dashboard would look something like this:
- One of your valued customers places an order through your Shopify, WooCommerce or similar, online store.
- With Zapier, a delivery task can be automatically created within your Onfleet dashboard, containing all necessary customer and delivery information. This creates a flow or stream of automatically populating orders, ready for delivery.
- At the beginning of a delivery day, your dispatchers log into Onfleet and simply route your drivers (or do this automatically with our Route Optimization tool) with the tasks that have been continuously and automatically created over the days leading up to the actual date of delivery.