1 year ago
#389228
Kenneth Cook
How to skip to next row of iteration without ending the function if a certain cell is NOT BLANK in google apps script
I need my code to check cells within two different columns and skip to the next row if certain conditions (listed below) are met. I am not sure why I can't figure this out. I'm sure it's simple, but my attempt to do this is causing the entire execution to terminate if a cell within column 22 (V) is NOT BLANK.
What I need is it to say:
Check Row 2 of Column 22; if it IS NOT BLANK, move onto the the next row without fetching the URL. If current row of Column 22 IS BLANK, then Check Column 11 (K), and if it IS NOT BLANK, then fetch URL. But, if it IS BLANK move on to next row without fetching URL.
I hope this makes sense what I am asking...
function updateDeliveryDateColumn() {
var spreadsheetId = 'ENTER_ID_HERE';
var ss = SpreadsheetApp.openById(spreadsheetId);
var activeSheet = ss.getSheetByName('ENTER_SHEET_NAME_HERE');
var settingsSheet = ss.getSheetByName('ENTER_SHEET_NAME_HERE');
var range = activeSheet.getRange('V2:V');
var createUrl = 'https://api.easypost.com/v2/trackers';
var getUrl = 'https://api.easypost.com/v2/trackers/';
// API key is stored in [1,2].
var headers = {
"Authorization": "Basic " + Utilities.base64Encode(settingsSheet.getRange(1,2).getValue().toString())
}
for (var i = 2; i <= activeSheet.getLastRow(); i ++ ){
if (activeSheet.getRange(i, 11).getValue() == "") {
continue;
}
if (activeSheet.getRange(i, 22) != "") {
continue;
}
Logger.log("Query tracking number " + activeSheet.getRange(i, 11).getValue());
var carrier = activeSheet.getRange(i, 21).getValue().toString();
if (carrier == '') {
carrier = guessCarrier(activeSheet.getRange(i, 11).getValue().toString());
activeSheet.getRange(i, 21).setValue(carrier);
if (carrier == 'UNKOWN') {
continue;
}
} else {
switch (carrier.toLowerCase()) {
case 'ups':
activeSheet.getRange(i, 21).setValue('UPS');
break;
case 'fedex':
activeSheet.getRange(i, 21).setValue('FedEx');
break;
case 'usps':
activeSheet.getRange(i, 21).setValue('USPS');
break;
default:
break;
}
}
var data = {
'carrier': activeSheet.getRange(i, 21).getValue(),
'tracking_code': activeSheet.getRange(i, 11).getValue(),
};
var options = {
'method' : 'post',
'contentType': 'application/json',
'headers': headers,
'muteHttpExceptions': true,
'payload' : JSON.stringify(data)
};
var response = UrlFetchApp.fetch(createUrl, options);
if (response.getResponseCode() != 200) {
Logger.log("Will retry get tracker:" + response);
var getOptions = {
'method' : 'get',
'headers': headers,
'muteHttpExceptions': true,
};
response = UrlFetchApp.fetch(getUrl, getOptions);
}
if (response.getResponseCode() != 200) {
Logger.log(response);
continue;
}
var parsed = JSON.parse(response.getContentText());
activeSheet.getRange(i, 26).setValue(parsed.status);
if (parsed.status == 'delivered'){
var deliveredEvents = parsed.tracking_details.filter(function (el) {
return el.status == 'delivered';
});
if (deliveredEvents.length == 1) {
activeSheet.getRange(i, 22).setValue(new Date(deliveredEvents[0].datetime));
}
}
}
}
javascript
loops
for-loop
google-apps-script
0 Answers
Your Answer