Google Sheets - Shipment Dashboard

Apps Script

This sample application is intended to demonstrate how to use Google Sheets Apps Script to access the Shipwell API. The application mimics the behavior of the shipment dashboard found in the Shipwell web app and uses the auth/token/ and the /shipments/ routes to authenticate and collect information on shipments available to the authenticated user.

To begin, select Extensions > App Scripts from the menu bar.

After selection the user is taken to a new browser tab containing the JavaScript editor for creating the App Script.

AppsScript Editor

The template Google workbook is available here and the user must create a copy of it in order to make changes.

Functions

The code is referenced from the google sheet via functions defined in the code editor. Adding a menu option to exercise the Shipwell API calls is a convenient way to test the functions created.

Copy
Copied
function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Shipwell API')
      .addItem('POST /auth/token/', 'authToken')
      .addItem('GET /shipments/', 'getDashboard')
      .addToUi();
}

This will add a menu to the Google sheet when the sheet is opened. Shipwell API will be displayed on the menu bar and when selected will call either authToken() or getDashboard() functions.

Authentication

Login tab in the sample workbook

Authentication is performed with a POST to /auth/token/ with the body of the request containing the username/password being authenticated. For additional information on this route visit the online documentation here.

In this example we’re using UrlFetchApp provided by Google to make REST API calls and the username/password is collected from specific cells in the Login sheet contained in the workbook. The SpreadsheetApp class is used extensively to get data from a sheet and set data on a sheet and here it’s used to collect the email from row 8, column 3 and the password from row 9 column 3 of the active sheet when the function is called.

The Login sheet in the workbook provides prompts for the user to provide this information. On successful authentication the token returned is saved as a script parameter.

Note!

Make sure to press the return key after entering your password so the value is available when getValue is used to read the cell.

Copy
Copied
function authToken() {

  const url = "https://api.shipwell.com/v2/auth/token/";
  const data = {
    "email": SpreadsheetApp.getActiveSheet().getRange(8, 3).getValue(),
    "password": SpreadsheetApp.getActiveSheet().getRange(9, 3).getValue()
  };
  const config = {
    "method": "post",
    "payload": JSON.stringify(data),
    "headers": {
      "Content-Type": "application/json"
    },
    "muteHttpExceptions": false
  };
  const response = UrlFetchApp.fetch(url, config);

  const dataAll = JSON.parse(response.getContentText()); 
  SpreadsheetApp.getActiveSheet().getRange(9, 3).setValues([["..........."]]);
  PropertiesService.getScriptProperties().setProperty('myToken', 'Token ' + dataAll.token);
}

Things to note:

  • UrlFetchApp requires the body of the payload of the request to be a string, not JSON, so use stringify to convert it before making the request
  • The response is type text and needs to be parsed into a JSON object for easier querying
  • MuteHttpExceptions when set to false will allow error responses from the server to be displayed and helps with debugging
  • The token is saved as a ScriptProperties() and is available for use in future API calls Click the Login button to exercise the authToken() function

Simple Shipment Dashboard

The GET /shipments/ route is used to collect data for each shipment displayed on the dashboard and there are two main features included. First, constructing the url used prior to making the call. Second, parsing through the response and displaying the data.

Constructing the URL

Filtering via query parameters is performed to limit the amount of data returned. The user can select which filter they want to use and the value provided with the query parameter. This is done in the top portion of the sheet.

The query parameter is listed in the first row, the check box to use/not use in the second row, and the value used with the parameter in the third row. Here is the code to construct the url:

Copy
Copied
function constructURL(page, page_size) {
  let url = 'https://api.shipwell.com/v2/shipments/?page=' + page + '&page-size=' + page_size + '&ordering=-pickup';
  const queryParams =  SpreadsheetApp.getActive().getSheetByName("Dashboard").getRange(2, 3, 2, 6).getValues();
  // SpreadsheetApp.getUi().alert('queryParams[0][1] = ' + queryParams[0][1]);
  if (queryParams[0][0]) {
    let result = url.concat('&pickup__gte=' + String(queryParams[1][0]));
    url = result;
  }
  if (queryParams[0][1]) {
    let result = url.concat('&pickup__lte=' + String(queryParams[1][1]));
    url = result;
  }
    if (queryParams[0][2]) {
    let result = url.concat('&status=' + queryParams[1][2]);
    url = result;
  }
  if (queryParams[0][3]) {
    let result = url.concat('&equipment_type=' + queryParams[1][3]);
    url = result;
  }
  if (queryParams[0][4]) {
    let result = url.concat('&mode=' + queryParams[1][4]);
    url = result;
  }
   if (queryParams[0][5]) {
    let result = url.concat('&reference_id=' + queryParams[1][5]);
    url = result;
  }
  return url
}

