function onOpen() { //onOpen get's called when you open the Sheet
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Sansho Scraper')
        .addItem('Menu 1', 'menuItem1')
        .addSeparator()
        .addItem('Menu 2', 'menuItem2')
        .addToUi();
}

function menuItem1() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert('You clicked the first menu item!');
}

function menuItem2() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert('You clicked the second menu item!');
}

/* 
CRAWLER THAT GENERATES 1 SHEET PER CSV
1. Chose CSV files and append them to the document
2. Empty the whole sheet
3. Merge all sheets to a first sheet called "Merge"
*/


// ============ GENERATES THE MENU ============ //

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Sansho Scraper')
        .addItem('Import CSV files', 'showPicker')
        .addSeparator()
        .addItem('Menu 2', 'menuItem2')
        .addToUi();
}

function menuItem1() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert('You clicked the first menu item!');
}

function menuItem2() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert('You clicked the second menu item!');
}

// ============ PICKER FUNCTION ============ //

/**
 * Displays an HTML-service dialog in Google Sheets that contains client-side
 * JavaScript code for the Google Picker API.
 */
function showPicker() {
    var html = HtmlService.createHtmlOutputFromFile('Picker.html')
        .setWidth(600)
        .setHeight(425)
        .setSandboxMode(HtmlService.SandboxMode.IFRAME);
    SpreadsheetApp.getUi().showModalDialog(html, 'Select a file');
}

/**
 * Gets the user's OAuth 2.0 access token so that it can be passed to Picker.
 * This technique keeps Picker from needing to show its own authorization
 * dialog, but is only possible if the OAuth scope that Picker needs is
 * available in Apps Script. In this case, the function includes an unused call
 * to a DriveApp method to ensure that Apps Script requests access to all files
 * in the user's Drive.
 *
 * @return {string} The user's OAuth 2.0 access token.
 */
function getOAuthToken() {
    DriveApp.getRootFolder();
    return ScriptApp.getOAuthToken();
}


// ============ PICKER CALLBACK FUNCTION + COLLECT ALL CSV TO ONE SHEET EACH ============ //

function customPickerCallback(Ids, escapeCharacter) {
  Logger.log('these are the ids');
    Logger.log(Ids);
    collectAllFiles(Ids, escapeCharacter);
}


function collectAllFiles(Ids, escapeCharacter) {
    // var id = ["1d9ZH5z7UocoyMyjmZoqmPpVjIQ90v4Re"];

    for (var k = 0; k < Ids.length; k++) {
        var thisId = Ids[k];
        // ====== GET FILES ====== //
        //var file = DriveApp.getFilesByName("AlpaGun2.csv").next();
        var file = DriveApp.getFileById(thisId); //doesnt need next for direct file
        var fileName = file.getName();
        Logger.log(fileName);
        var csvString = file.getBlob().getDataAsString();

        // ====== DATA TO ARRAY ====== //
        var array = CSVToArray(csvString, escapeCharacter);
        for (var i = 0; i < array.length; i++) { // apparently the last row is empty. Remove it to prevent errors when appending
            if (array[i] == "") {
                array.splice(i, 1);
                i--;
            }
        }

        // ====== OPEN NEW SHEET WITH NAME ====== //
        var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        var yourNewSheet = activeSpreadsheet.getSheetByName(fileName);

        if (yourNewSheet != null) {
            activeSpreadsheet.deleteSheet(yourNewSheet);
        }

        yourNewSheet = activeSpreadsheet.insertSheet();
        yourNewSheet.setName(fileName);

        // ====== FILL SHEET ====== //
        yourNewSheet.getRange(1, 1, array.length, array[0].length).setValues(array);

        // ====== REMOVE EMPTY LINES ====== //
        var maxRows = yourNewSheet.getMaxRows();
        var lastRow = yourNewSheet.getLastRow();
        yourNewSheet.deleteRows(lastRow + 1, maxRows - lastRow);

    }

    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert("Adding Files finished");

}



// ============ SANTAS LITTLE HELPERS ============ //


/**
 * CSVToArray parses any String of Data including '\r' '\n' characters,
 * and returns an array with the rows of data.
 * @param {String} CSV_string - the CSV string you need to parse
 * @param {String} delimiter - the delimeter used to separate fields of data
 * @returns {Array} rows - rows of CSV where first row are column headers
 */
