Madproject

Always know where your towel is.

Google Apps Script: Importing a text file via a form file input

Google Apps Script

One of the organisations I work for makes heavy use of Google Apps such as Drive & Spreadsheet. They wanted the ability to upload/import a CSV file, perform automated operations on the data and finally spit out the result into a Google Spreadsheet.

I built the text/csv file import facility by using the FileReader API. Essentially we read the file locally and pass the data along to Google Script.

Sidebar.html

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<!-- Below is the HTML code that defines the sidebar element structure. -->
<div class="sidebar branding-below">

  Import the CSV file to automatically format it

  <form id="csvForm">

    <div class="form-element file-input">
      <input type="file" name="myFile" id="myFile">
    </div>

    <div class="form-element">
      <button type="submit" id="upload-csv" class="action">Upload CSV</button>
    </div>

  </form>

  <div id="output"></div>

</div>

<!-- Enter sidebar bottom-branding below. -->
<div class="sidebar bottom">
  <span class="gray branding-text">By Trevor Wistaff</span>
</div>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('SidebarJavaScript').getContent(); ?>

SidebarJavaScript.html

<script src="//cdnjs.cloudflare.com/ajax/libs/PapaParse/4.1.1/papaparse.min.js"></script>
<script>
  var form = document.getElementById('csvForm');

  form.addEventListener('submit', function(e) {
    // Prevent the default submit behaviour
    e.preventDefault();
    // Reset the output text if any
    document.getElementById('output').innerHTML = '';
    // Read the CSV
    readCSV();
  });

  function readCSV() {
    // Grab the first file out of the array
    var myFile = form.myFile.files[0];

    // Very basic file type checking
    if(myFile.name.split('.').pop().toLowerCase() === 'csv') {
      // Initialize the FileReader
      var reader = new FileReader();

      // Define the onload method belonging to the reader object
      reader.onload = function(e) {
        // Here I'm using Papa Parse to parse the CSV
        var csv = Papa.parse(reader.result);
        // Define 2 handlers that handle the result of processCSV and finally call processCSV()
        // which is defined in my Import.gs file.
        google.script.run.withSuccessHandler(postHandler).withFailureHandler(postHandler).processCSV(csv);
      }
    
      // Read the file as text. This will then run reader.onload define above.
      reader.readAsText(myFile);
    } else {
      document.getElementById('output').innerHTML = "That's just silly, remember you can only import CSV files!";
    }
  }

  function postHandler(status) {
    document.getElementById('output').innerHTML = status;
  }
 
</script>

Note: You must have your script’s sandbox mode set to IFRAME. As of yesterday, all new GA Scripts will default to IFRAME as per their sunset schedule, so unless you’re working on an old script, no need to worry about this.

Post navigation

Leave a Reply

Your email address will not be published. Required fields are marked *