Follow

Automatically delete unwanted columns in task export

If you use the task export for generating route sheets, manifests, or labels, you may want to quickly remove columns that aren't relevant. Below are step by step instructions for creating an Excel macro inside a spreadsheet to automate the process. Once configured, simply paste data in your macro-enabled sheet and run the macro.

 

Step 1:

Download ‘Onfleet_Macro_Template.xlsm’ (attached at the bottom of the article) - this is a blank macro-enabled workbook and comes with the macro installed.  This will prevent you from having to copy/paste the macro text every time you want to run it.

Note: Make sure you download the file. Do not open in Google Sheets. 

 

Step 2:

Export tasks from the dashboard.  Open the CSV file and copy all the data, including the headers. This can be done with hotkeys:

  • MAC: HOLD ‘Cmd’ + ‘A’ and then ‘Cmd’ + ‘C’
  • Windows: HOLD ‘Control’ + ‘A’ and then ‘Control’ + ‘C’

 

Step 3:

Paste the data from the export into the ‘Onfleet_Macro_Template.xlsm’.  DO NOT select all to do this. Simply paste the data into the first cell ‘A1’.

 

Step 4:

Enter the Visual Basic Editor in the ‘Onfleet_Macro_Template.xlsm’.  To do this, select ‘Tools’ from the options taskbar in Excel, hover over the ‘Macro’ option, and select ‘Visual Basic Editor’.

 

Step 5:

Select the columns you’d like to include in the spreadsheet.  You can refer to the column numbers in the table below as a guide. In the fourth line in the Visual Basic Editor, enter the column numbers which you’d like to keep after the ‘S = ‘.  

For example, if you only want the columns: ‘Driver Number’, ’Task Number’, ‘shortId’, ‘status’  you would enter: s = “,1,2,3,4,”

Note: keep the commas as entered above

 

Step 6:

Exit out of the Visual Basic Editor.  To run the macro, go back to options and hover your cursor over ‘Macro’ and select ‘Macros’.  Select the macro ‘RemoveColumns’ and hit ‘Run’. If done correctly, the program should delete all the columns except those you have selected.  Please give Excel up to 30 seconds to execute the macro.

Save your new workbook as a different filename so you can reuse the workbook.  Now that you are configured, simply paste data in your macro-enabled sheet and run the macro whenever you want to remove the unnecessary columns!

 

Columns:

Column Name

Column Letter

Column Number

Driver Number

A

1

Task Number

B

2

shortId

C

3

status

D

4

creatorOrganization

E

5

executorOrganization

F

6

workerName

G

7

destinationAddress

H

8

destinationLonLat

I

9

recipientsNames

J

10

recipientsNumbers

K

11

completeBeforeTime

L

12

completeAfterTime

M

13

creationTime

N

14

startTime

O

15

completionTime

P

16

didSucceed

Q

17

completionNotes

R

18

completionLonLat

S

19

distance

T

20

taskDetails

U

21

recipientNotes

V

22

signatureUrl

W

23

photoUrl

X

24

feedbackRating

Y

25

feedbackComments

Z

26

forceCompletedBy

AA

27

startLonLat

AB

28

departureTime

AC

29

departureLonLat

AD

30

arrivalTime

AE

31

arrivalLonLat

AF

32

taskType

AG

33

dependencies

AH

34

failureReason

AI

35

barcodesCaptured

AJ

36

trackingViewed

AK

37

signatureText

AL

38

metadata

AM

39

 

 

Have more questions? Submit a request