Google Drive Sheet to CSV export for UE4

UE4Tools.gs

/**
 * @OnlyCurrentDoc
 */

kvString = "=KV(";
kvaString = "=KVA(";

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Save as CSV file', functionName: 'exportToCSV'}
  ];
  spreadsheet.addMenu('UE4 Tools', menuItems);
}

/**
 * References a single cell containing a key
 *
 * @param The cell containing the key
 * @return The cell adjacent to the key (to the right)
 * @customfunction
 */
function KV(arg) {
  return arg;
}

/**
 * References 1 or more comma separated cells and/or ranges as an array of keys.
 * @param The cells or ranges containing the key(s)
 * @return The corresponding values of the cells or ranges
 * @customfunction
 */
function KVA(){
  if(arguments.length == 1){
    if(Array.isArray(arguments[0])) {
      var output = buildArrayOutput_(arguments[0]);
      return output; 
    }
  }
  
  // For comma separated vararg
  var output = buildArrayOutput_(arguments);
  return output;
}

function buildArrayOutput_(inputArray){
  var output = "(";
  for(var i = 0; i < inputArray.length; i++) {
    if(i > 0){
      output += ",";
    }
    output += inputArray[i];
  }
  output += ")";
  
  return output;
}

function getSwapValue_(formula){
  if(formula){
    if(formula.toUpperCase().indexOf(kvString) != -1){
      return getSwapSingle_("=KV" + formula.substr(3, formula.length));
    }
    else if(formula.toUpperCase().indexOf(kvaString) != -1){
      return getSwapArray_("=KVA" + formula.substr(4, formula.length)); 
    }
  }
  
  return null;
}

function getSwapSingle_(formula){
  if(formula){

    // The formula should look like:
    // "=kvA(SheetName!A5)"
    // validate this first
    var startIndex = formula.indexOf(kvString.toUpperCase());
    if(startIndex == -1) {
      return formula;
    }
    
    if(formula.indexOf("!") == -1){
      return formula;
    }
    
    // strip off everything except for the function argument
    // 1. remove the function name and opening paren
    var argument = formula.replace(kvString, '');
    
    // 2. Remove the trailing )
    argument = argument.slice(0, argument.lastIndexOf(')')); 
        
    // [SheetName, A1Notation]
    var notationArray = argument.split('!');
    
    var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(notationArray[0]);
    try {
      // Return the pointed to value + 1
      return ws.getRange(notationArray[1]).offset(0,1).getValue();
    }
    catch(err) {
     Logger.log(err);
     Browser.msgBox(err);
    }
  }
 
  return formula;
}

function getSwapArray_(formula){
  if(formula){
    // the formula will match one of these forms
    // "=kvA(SheetName!A5)"
    // "=kvA(SheetName!D2,BlueprintIndex!D3,BlueprintIndex!D4)"
    // "=kvA(BlueprintIndex!D2:D5)"
    var startIndex = formula.toUpperCase().indexOf(kvaString);
    if(startIndex == -1) {
      return formula;
    }
    
    if(formula.indexOf("!") == -1){
     return formula; 
    }
        
    // strip off everything except for the function argument
    // 1. remove the function name and opening paren
    var argument = formula.replace(kvaString, '');
    
    // 2. Remove the trailing )
    argument = argument.slice(0, argument.lastIndexOf(')')); 
    
    // "SheetName!A5"
    // "SheetName!D2,SheetName!D3,SheetName!D4"
    // "SheetName!D2:D5"
    // "SheetName!D2:D5, SheetName!D8"
    
    // First split, this will give us individual sheetname!range pairs, this covers
    // both ranges like "D2:D5" and individual cells like D8
    var notationArray = argument.split(',');
    var outputString = "(";
    
    for(var iArrayEntry = 0; iArrayEntry < notationArray.length; iArrayEntry++){
      if(iArrayEntry > 0){
       outputString += ","; 
      }
      // array entries look like this:
      // "SheetName!D2"
      // "SheetName!D5:D8
      
      // split the notationArray at "!"
      var sheetAndData = notationArray[iArrayEntry].split('!');
      
      if(sheetAndData.length != 2){
        Browser.msgBox("Invalid array data: " + sheetAndData);
        return formula;
      }
      
      var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetAndData[0]);
      try {
        // Finally there are only 2 cases
        // 1. A single row
        if(sheetAndData[1].indexOf(':') == -1){
          outputString += ws.getRange(sheetAndData[1]).offset(0,1).getValue();
        }
        // 2. A range of rows
        else{
         var dataArray =  ws.getRange(sheetAndData[1]).offset(0,1).getValues();
          for(var iData = 0; iData < dataArray.length; iData++){
            if(iData > 0){
              outputString += ",";
            }
            outputString += dataArray[iData]; 
          }
        }
      }
      catch(err) {
        Logger.log(err);
        Browser.msgBox(err);
      }
    }
    
    outputString += ")";
    return outputString;
  }
  return formula;
}

function convertDocumentToCSV_(csvFileName) {
  var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  try {
    var data = ws.getDataRange().getValues();
    var csvFile = undefined;

    // Loop through the spreadsheet and build data
    if (data.length > 1) {
      var csv = "";
      var swappedValue = "";
      
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          swappedValue = getSwapValue_(ws.getRange(row + 1, col + 1, 1).getFormula());
          
          if(swappedValue) {
           data[row][col] = swappedValue; 
          }
          
          // If there is a comma
          var bIsComma = data[row][col].toString().indexOf(",") != -1;
          var bIsArray = data[row][col].toString().indexOf("(") != -1;
          if (bIsComma || bIsArray) {
            // Insert the data surrounded by quotes
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // Join each row's columns
        // Add a carriage return to end of each row, except for the last one
        if (row < data.length - 1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

function exportToCSV() {
  var fileName = SpreadsheetApp.getActiveSpreadsheet().getName() + "_-_" + SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  fileName = fileName.replace(' ', '_');
  
  if(fileName.length !== 0) {
   fileName = fileName + ".csv"; 
   var csvFile = convertDocumentToCSV_(fileName);
    DriveApp.createFile(fileName, csvFile);
  }
}