基于UrlFetchApp的3PL物流API数据解析与特定字段提取需求

我编写了一段调用3PL物流网站API获取报价数据的脚本，目前需要从返回结果里筛选出DestinationServiceCenter、OriginServiceCenter、QuoteNumber、ServiceDays、NetCharge这些特定数据。

原脚本 #

function rateCarrier() {
  
  var source_sheet = SpreadsheetApp.getActiveSheet();

  var shipmentdate = source_sheet.getRange("A3").getValue();

  var originzip = source_sheet.getRange("B3").getValue();
  var origincity = source_sheet.getRange("C3").getValue();
  var originstate = source_sheet.getRange("D3").getValue();

  var destinationzip = source_sheet.getRange("B6").getValue();
  var destinationcity = source_sheet.getRange("C6").getValue();
  var destinationstate = source_sheet.getRange("D6").getValue();

  var skidcount = source_sheet.getRange("B9").getValue();
  var weight = source_sheet.getRange("C9").getValue();
  var freightclass = source_sheet.getRange("D9").getValue();

  var json = 
  '{' +
'  "RateQuote": {' +
'    "Origin": {' +
'      "City": "'+origincity+'",' +
'      "StateOrProvince": "'+originstate+'",' +
'      "ZipOrPostalCode": "'+originzip+'",' +
'      "CountryCode": "USA"' +
'    },' +
'    "Destination": {' +
'      "City": "'+destinationcity+'",' +
'      "StateOrProvince": "'+destinationstate+'",' +
'      "ZipOrPostalCode": "'+destinationzip+'",' +
'      "CountryCode": "USA"' +
'    },' +
' "Items":[' +
'     {' +
'         "Weight": "'+weight+'",' +
'         "Class": "'+freightclass+'"' +
'     }' +
' ],   ' +
'    "PickupDate": "'+shipmentdate+'"' +
'  },' +
'}';

  var headers = {
    "apiKey": "deleted",
    "Content-Type": "application/json",
    "Authorization": "Basic _authcode_"
  };

var options = {
  "method": "POST",
  "contentType": "application/json",
  "headers": headers,
  "payload": json
};

  var url = "https://api.rlc.com/RateQuote";
  var response = UrlFetchApp.fetch(url, options);

    if (200 == response.getResponseCode())
    {
        Logger.log('HTTP request succeeded');
    }
    else
    {
        Logger.log('HTTP request failed');
    }

    var data = JSON.parse(response.getContentText());
    Logger.log(data);
}

API返回结果 #

{Messages=[], Code=200.0, RateQuote={Charges=[{Amount=$3,956.00, Weight=5000, Rate=$79.12, Title=Class: 60 / Rated 50, Type=}, {Amount=$3,956.00, Rate=, Title=Gross Charge, Type=GROSS, Weight=5000}, {Amount=$3,698.86, Type=DISCNT, Rate=93.5%, Title=R+L Discount Saves This Much, Weight=}, {Rate=, Amount=$257.14, Type=DISCNF, Title=Discounted Freight Charge, Weight=}, {Rate=47.3%, Type=FUEL, Title=Fuel Surcharge, Amount=$121.63, Weight=}, {Title=Net Charge, Type=NET, Weight=, Amount=$378.77, Rate=}], Ocean=null, PickupDate=11/10/2022, Destination={CountryCode=USA, City=DYERSVILLE, StateOrProvince=IA, ZipOrPostalCode=52040}, DestinationServiceCenter={Phone=1-800-318-2452, Code=DVP, Location=Davenport, IA, City=Davenport, ZipCode=52806-1010, State=IA, Address1=2940 W. 73rd Street, Address2=}, CustomerDiscounts=$3,698.86, Origin={StateOrProvince=OH, CountryCode=USA, ZipOrPostalCode=44149, City=STRONGSVILLE}, CategorizedMessages={RatesMessages=[FAK 50 APPLIES TO CLASS 50 TO 70], GeneralMessages=[* Not to exceed 20,000lbs or 20'. Shipments over 10,000lbs or over 10' may be delayed for pickup or experience service delays due to current capacity limitations.   Please contact your Service Center for more information by calling 800.543.5589., Note: Some residential deliveries may require additional days of service., * This quote is based on information you provide. The actual charges shall be determined by actual shipment characteristics, and any accessorial charges that are applicable to the shipment at time of shipment., For classes higher than 300, contact the Rate department at 800-535-1983., Please note that a sufferance and in-bond storage charges accumulated in the customs process are the responsibility of the debtor of the freight charges., This rate is not applicable to tradeshows. Please contact our Rate Specialist at 800-535-1983 for more information.], TransitMessages=[]}, OriginServiceCenter={Code=NOR, State=OH, ZipCode=44857-9519, Location=Norwalk, OH, Phone=1-800-634-7259, City=Norwalk, Address2=, Address1=1403 St. Rt. 18 E}, ServiceLevels=[{Charge=$3,956.00, QuoteNumber=5555555, ServiceDays=2.0, Name=Standard Service, Code=STD, NetCharge=$378.77, HourlyWindow=null}, {HourlyWindow=null, NetCharge=$430.19, Charge=$51.42, ServiceDays=2.0, Code=GSDS, QuoteNumber=28477953, Name=Guaranteed Service}]}, Errors=[]}

筛选指定数据的修改方案 #

在脚本中解析完API响应后，添加以下代码来提取所需字段：

var data = JSON.parse(response.getContentText());

// 提取RateQuote核心数据
var rateQuote = data.RateQuote;

// 获取始发与目的服务中心信息
var originServiceCenter = rateQuote.OriginServiceCenter;
var destinationServiceCenter = rateQuote.DestinationServiceCenter;

// 遍历服务等级，提取报价编号、服务天数、净费用
var serviceLevelDetails = rateQuote.ServiceLevels.map(level => ({
  QuoteNumber: level.QuoteNumber,
  ServiceDays: level.ServiceDays,
  NetCharge: level.NetCharge
}));

// 输出筛选后的数据
Logger.log("始发服务中心:", originServiceCenter);
Logger.log("目的服务中心:", destinationServiceCenter);
Logger.log("报价详情:", serviceLevelDetails);

说明 #

• OriginServiceCenter和DestinationServiceCenter是RateQuote下的直接对象，可直接提取；
• ServiceLevels是数组，每个元素对应一种服务类型，用map方法遍历后提取每个元素里的QuoteNumber、ServiceDays、NetCharge三个字段；
• 你可以根据需求将这些数据写入谷歌表格，或者做进一步的格式化处理。

内容的提问来源于stack exchange，提问作者user1100756