Import AR.Drone Academy Flight Data to Google Sheets

ARDroneSheetsImport.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AR.Drone Academy")
      .addItem("Import New Flight", "flightNumPrompt")
        .addToUi();
}

function flightNumPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
    "New Flight",
    "Please enter your flight number:",
    ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK && text != "") {
    getFlightData(text);
  }
}

function getFlightData(flightNumber) {
  if (flightNumber == null) {
    flightNumber = "3543111"; // Testing data.
  }
  
  var headerUrl = "http://academy.ardrone.com/api/logs/" + flightNumber;
  var headerDataSet = JSON.parse(UrlFetchApp.fetch(headerUrl).getContentText());
  
  if (headerDataSet == null || headerDataSet.length < 1) {
    Browser.msgBox("Error retrieving the flight. Make sure it is marked as public and try again.");
    return;
  }
  
  // At this point, we /should/ have a valid flight.
  
  // Create a new sheet for this flight.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(flightNumber);
  if (sheet != null) {
    Browser.msgBox("A sheet with this name already exists.");
    return;
  }
  sheet = ss.insertSheet(flightNumber); 
    
  var header = [
      ["Flight Number",  "Pilot",                     "Date/Time",            "Total Flight Time",             "Crashes"],
      [headerDataSet.id, headerDataSet.user.username, headerDataSet.datetime, headerDataSet.total_flight_time, headerDataSet.crash],
      ["", "", "", "", ""], // Make a spacer row.
      ["Time Elapsed (s)", "Battery Level (%)", "Altitude (m)", "Speed (m/s)", ""] // Pad to 5 columns.
    ];
  
  dataRange = sheet.getRange(1, 1, header.length, header[0].length);
  dataRange.setValues(header);
  sheet.setFrozenRows(header.length);
  
  // Bold the detail header.
  sheet.getRange(header.length, 1, 1, header[0].length).setFontWeight("bold");
  
  // Get the details.
  var detailUrl = headerUrl + "/details";
  var detailDataSet = JSON.parse(UrlFetchApp.fetch(detailUrl).getContentText());
  
  var rows = [], data, baseTime;
  
  // Make sure there are details, and get the base timestamp.
  if (detailDataSet.length > 0) {
    baseTime = detailDataSet[0].time;
  } else {
    Browser.msgBox("Record error");
    return;
  }
  
  // Create a row for each detail.
  for (i = 0; i < detailDataSet.length; i++) {
    data = detailDataSet[i];
    rows.push([
      data.time - baseTime, // Get elapsed time.
      data.battery_level, 
      data.altitude / 1000, // Convert to meters.
      data.speed / 1000 // Convert to meters per second
    ]); 
  }

  // Populate the sheet.
  dataRange = sheet.getRange(header.length + 1, 1, rows.length, 4); // 4 Denotes total number of fields in a row.
  dataRange.setValues(rows);
}