Google Sheets Mapping Data: Telling the Agent What to Sync

Modified on Tue, 19 Aug at 6:02 PM

Data mapping is a critical step in configuring the integration between the PortPro TMS and a selected Google Sheet. During this phase, each field from the PortPro TMS—such as "Load Number," "Container Status," or "Customer Name"—must be aligned with the appropriate columns in the chosen Google Sheet worksheet. Proper mapping ensures accurate data placement and effective synchronization.

Understanding Data Mapping

Data mapping serves as a set of instructions for the AI agent. For example, the "Load ID" field in the PortPro TMS may be configured to populate Column A in a Google Sheet, or into a custom-named column such as "Shipment Identifier."

Using the AI-Assisted Mapping Feature

To streamline the mapping process, the Google Sheets AI Agent may offer an AI-assisted mapping feature, which can include:

Automatic Suggestions: Based on the existing column headers in the selected Google Sheet worksheet, the agent may suggest field-to-column mappings based on logical matches.
Interactive Chat Interface: In some versions, an interactive chat interface may be available where mapping preferences can be described, and the AI will assist in building the configuration.

Note: AI-generated suggestions should always be reviewed manually. Final adjustments can be made to ensure the mappings align with intended outcomes.

The Crucial Role of the Primary Key

As part of the mapping setup, it is necessary to designate one column in the worksheet as the Primary Key. This is a vital requirement.

What is a Primary Key?
A Primary Key is a column that contains a unique value for each row. Examples include "Load Number," "Container ID," or another unique identifier used within the TMS.
Why is it important?
The AI agent relies on the Primary Key to:
- Uniquely identify each row in the Google Sheet.
- Accurately update existing records when TMS data changes.
- Properly insert new rows for new TMS records without duplication.
- Delete rows in the Google Sheet when corresponding records are removed from the TMS.
- Enable the polling feature for intelligent record matching.

Important: The column selected as the Primary Key must contain only unique values for all synchronized records. Duplicate values in this column can result in synchronization errors or incorrect updates.

Reviewing and Confirming Mappings

Once all field mappings have been defined and the Primary Key has been selected, a thorough review is essential.

Confirm that each TMS field is correctly aligned with the intended column in the worksheet.
Verify the Primary Key assignment.
After confirming accuracy, save the mapping configuration.

Saving the configuration will typically initiate the data synchronization process between the PortPro TMS and the designated Google Sheet.

Mappable Data:

Load #	A unique reference number assigned to the load
Customer	The name of the customer who requested the load
Branch	The branch of the customer who requested the load
Pick Up Location	The location where the load will be picked up from
Delivery Location	The location where the load will be delivered to
Return Location	The location where the load will be returned to
Hook Chassis Location	The location where the hook chassis will be located
Terminate Chassis Location	The location where the chassis will be terminated
Freight Hold	Freight status for the load
Custom Hold	Custom hold status
Carrier Hold	The carrier hold status of the load
Broker Hold	The broker hold status of the load
Vessel ETA	Information about the vessel transporting the load
Last Free Day	The last day the load can be stored without incurring additional charges
Discharged Date	The date when the load was discharged
Outgate Date	The date when the load exited the gate
Empty Date	The day the container became empty
Ingate Date	The date when the load entered the gate
Ready to Return Date	The date when the load is ready for return
Loaded Date	The date when the load was loaded
ERD	The date when the container is available for return
Cut Off	The date when the load is due to be picked up
Date Returned	The date when the load was returned
Container #	The unique identifier for the container used in the load
Container Size	The size of the container used in the load
Container Type	The type of container used in the load
Container Owner	The owner of the container used in the load
Chassis #	The unique identifier for the chassis used in the load
Chassis Size	The size of the chassis used in the load
Chassis Type	The type of chassis used in the load
Chassis Owner	The owner of the chassis used in the load
Genset #	The unique identifier for the genset used in the load
Temperature	The temperature of the container
Route	The route of the load
SCAC	The SCAC of the load
Tare Weight	The tare weight of the load
Hazmat	Whether the load is a hazardous material
Overweight	Whether the load is overweight
Overheight	Whether the load is overheight
Liquor	Whether the load contains liquor
Hot	Whether the load is urgent
Genset	The genset of the load
Street Turn	The street turn of the load
House Bill Of Lading	The house bill of lading of the load
Booking #	The booking number of the load
Reference #	The secondary reference number of the load
Vessel Name	The name of the vessel transporting the load
Voyage	The voyage number of the load
Purchase Order #	The purchase order number of the load
Shipment #	The shipment number of the load
Pick Up #	The pick up number of the load
Appointment #	The appointment number of the load
Return #	The return number of the load
Reservation #	The reservation number of the load
Seal #	The seal number of the load
Master Bill Of Lading	The master bill of lading of the load
Delivery Reference #	The delivery reference number of the load
Ready for Pickup	Whether the load is ready for pickup
Gray Chassis #	The unique identifier for the gray chassis used in the load
Gray Chassis Size	The size of the gray chassis used in the load
Gray Chassis Type	The type of gray chassis used in the load
Gray Chassis Owner	The owner of the gray chassis used in the load
Status	Current load status (e.g. dispatched, enroute to deliver)
Load Type	Type of the load (IMPORT, EXPORT, ROAD, BILL ONLY)
Billing Date	The date when the load was billed
Available Date	The date when the load is available
Pick Up Apt From	The start time of the load pick up appointment
Pick Up Apt To	The end time of the load pick up appointment
Delivery Apt From	The start time of the load delivery appointment
Delivery Apt To	The end time of the load delivery appointment
Return Apt From	The start time of the load return appointment
Return Apt To	The end time of the load return appointment
Per Diem Free Day	Last Date to return the container to port for free
Trailer #	The unique identifier for the Trailer used in the load
Trailer Size	The size of the trailer used in the load
Trailer Type	The type of trailer used in the load
Delivered To Unload Date	The date when the load was delivered to the unload location
Load Completed Date	The date when the load was completed
Current Location	The last known location of the load
Lift Off Reference #	The lift off reference number of the load
Pieces	The commodity and the number of pieces
Delivery Arrival Time	The time of the delivery arrival for the load
Driver Notes	The notes for the driver of the load
Yard Notes	The notes for the yard of the load
Load Notes	The notes for the load