If you use Google Sheets to manage your customer database or your orders, discover without further delay our solution to connect your spreadsheet to our AntsRoute route optimization software. In this article, we present you the few steps to follow.
How does the connection work?
Let's take the example of an industrial manufacturer who makes deliveries of pallets to customers. Deliveries are planned 5 days in advance, but as the delivery date is approaching, the quantities of pallets can fluctuate depending on available stocks. The manufacturer uses a Google Sheets table to keep track of their orders. Here is an example of the table:
💡 The first column indicates whether the change has been sent to AntsRoute.
💡 The columns “Phone” and “Mobile” should be in “Number” format. The columns “Starting time slot” and “Ending time slot” must be in “Text” format.
💡 If an order is updated, the operations manager adds “0” in the first cell of the edited line. Google Sheets automatically sends a new order to AntsRoute and deletes the old one. Once it is done, the first cell changes from “0” to “1”.
💡 From the AntsRoute interface, the operator must complete or re-optimise the routes on the day concerned in order to reassign the edited orders.
How to connect Google Sheets to AntsRoute ?
Step 1: Retrieve an AntsRoute API key
- From your AntsRoute account, click on your initials at the top right.
- Click on “Integrations”.
- Click on “API keys”.
- Click on the adding button “+”.
- Fill in the “Name”.
- Click on the button “Create”.
- Click on the button “Copy”.
- Click on the button “Continue”.
💡 You will be asked for the API key in the next step. By then, paste this key into a document.
Step 2: Set up App Script
- From your Google Sheets table, retrieve the file ID.
- Retrieve the name of your spreadsheet.
- Click on the button “Extensions”.
- Click on “App Script”.
- Retrieve the script located at the end of this article, click on the “Editor” tab > “Code.gs” and paste the script.
- In the code
const spreadsheetId = XXXXXreplace “XXXXX” with the Google Sheets file ID. - In the code
const sheetName = YYYYYreplace “YYYYY” with the name of your spreadsheet. - In the code
cakey = ZZZZZreplace “ZZZZZ” with the AntsRoute API key. - Set the trigger by clicking on the stopwatch icon in the side menu.
- Click on the button “Add trigger” at the bottom right.
💡 You must replace “ZZZZZ” with the AntsRoute API key in two places in the script.
function PostAntsRoute() {
// Replace `spreadsheetId` and `sheetName` with the ID and name of your Google Sheets document
const spreadsheetId = "XXXXX";
const sheetName = "YYYYY";
// Get the sheet by ID and name
const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
// Get the number of rows in the sheet
const numRows = sheet.getLastRow();
// Loop on the Modified Value
for (let i = 2; i <= numRows; i++) {
var ModifiedRowValues = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0];
if (ModifiedRowValues[0]===0){
// Define the variables that will be used in the request
// You can find the values from google sheet column number-1
var DeliveryId = ModifiedRowValues[1];
var ScheduleDate = Utilities.formatDate(ModifiedRowValues[2], "GMT+1", "YYYY-MM-dd");
var CustomerExternalId = ModifiedRowValues[3];
var Prenom = ModifiedRowValues[4];
var Nom = ModifiedRowValues[5];
var Telephone = "+33"+ModifiedRowValues[6];
var Mobile = "+33"+ModifiedRowValues[7];
var Courriel = ModifiedRowValues[8];
var Adresse = ModifiedRowValues[9];
var Code_postal = ModifiedRowValues[10];
var Ville = ModifiedRowValues[11];
var Code_acces = ModifiedRowValues[12];
var Duree = ModifiedRowValues[13];
var DebutCreneau= ModifiedRowValues[14];
var FinCreneau= ModifiedRowValues[15];
var Commentaires = ModifiedRowValues[16];
var Palette = ModifiedRowValues[17];
var Poids = ModifiedRowValues[18];
var Description = ModifiedRowValues[19];
var Reference = ModifiedRowValues[20];
// Define the URL for the request to Delete the current order in AntsRoute
var url1 = "https://app.antsroute.com/capi/order/external-id/"+DeliveryId+"?purge=true";
var options1 = {
"method": "DELETE",
"headers": {
"Accept": "application/json",
"cakey": "ZZZZZ"
}
};
//Define the URL for the request to add the modified order in AntsRoute
var url2 = "https://app.antsroute.com/capi/order/planning";
var options2 = {
"method": "POST",
"headers": {
"Content-Type": "application/json",
"Accept": "application/json",
"cakey": "ZZZZZ"
},
"payload": JSON.stringify({
"type": "DELIVERY",
"scheduleDate": ScheduleDate,
"duration": Duree,
"externalId": DeliveryId,
"comments": Commentaires,
"customer": {
"lastName": Nom,
"firstName": Prenom,
"address": Adresse+" "+Code_postal+" "+Ville,
"phoneNumber": Telephone,
"mobileNumber": Mobile,
"email": Courriel,
"externalId": CustomerExternalId,
"customFields": [
{
"name": "Code d'accès",
"value": Code_acces
}
]
},
"customFields": [
{
"name": "Référence",
"value": Reference
},
{
"name": "Description",
"value": Description
}
],
"capacities": [
{
"capacityName": "Poids",
"capacityValue": Poids
},
{
"capacityName": "Palette",
"capacityValue": Palette
}
],
"timeSlot": {
"start": DebutCreneau,
"end": FinCreneau
},
"loading": {
"location": {
"name": "Warehouse A",
"address": "Nantes, France"
},
"duration": 10,
"timeSlot": {
"start": "06:00",
"end": "18:30"
}
}
})
};
//Send the request to Delete
try {
var response1 = UrlFetchApp.fetch(url1,options1);
var responseBody1 = response1.getContentText()
}
catch (error) {
Logger.log(error.message);
Logger.log(error.response1);
}
//Send the request to Post
try {
var response2 = UrlFetchApp.fetch(url2,options2);
var responseBody2 = response2.getContentText();
var data2 = JSON.parse(responseBody2)
}
catch (error) {
Logger.log(error.message);
Logger.log(error.response2);
}
//Change the state of the order in google sheet: "the modifications are in AntsRoute"
let range = sheet.getRange(i,1);
range.setValue('1')
}
}
}