Untitled

function getRate() {
  var url = 'https://api.goshippo.com/shipments/';

  var headers = {
    "Authorization": "ShippoToken xxxxx",
    "Content-Type": "application/json",
  };

  var addresses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Canada").getRange("D2:F851").getValues();
  var addressObj = [];
  for (address of addresses) {
    addressObj.push({
      "city" : address[0],
      "state" : address[1],
      "zipcode" : String(address[2])
    })
  }

  for (i in addressObj) {
    console.log(parseInt(i) + 2);
    console.log(addressObj[i]);
    var payload = {
      "address_from": {
        "name": "Test KD",
        "street1": "543 Highway 1",
        "city": "St. Francois Xavier",
        "state": "MB",
        "zip": "R4L1A1",
        "country": "CA",
        "phone": "+15109285754",
        "email": "developer@karteldaun.com"
      },
      "address_to": {
        "name": "Mrs. Hippo",
        "street1": "Test St.",
        "city": addressObj[i].city,
        "state": addressObj[i].state,
        "zip": addressObj[i].zipcode,
        "country": "CA"
      },
      "parcels": [{
        "length": "21",
        "width": "12",
        "height": "5",
        "distance_unit": "cm",
        "weight": "1",
        "mass_unit": "kg"
      }],
      "async": false
    };

    var options = {
      'method': 'post',
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    var response = UrlFetchApp.fetch(url, options);
    var jsonResponse = JSON.parse(response.getContentText());
    // console.log(jsonResponse);

    if(jsonResponse) {
      for (rate of jsonResponse.rates) {
        if (rate.servicelevel.name == "Priority") {
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Canada").getRange(parseInt(i) + 2, 7).setValue(rate.amount);
          SpreadsheetApp.flush();
          break;
        }
        if (rate.servicelevel.name == "Xpresspost") {
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Canada").getRange(parseInt(i) + 2, 7).setValue(rate.amount);
          SpreadsheetApp.flush();
          break;
        }
      }
      // Logger.log(jsonResponse.rates);
    } else {
      Logger.log("No object_id found in the response");
    }
  }
}