The base url is defined first. Then getSheetByName("Dashboard").getRange(2, 3, 2, 6).getValues() is used to get the values in a range of row 2, column 3 through row 3, column 9. The results are put in a 2X6 nested array of the values of the check boxes and their associated parameter value provided by the user. Each pair is inspected in the conditional statements and if true then the query parameter and the value provided by the user is added to the url.

Get the Dashboard

The user can select what data is displayed on the dashboard in the left portion of the sheet.

In the same way we retrieved the values from the sheet and placed them in an array, the data will be included in the dashboard if the checkbox is selected. The dashboard contains a base level of information and is augmented with data selected by the user. However, instead of fixing the data to a column and having the column blank if the data isn't selected, this dashboard will dynamically expand and contract based on what the user selects. So the column headers are constructed prior to populating the dashboard in a very similar way the url was built up based on the user's selection. Here is the code for that:

Copy
Copied
function buildDashboardHeader () {
  const dashboardHeader = [['ID',	'Status', 'Customer',	'Pickup',	'Pickup Date', 'Delivery',	'Delivery Date',	'Mode',	'Equipment',	'Weight',	'Created']];

  SpreadsheetApp.getActiveSheet().getRange(5, 3, 1, 11).setValues(dashboardHeader);

  let collectData =  SpreadsheetApp.getActive().getSheetByName("Dashboard").getRange(6, 1, 8, 2).getValues(); 
  let dashboardBase = 11;
  let offset = 3;

  if (collectData[0][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["BOL"]]);
    SpreadsheetApp.getActiveSheet().getRange(5,dashboardBase + offset,SpreadsheetApp.getActiveSheet().getLastRow()-1).setNumberFormat("@");
    dashboardBase++;
  }
  if (collectData[1][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["PRO #"]]);

    dashboardBase++;
  }
  if (collectData[2][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["Link"]]);
    dashboardBase++;
  }
  if (collectData[3][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["Cust Ref #"]]);
    SpreadsheetApp.getActiveSheet().getRange(5,dashboardBase + offset,SpreadsheetApp.getActiveSheet().getLastRow()-1).setNumberFormat("@");
    dashboardBase++;
  }
  if (collectData[4][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["Pickup #"]]);
    SpreadsheetApp.getActiveSheet().getRange(5,dashboardBase + offset,SpreadsheetApp.getActiveSheet().getLastRow()-1).setNumberFormat("@");
    dashboardBase++;
  }
  if (collectData[5][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["Customer $"]]);
    SpreadsheetApp.getActiveSheet().getRange(5,dashboardBase + offset,SpreadsheetApp.getActiveSheet().getLastRow()-1).setNumberFormat("$#,##0.00;$(#,##0.00)");
    dashboardBase++;
  }
  if (collectData[6][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["Carrier $"]]);
    SpreadsheetApp.getActiveSheet().getRange(5,dashboardBase + offset,SpreadsheetApp.getActiveSheet().getLastRow()-1).setNumberFormat("$#,##0.00;$(#,##0.00)");
    dashboardBase++;
  }
    if (collectData[7][1]) {
    SpreadsheetApp.getActiveSheet().getRange(5, dashboardBase + offset).setValues([["PO #"]]);
    dashboardBase++;
  }
}

Pagination

The GET /shipments/ response includes total_count, total_pages and page_size in the response to limit what is returned while allowing navigation across the entire result set. This results in needing to GET multiple pages of data using page and page_size in the request. A request with a page_size of 10 resulting in 99 shipments being selected will need to be called 10 time with a different page index after each call and the last call with a page index of 10 will only have 9 shipments included in the response.

Here’s the code to GET a page of shipments, increment the page index, and continue to collect shipment data until all shipments are collected.

Copy
Copied
function getDashboard() {
  let page = 1;
  const page_size = 10;
  let total_pages = 2;

  let range = SpreadsheetApp.getActive().getSheetByName("Dashboard").getRange(5,3,1000,20);
  range.clearContent();

  buildDashboardHeader();

  while (page <= total_pages) {
    total_pages = getDashboardPage(page, page_size);
    page++;
  }
}