function CSVToArray(CSV_string, delimiter) {
    delimiter = (delimiter || ","); // user-supplied delimeter or default comma

    var pattern = new RegExp( // regular expression to parse the CSV values.
        ( // Delimiters:
            "(\\" + delimiter + "|\\r?\\n|\\r|^)" +
            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
            // Standard fields.
            "([^\"\\" + delimiter + "\\r\\n]*))"
        ), "gi"
    );

    var rows = [[]];  // array to hold our data. First row is column headers.
    // array to hold our individual pattern matching groups:
    var matches = false; // false if we don't find any matches
    // Loop until we no longer find a regular expression match
    while (matches = pattern.exec(CSV_string)) {
        var matched_delimiter = matches[1]; // Get the matched delimiter
        // Check if the delimiter has a length (and is not the start of string)
        // and if it matches field delimiter. If not, it is a row delimiter.
        if (matched_delimiter.length && matched_delimiter !== delimiter) {
            // Since this is a new row of data, add an empty row to the array.
            rows.push([]);
        }
        var matched_value;
        // Once we have eliminated the delimiter, check to see
        // what kind of value was captured (quoted or unquoted):
        if (matches[2]) { // found quoted value. unescape any double quotes.
            matched_value = matches[2].replace(
                new RegExp("\"\"", "g"), "\""
            );
        } else { // found a non-quoted value
            matched_value = matches[3];
        }
        // Now that we have our value string, let's add
        // it to the data array.
        rows[rows.length - 1].push(matched_value);
    }
    return rows; // Return the parsed data Array
}

<!DOCTYPE html>
<html>

<head>
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
  <script>
    // IMPORTANT: Replace the value for DEVELOPER_KEY with the API key obtained
    // from the Google Developers Console.
    var DEVELOPER_KEY = 'AIzaSyCCTPpJ5Sajvjrb7S6vfh3dOl3cxdoskKA';
    var DIALOG_DIMENSIONS = { width: 600, height: 425 };
    var pickerApiLoaded = false;

    /**
     * Loads the Google Picker API.
     */
    function onApiLoad() {
      gapi.load('picker', {
        'callback': function () {
          pickerApiLoaded = true;
        }
      });
    }

    /**
     * Gets the user's OAuth 2.0 access token from the server-side script so that
     * it can be passed to Picker. This technique keeps Picker from needing to
     * show its own authorization dialog, but is only possible if the OAuth scope
     * that Picker needs is available in Apps Script. Otherwise, your Picker code
     * will need to declare its own OAuth scopes.
     */
    function getOAuthToken() {
      google.script.run.withSuccessHandler(createPicker)
        .withFailureHandler(showError).getOAuthToken();
    }

    /**
     * Creates a Picker that can access the user's spreadsheets. This function
     * uses advanced options to hide the Picker's left navigation panel and
     * default title bar.
     *
     * @param {string} token An OAuth 2.0 access token that lets Picker access the
     *     file type specified in the addView call.
     */
    function createPicker(token) {
      if (pickerApiLoaded && token) {

        // Show all files in Google Drive for selection
        // Instruct Picker to display only spreadsheets in Drive. For other
        // views, see https://developers.google.com/picker/docs/#otherviews
        var view = new google.picker.DocsView(google.picker.ViewId.DOCS);

        view.setIncludeFolders(true)
          .setSelectFolderEnabled(true)

        view.setMode(google.picker.DocsViewMode.LIST);


        var picker = new google.picker.PickerBuilder()
          .addView(view)

          // Hide the navigation panel so that Picker fills more of the dialog.
          //.enableFeature(google.picker.Feature.NAV_HIDDEN)
          .enableFeature(google.picker.Feature.MULTISELECT_ENABLED)
          .enableFeature(google.picker.Feature.SUPPORT_TEAM_DRIVES)

          // Hide the title bar since an Apps Script dialog already has a title.
          .hideTitleBar()
          .setOAuthToken(token)
          .setDeveloperKey(DEVELOPER_KEY)
          .setCallback(pickerCallback)
          .setOrigin(google.script.host.origin)
          // Instruct Picker to fill the dialog, minus 2 pixels for the border.
          .setSize(DIALOG_DIMENSIONS.width - 2,
            DIALOG_DIMENSIONS.height - 2)
          .build();
        picker.setVisible(true);
      } else {
        showError('Unable to load the file picker.');
      }
    }

    /**
     * A callback function that extracts the chosen document's metadata from the
     * response object. For details on the response object, see
     * https://developers.google.com/picker/docs/result
     *
     * @param {object} data The response object.
     */
    function pickerCallback(data) {
      var action = data[google.picker.Response.ACTION];
      if (action == google.picker.Action.PICKED) {
        var doc = data[google.picker.Response.DOCUMENTS][0];
        var id = doc[google.picker.Document.ID];
        var url = doc[google.picker.Document.URL];
        var title = doc[google.picker.Document.NAME];
        //document.getElementById('result').innerHTML =
        //    '<b>You chose:</b><br>Name: <a href="' + url + '">' + title +
        //    '</a><br>ID: ' + id;


        var escapeCharacter = document.getElementById('escapeCharacter').value;
        document.getElementById('result').innerHTML = "Adding files started. You can close this window now :)";


        // ====== sends the ID to Code.gs ====== @vinni
        var fileId = data.docs[0].id;
        var documentArray = [];
        // generate Id Array
        for (var i = 0; i < data.docs.length; i++) {
          var element = data.docs[i].id;
          documentArray.push(element);
        }



        // ====== This Calls the Script with IDs in Code.gs ====== @vinni
        google.script.run
          .withSuccessHandler(useData) // this will call the google apps script function in your Code.gs file
          .customPickerCallback(documentArray, escapeCharacter); // this is a function in your JavaScript section where you will do something with the code you got from your apps script function  
      } else if (action == google.picker.Action.CANCEL) {
        document.getElementById('result').innerHTML = 'Picker canceled.';
      }

    }

    /**
     * Displays an error message within the #result element.
     *
     * @param {string} message The error message to display.
     */
    function showError(message) {
      document.getElementById('result').innerHTML = 'Error: ' + message;
    }


    function useData(data) {
      // empty function. Not sure if necessary but seems to be successhandler
    }
  </script>
