Scripting in Google Sheets

January 24th, 2019

Scripting in Google Sheets

TLDR: Building a custom Google Sheets Menu that imports many CSV Files and afterwards merges them into one big sheet. Easier than expected and saved us so much time.

For a recent project, we were crawling a lot of data into many different generated Csv files. Each data set was kept as a separate file to have a better overview on the data. In the end we had around 200 files that needed to be merged into one big Google Sheet for data analysis. After painstakingly merging the first by hand, we tried to automate it, as the different data sheets would also update during the project. In the end we created custom menu in our Google Sheet which could call all the necessary functions: Importing a folder of Csv's and then merging them into one.

1. Creating a Menu

Creating a custom menu in Google Sheets is rather easy. Open the Script Editor «Tools > Script Editor». And put the following code into the Code.gs file:

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!');
}

This gives us buttons to trigger different functions from our Sheet. Now, when pressing a menu point, the named function is getting called:

Write the code into Code.sg and save it. Write the code into Code.sg and save it.
A custom menu appears on hard reload. A custom menu appears on hard reload.
Popup is triggered. Popup is triggered.

2. Importing CSV Sheets

The import consists of two steps:

  1. Authenticating the user with his Google Drive account, so we can access the Csv's (and don't need to manually upload them each time)
  2. Importing each Csv into a new Sheet

To do this, we change the first menu point and add a couple functions. Update your Code.gs with this code:

/* 
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
}

Also, you'll see that the script will open a custom HTML, which is going to hold our File Picker Modal. Add the following code in the Script editor with «File > New > HTML-File» and call it Picker.html:

<!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>

Then, our Sheet importer is ready to be used:

Chose Import CSV Files in the custom menu. Chose Import CSV Files in the custom menu.
Set the correct escape Character (Usually «,») Set the correct escape Character (Usually «,»)
Mark all the files (not folders). Mark all the files (not folders).
Voilà, each file got imported as one sheet with the correct name. Voilà, each file got imported as one sheet with the correct name.

Further we added three other functions:

  1. Merge all the imported Sheets into the Sheet "Merge" (Make sure to have one called like that)
  2. Empty the "Merge" Sheet (This speeds up emptying, if it's really big)
  3. Delete all imported Sheets

This is the full Code.gs Code:

/* 
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

Lastly, the way we used the importer in our case:

  1. We scraped the data with a node.js server into Csv files
  2. Those files were uploaded into a Google Drive folder
  3. We imported all these Csv's into one Master Sheet
  4. And merged them into one big Sheet
  5. The merged sheet was exported back into a Csv and then analyzed with Open Refine
  6. The result was moved back into Google Sheets, where we calculated the results

The functions we used most for 6. were Filter(), Unique(), Count If(). More details on this after the data is released.

Cheers!

Web-based Facefilter with Speech Recognition?

Yes we can!

Web-based Facefilter with Speech Recognition?

Collecting cross-device gyroscope data with lastdab.com

As we couldn’t find any online.

Collecting cross-device gyroscope data with lastdab.com