Things to note:

  • total_pages is initialized to a value of 2 to force entering the while loop, it is then set after getting a page of shipments and inspected in the conditional statement
  • range.clearContent() is used to clear the entire dashboard, including the headers before collecting data to be displayed.
  • The dashboard headers are determined and the cells populated with their values
  • total_pages is returned from getDashboardPage which makes the GET /shipments/ call

Collecting the shipment data

This is the ‘meat’ of this example! The url is constructed and the /shipment/ route is called. Then, based on the length of the results (number of shipments), data from each shipment object is collected and displayed one row at a time before iterating to the next shipment object. In the case of carrier and customer amounts, multiple line items could be included and will need to be iterated over to calculate to total amount resulting in nested while loops.

Copy
Copied
function getDashboardPage (page, page_size){

  const url = constructURL(page, page_size)

  const token = SpreadsheetApp.getActive().getSheetByName("Login").getRange(10, 3, 1, 1).getValues();

  const config = {
    "method": "get",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": String(PropertiesService.getScriptProperties().getProperty('myToken'))
    },
    "muteHttpExceptions": true
  };
  
  const response = UrlFetchApp.fetch(url, config);
  const dataAll = JSON.parse(response.getContentText()); 

  const collectData =  SpreadsheetApp.getActive().getSheetByName("Dashboard").getRange(6, 1, 8, 2).getValues(); 
  let i = 0;  
  while (i < dataAll.results.length) {
    let dashboardRow = [[
      dataAll.results[i].reference_id, 
      dataAll.results[i].state,
      dataAll.results[i].customer.name,
      dataAll.results[i].stops[0].location.address.city + ', ' + dataAll.results[i].stops[0].location.address.state_province,
      dataAll.results[i].stops[0].planned_date,
      dataAll.results[i].stops[1].location.address.city + ', ' + dataAll.results[i].stops[1].location.address.state_province,
      dataAll.results[i].stops[1].planned_date,
      dataAll.results[i].mode?.code,
      dataAll.results[i].equipment_type?.name,
      dataAll.results[i].line_items[0]?.total_line_item_weight,
      dataAll.results[i].created_at.substr(0, 10)
      ]];
    if (collectData[0][1]) {
      dashboardRow[0].push(dataAll.results[i].bol_number);
    }
    if (collectData[1][1]) {
      dashboardRow[0].push(dataAll.results[i].pro_number);
    }
    if (collectData[2][1]) {
      dashboardRow[0].push('https://app.shipwell.com/shipments/' + dataAll.results[i].id);
    }
    if (collectData[3][1]) {
      dashboardRow[0].push(dataAll.results[i].customer_reference_number);
    }
    if (collectData[4][1]) {
      dashboardRow[0].push(dataAll.results[i].pickup_number);
    }
    if (collectData[5][1]) {
      let j=0;
      let customer_amount = 0.0;
      while (j < dataAll.results[i].relationship_to_customer?.vendor_charge_line_items.length) {
        customer_amount += parseFloat(dataAll.results[i].relationship_to_customer?.vendor_charge_line_items[j].amount);
        j++;
      }
      dashboardRow[0].push(customer_amount);

    }
    if (collectData[6][1]) {
      let j=0;
      let carrier_amount = 0.0;
      while (j < dataAll.results[i].relationship_to_vendor?.customer_charge_line_items.length) {
        carrier_amount += parseFloat(dataAll.results[i].relationship_to_vendor?.customer_charge_line_items[j].amount);
        j++;
      }
      dashboardRow[0].push(carrier_amount);
    }
    if (collectData[7][1]) {
      dashboardRow[0].push(dataAll.results[i]?.purchase_order_number);
    }
    var row = (i + 6) + (page * page_size - page_size);

    SpreadsheetApp.getActive().getSheetByName("Dashboard").getRange(row, 3, 1, dashboardRow[0].length).setValues(dashboardRow);
    i++;
  }

  return dataAll.total_pages;
}

Final Thoughts

This example is intended to provide insights into using the Shipwell API. Shortcuts were taken like hard coding values and no data validation, the data collected in the app should NOT be considered a source of truth for shipment details.

Try adding additional query parameters and additional data to augment the dashboard results! The GET /shipments/ doc can be found here.

Copyright © Shipwell 2024. All right reserved.