</head>

<body>
  <div>
    <img src="http://cloud.sansho.studio/hosting/logo.png" style="width: 39px; height: 39px;">
    <p style="margin-bottom: 0px">Set an escape character for your CSVs:</p>
    <br>
    <input id="escapeCharacter" value=",">
    <br>
    <br>
    <button onclick='getOAuthToken()'>Select your CSV Files</button>
    <p id='result'></p>
  </div>
  <script src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
</body>

</html>

/* 
CRAWLER THAT GENERATES 1 SHEET PER CSV
1. Chose CSV files and append them to the document
2. Empty the whole sheet
3. Merge all sheets to a first sheet called "Merge"
*/


// ============ GENERATES THE MENU ============ //

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Sansho Scraper')
        .addItem('Import CSV files', 'showPicker')
        .addSeparator()
        .addItem('Merge all sheets into "Merge"', 'mergeAllSheetsIntoMerge')
        .addSeparator()
        .addItem('Empty "Merge" sheet', 'emptyMergeSheet')
        .addSeparator()
        .addItem('Delete all imported sheets', 'deleteSheets')

        .addToUi();
}

function menuItem1() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert('You clicked the first menu item!');
}

function menuItem2() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert('You clicked the second menu item!');
}

// ============ PICKER FUNCTION ============ //

/**
 * Displays an HTML-service dialog in Google Sheets that contains client-side
 * JavaScript code for the Google Picker API.
 */
function showPicker() {
    var html = HtmlService.createHtmlOutputFromFile('Picker.html')
        .setWidth(600)
        .setHeight(425)
        .setSandboxMode(HtmlService.SandboxMode.IFRAME);
    SpreadsheetApp.getUi().showModalDialog(html, 'Select a file');
}

/**
 * Gets the user's OAuth 2.0 access token so that it can be passed to Picker.
 * This technique keeps Picker from needing to show its own authorization
 * dialog, but is only possible if the OAuth scope that Picker needs is
 * available in Apps Script. In this case, the function includes an unused call
 * to a DriveApp method to ensure that Apps Script requests access to all files
 * in the user's Drive.
 *
 * @return {string} The user's OAuth 2.0 access token.
 */
function getOAuthToken() {
    DriveApp.getRootFolder();
    return ScriptApp.getOAuthToken();
}


// ============ PICKER CALLBACK FUNCTION + COLLECT ALL CSV TO ONE SHEET EACH ============ //

function customPickerCallback(Ids, escapeCharacter) {
    collectAllFiles(Ids, escapeCharacter);
}


function collectAllFiles(Ids, escapeCharacter) {
    for (var k = 0; k < Ids.length; k++) {
        var thisId = Ids[k];
        // ====== GET FILES ====== //
        var file = DriveApp.getFileById(thisId); //doesnt need next for direct file
        var fileName = file.getName();
        Logger.log(fileName);
        var csvString = file.getBlob().getDataAsString();

        // ====== DATA TO ARRAY ====== //
        var array = CSVToArray(csvString, escapeCharacter);
        for (var i = 0; i < array.length; i++) { // apparently the last row is empty. Remove it to prevent errors when appending
            if (array[i] == "") {
                array.splice(i, 1);
                i--;
            }
        }

        // ====== OPEN NEW SHEET WITH NAME ====== //
        var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        var yourNewSheet = activeSpreadsheet.getSheetByName(fileName);

        if (yourNewSheet != null) {
            activeSpreadsheet.deleteSheet(yourNewSheet);
        }

        yourNewSheet = activeSpreadsheet.insertSheet();
        yourNewSheet.setName(fileName);

        // ====== FILL SHEET ====== //
        yourNewSheet.getRange(1, 1, array.length, array[0].length).setValues(array);

        // ====== REMOVE EMPTY LINES ====== //
        var maxRows = yourNewSheet.getMaxRows();
        var lastRow = yourNewSheet.getLastRow();
        yourNewSheet.deleteRows(lastRow + 1, maxRows - lastRow);

    }

    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert("Adding Files finished");

}


