function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{name : "Tracking all track codes (to the first empty cell)", functionName : "trackingAllToTheFirstEmpty"}, {name : "Tracking selection track code",functionName : "trackingSelected"},
{name: "Custom tracking range", functionName : "trackingAllCustom"}];
sheet.addMenu("Track", entries);
};
// Set required values
var trackingNumbersColumn = "A"; // Column with tracks
var retrackColumn = "B"; // Column with new tracks (consolidation tracks and retracks);
var dateTimeColumn = "C"; // Column with date;
var statusColumn = "D"; // Column with statuses;
var startRowForTrack = 2; // Row number with the first track code
function colorStatus (status, nRow) {
if (status == "Arrival at local delivery office" || status == "Arrival at Delivery Office") {
SpreadsheetApp.getActiveSheet().getRange(statusColumn + nRow).setFontColor("green").setFontWeight('bold');
}
else if (status == "Shipment cancelled") {
SpreadsheetApp.getActiveSheet().getRange(statusColumn + nRow).setFontColor("red").setFontWeight('bold');
}
else if (status == "Tracking error") {
SpreadsheetApp.getActiveSheet().getRange(statusColumn + nRow).setFontColor("red").setFontWeight('bold');
}
else if (status == "No tracking information is available now") {
SpreadsheetApp.getActiveSheet().getRange(statusColumn + nRow).setFontColor("orange").setFontWeight('bold');
}
else if (status.match(/Вручение/) != null) {
SpreadsheetApp.getActiveSheet().getRange(statusColumn + nRow).setFontColor("gray").setFontWeight('bold');
}
}
function tracking(trackCode) {
var result = {"lastDateTime":"-", "lastStatus":"-"};
if (trackCode != "") {
var url = 'https://global.cainiao.com/detail.htm?mailNoList=' + trackCode;
var response = UrlFetchApp.fetch(url);
var html = UrlFetchApp.fetch(url).getContentText();
var retrack = '';
var lastDateTime = '';
var lastStatus = '';
var statuses = html.match('id="waybill_list_val_box"[^<]+</textarea>');
statuses = statuses.toString();
if (statuses.match(/\d{4}-\d{2}-\d{2}/g) == null) {
lastStatus = 'Tracking error';
}
else if (statuses.match(/\d{4}-\d{2}-\d{2}/g).length < 2) {
lastStatus = 'No tracking information is available now';
}
else {
var lastStatusAndTime = statuses.match(/latestTrackingInfo"[^}]+[^&]+/);
lastStatusAndTime = lastStatusAndTime.toString();
// lastStatus = lastStatusAndTime.match(/;[А-яA-z0-9\s/.\(\)-\/]+",/g)[0].slice(1, -7);
lastStatus = lastStatusAndTime.match(/;[^&]+",/g)[0].slice(1, -7);
lastDateTime = lastStatusAndTime.match(/\d{4}-\d{2}-\d{2}/);
var s = new RegExp(trackCode + '[^&]');
var parceRetrack = statuses.match(s);
if (parceRetrack != null) {
retrack = statuses.match(/[:|(][A-z0-9]+\)/).toString().slice(1, -1);
}
}
}
else {
var ui = SpreadsheetApp.getUi();
var response = ui.alert("Error", "Не указан трек-код", ui.ButtonSet.OK);
}
result = {"retrack":retrack, "lastDateTime":lastDateTime, "lastStatus":lastStatus};
return result;
}
function trackingSelected() {
var selRowNumber = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
var selectedTrackCode = SpreadsheetApp.getCurrentCell().getValue();
if (selectedTrackCode != "") {
var trackingOutput = tracking(selectedTrackCode);
var lastDateTime = trackingOutput["lastDateTime"];
var lastStatus = trackingOutput["lastStatus"];
var retrack = trackingOutput["retrack"];
SpreadsheetApp.getActiveSheet().getRange(retrackColumn + selRowNumber).setValue(retrack);
SpreadsheetApp.getActiveSheet().getRange(dateTimeColumn + selRowNumber).setValue(lastDateTime);
SpreadsheetApp.getActiveSheet().getRange(statusColumn + selRowNumber).setValue(lastStatus);
colorStatus(lastStatus, selRowNumber);
}
else {
var ui = SpreadsheetApp.getUi();
var response = ui.alert("Error", "No track code", ui.ButtonSet.OK);
}
}
function trackingAllToTheFirstEmpty() {
var currentTrackCode = "";
SpreadsheetApp.getActiveSheet().getRange(trackingNumbersColumn + startRowForTrack).activate();
var rowNumber = startRowForTrack;
while (SpreadsheetApp.getCurrentCell().getValue() != "") {
SpreadsheetApp.getActiveSheet().getRange(trackingNumbersColumn + rowNumber).activate();
currentTrackCode = SpreadsheetApp.getCurrentCell().getValue();
if (currentTrackCode != "") {
var trackingOutput = tracking(currentTrackCode);
var lastDateTime = trackingOutput["lastDateTime"];
var lastStatus = trackingOutput["lastStatus"];
var retrack = trackingOutput["retrack"];
SpreadsheetApp.getActiveSheet().getRange(retrackColumn + rowNumber).setValue(retrack);
SpreadsheetApp.getActiveSheet().getRange(dateTimeColumn + rowNumber).setValue(lastDateTime);
SpreadsheetApp.getActiveSheet().getRange(statusColumn + rowNumber).setValue(lastStatus);
colorStatus(lastStatus, rowNumber);
}
else {
break;
}
rowNumber++;
}
}
function trackingAllCustom() {
// Set required values
var startRowForSelTrack = 2; // Set the row number with the first track code
var endRowForSelTrack = 100; // Set the row number with the last track code
var currentTrackCode = "";
var rowNumber = startRowForSelTrack;
SpreadsheetApp.getActiveSheet().getRange(trackingNumbersColumn + startRowForSelTrack).activate();
for (startRowForSelTrack; startRowForSelTrack <= endRowForSelTrack; startRowForSelTrack++) {
SpreadsheetApp.getActiveSheet().getRange(trackingNumbersColumn + rowNumber).activate();
currentTrackCode = SpreadsheetApp.getCurrentCell().getValue();
if (currentTrackCode != "") {
var trackingOutput = tracking(currentTrackCode);
var lastDateTime = trackingOutput["lastDateTime"];
var lastStatus = trackingOutput["lastStatus"];
var retrack = trackingOutput["retrack"];
SpreadsheetApp.getActiveSheet().getRange(retrackColumn + rowNumber).setValue(retrack);
SpreadsheetApp.getActiveSheet().getRange(dateTimeColumn + rowNumber).setValue(lastDateTime);
SpreadsheetApp.getActiveSheet().getRange(statusColumn + rowNumber).setValue(lastStatus);
colorStatus(lastStatus, rowNumber);
}
rowNumber++;
}
}
