[FEATURE] Import from Google Sheets

Is your feature request related to a problem? Please describe.

To make it easier for non-technical pilots to collaborate on checklists (e.g., co-owners of one airplane), it would be nice if EFIS Editor had the ability to import/export in CSV format. This would allow non-technical users to edit and refine their spreadsheets using Excel or Google Sheets and then (re)import them into EFIS editor.

Describe the solution you'd like

Upload and dowload support for CSV files.

I have a simple format in mind, essentially it's a user-friendly, de-normalized version of the existing JSON format.

Here's an example of a Google Sheet that would work with this format.

Describe alternatives you've considered

Sharing checklist files between users, having to import/export each time using whatever klunky device interface, which is very tedious.

Additional context

I realize this idea is somewhat hacky, but I think normal people would be able to use this without too much trouble especially when starting from an existing, working spreadsheet.

File format:

• Meta-data appears in Name: Value cell pairs at the top (see example Google Sheet)
• The first row looking like "Group","Checklist","Type","Text","Response","Indent","Center" marks the start of the table
• Subsequent rows structurally mirror a de-normalized version of the JSON format

I tried creating an Add-on to Google Sheets that would allow you to export directly to JSON instead of CSV. I didn't finish it due to issues with Oauth and Advanced Protection, but this shows the basic logic (untested):

/** @OnlyCurrentDoc */
function exportSheetsToJSON() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let rows = sheet.getDataRange().getValues();
  
  // Table expected column names
  let cols = [
    "Group",
    "Checklist",
    "Type",
    "Text",
    "Response",
    "Indent",
    "Center"
  ];

  // Initialize metadata
  let metadata = {
    "name":   "My Checklists"
  };

  // Find the table
  let tableRow = -1;
  for (let i = 0; i < rows.length; i++) {
    let row = rows[i];

    // Is this the start of the table?
    if (row.length >= cols.length && cols.every((val, index) => val === row[index])) {
      tableRow = i;
      break;
    }

    // Gather metadata at the top of the sheet
    let defaultGroup = false;
    let defaultChecklist = false;
    for (let j = 0; j < 10; j += 2) {
      let name = row[j];
      let value = row[j + 1];
      if (!value)
        continue;
      let property = false;
      if (name === "Name:")
        property = "name";
      else if (rname == "Aircraft:")
        property = "aircraftInfo";
      else if (name == "Make & Model:")
        property = "makeAndModel";
      else if (name == "Copyright Info:")
        property = "aircraftInfo";
      else if (name == "Manufacturer Info:")
        property = "manufacturerInfo";
      else if (name == "Default Group:")
        defaultGroup = value;
      else if (name == "Default Checklist:")
        defaultChecklist = value;
      if (property)
        metadata.property = value;
    }
  }
  if (tableRow === -1)
    throw "Error: Did not find table header row " + JSON.stringify(cols);
  
  // Initialize JSON
  let json = {
    metadata: metadata,
    groups:   []
  };
  
  // Scan table rows
  let group = false;
  let checklist = false;
  for (let i = 1; i < rows.length; i++) {
    let row = rows[i];
    let groupTitle = row[0];
    let checklistTitle = row[1];
    let type = row[2];
    let text = row[3];
    let expect = row[4];
    let indent = row[5];
    let center = row[6];

    // Validate
    if (typeof groupTitle !== 'string' || groupTitle.length == 0)
      throw "Error: row " + i + ": invalid/missing \"Group\" column value";
    if (typeof checklistTitle !== 'string' || checklistTitle.length == 0)
      throw "Error: row " + i + ": invalid/missing \"Checklist\" column value";

    // Start new group if needed
    if (!group || groupTitle !== group.title) {
      group = {
        title:      groupTitle,
        checklists: []
      };
      json.groups.push(group);
      checklist = false;
    }

    // Start new checklist if needed
    if (!checklist || checklistTitle != checklist.title) {
      checklist = {
        title:    checklistTitle,
        items:    []
      };
      groups.checklists.push(checklist);
    }
    
    // Build item
    let item = {};
    if (type === "Title Bar") {
      item.type = "ITEM_TITLE";
      item.prompt = text;
    } else if (type === "Challenge") {
      item.type = expect ? "ITEM_CHALLENGE_RESPONSE" : "ITEM_CHALLENGE";
      item.prompt = text;
      if (expect)
        item.expectation = expect;
    } else if (type === "Information") {
      item.type = "ITEM_PLAINTEXT";
      item.prompt = text;
    } else if (type === "Warning") {
      item.type = "ITEM_WARNING";
      item.prompt = text;
    } else if (type === "Caution") {
      item.type = "ITEM_CAUTION";
      item.prompt = text;
    } else if (type === "Note") {
      item.type = "ITEM_NOTE";
      item.prompt = text;
    } else if (type === "Space")
      item.type = "ITEM_SPACE";
    else
      throw "Error: row " + i + ": unknown type \"" + type + "\"";

    // More validation
    if (typeof(item.prompt) !== 'undefined'
        && (typeof(item.prompt) !== 'string' || item.prompt.length === 0))
      throw "Error: row " + i + ": invalid/missing \"Text\" column value";
    if (typeof(item.expectation) !== 'undefined'
        && (typeof(item.expectation) !== 'string' || item.expectation.length === 0))
      throw "Error: row " + i + ": invalid/missing \"Response\" column value";
    if (indent) {
      if (typeof(indent) !== 'number' || indent < 0 || indent !== Math.round(indent))
        throw "Error: row " + i + ": invalid indent value \"" + indent + "\"";
      item.indent = indent;
    }
    if (centered)
      item.centered = true;
    
    // Add item
    checklist.push(item);
  }

  // Resolve default checklist indicies
  if (defaultGroup && defaultChecklist) {
    let found = false;
    for (let i = 0; !found && i < json.groups.length; i++) {
      let group = json.groups[i];
      for (let j = 0; !found && j < group.checklists.length; j++) {
        let checklist = group.checklists[j];
        if (group.title === defaultGroup && checklist.title === defaultChecklist) {
            json.metadata.defaultGroupIndex = i;
            json.metadata.defaultChecklistIndex = j;
            found = true;
          }
      }
    }
    if (!found)
      throw "Error: Default checklist \"" + defaultChecklist + "\" in group \"" + defaultGroup + "\" not found";
  }

  // Return JSON
  let content = ContentService.createTextOutput(JSON.stringify(json)).setMimeType(ContentService.MimeType.JSON);
  SpreadsheetApp.getUi().showModalDialog(content, 'Exported JSON Data');
}

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('EFIS Export')
    .addItem('Export as JSON', 'exportSheetsToJSON')
    .addToUi();
}