// ============ EMPTY SHEET COMPLETELY ============ //
function deleteSheets() {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

    for (var k = 0; k < sheets.length; k++) {
        var sheet = sheets[k];

        if (sheet.getName() !== "Merge") {
            Logger.log(sheet.getName());
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            ss.setActiveSheet(ss.getSheetByName(sheet.getName()));
            ss.deleteActiveSheet();
        }
    }
  
  // set first sheet active
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
}



// ============ MERGE SHEET ON FIRST SHEET CALLED "MERGE MASTER" ============ //
function mergeAllSheetsIntoMerge() {
    // var sheetMerger=SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; // selects the first
    var sheetMerger = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Merge");
    sheetMerger.clear();
    var maxRows = sheetMerger.getMaxRows();  // ====== REMOVE EMPTY LINES ====== //
    var lastRow = sheetMerger.getLastRow();

    if (maxRows > 10) { sheetMerger.deleteRows(5, maxRows - 5) } // position, how many

    // add the first sheet
    var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
    Logger.log(sheetMerger.getLastRow());
    var lastRowSheetMerger = sheetMerger.getLastRow();
    if (lastRowSheetMerger === 0) { lastRowSheetMerger = 1 }; //position 0 not possible

    var sheet2LastRow = sheet2.getLastRow();
    if (sheet2LastRow === 0) { sheet2LastRow = 1 }; //position 0 not possible

    sheetMerger.insertRowsAfter(lastRowSheetMerger, sheet2.getLastRow()) // add enough rows
    sheet2.getRange(1, 1, sheet2LastRow, sheet2.getLastColumn()).copyTo(sheetMerger.getRange(1, 1));

    //return;

    // loop through all sheets and add them
    var sheetLength = SpreadsheetApp.getActiveSpreadsheet().getSheets().length;

    for (var i = 2; i < sheetLength; i++) {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[i];
        //Logger.log(i);
        if (sheet.getLastRow() < 2) { continue }; //skip empty sheets
        sheetMerger.insertRowsAfter(sheetMerger.getLastRow(), sheet.getLastRow()) // add enough rows
        sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).copyTo(sheetMerger.getRange(sheetMerger.getLastRow() + 2, 1));
    }

    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .alert("Merged everything");
}

// ============ EMPTY MERGE SHEET ============ //
function emptyMergeSheet() {
    var sheetMerger = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Merge");
    sheetMerger.clear();

    // ====== REMOVE EMPTY LINES ====== //
    var maxRows = sheetMerger.getMaxRows();
    var lastRow = sheetMerger.getLastRow();
    sheetMerger.deleteRows(5, maxRows - 5); // position, how many
}





// ============ SANTAS LITTLE HELPERS ============ //


/**
 * CSVToArray parses any String of Data including '\r' '\n' characters,
 * and returns an array with the rows of data.
 * @param {String} CSV_string - the CSV string you need to parse
 * @param {String} delimiter - the delimeter used to separate fields of data
 * @returns {Array} rows - rows of CSV where first row are column headers
 */
function CSVToArray(CSV_string, delimiter) {
    delimiter = (delimiter || ","); // user-supplied delimeter or default comma

    var pattern = new RegExp( // regular expression to parse the CSV values.
        ( // Delimiters:
            "(\\" + delimiter + "|\\r?\\n|\\r|^)" +
            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
            // Standard fields.
            "([^\"\\" + delimiter + "\\r\\n]*))"
        ), "gi"
    );

    var rows = [[]];  // array to hold our data. First row is column headers.
    // array to hold our individual pattern matching groups:
    var matches = false; // false if we don't find any matches
    // Loop until we no longer find a regular expression match
    while (matches = pattern.exec(CSV_string)) {
        var matched_delimiter = matches[1]; // Get the matched delimiter
        // Check if the delimiter has a length (and is not the start of string)
        // and if it matches field delimiter. If not, it is a row delimiter.
        if (matched_delimiter.length && matched_delimiter !== delimiter) {
            // Since this is a new row of data, add an empty row to the array.
            rows.push([]);
        }
        var matched_value;
        // Once we have eliminated the delimiter, check to see
        // what kind of value was captured (quoted or unquoted):
        if (matches[2]) { // found quoted value. unescape any double quotes.
            matched_value = matches[2].replace(
                new RegExp("\"\"", "g"), "\""
            );
        } else { // found a non-quoted value
            matched_value = matches[3];
        }
        // Now that we have our value string, let's add
        // it to the data array.
        rows[rows.length - 1].push(matched_value);
    }
    return rows; // Return the parsed data Array
}

Workflow