Tutorial: Interacting With Your Google Drive with Apps Script (including parsing/writing .csv files)

A question came in on the Google Apps Script Google+ Community which in part was asking about parsing .csv files into a Google Sheet. I saw the question come in over my phone and knowing it was very achievable directed the person to the Interacting With Your Docs List tutorial on the Google Developers site as a starting point. In particular this tutorial included a function to parse a .csv file using regular expressions. Shortly after +Andrew Roberts kindly pointed out  that Apps Script includes a parseCsv method … doh. +Marcos Gomes then pointed out that the tutorial uses the Docs List Service that was deprecated on December 11, 2014 … doh. Given the tutorial I referenced was written in May 2010 it’s not surprising given the updates in Apps Script that it’s now out of date and the tutorial itself should probably be carrying a deprecated notice. One of the really nice things about the Google Developers site is most, if not all, the documentation is released under Creative Commons Attribution 3.0 License. So for my penance below is a reworking of Tutorial: Interacting With Your Docs List CC-BY Google Inc. (Jan Kleinert)

Goal

This tutorial shows how to search the files in your Google Drive and display the results and how to import from and export to CSV files which are saved in your Google Drive.

Time to Complete

Approximately 10 minutes

Prerequisites

Before you begin this tutorial, you should already be familiar with:

Index

This tutorial is divided into the following sections:

Section 1: Searching your Google Drive and displaying matching files

In this section, we assume have some files stored in your Google Drive. We will write a script to create a custom menu, search through the files for a user-supplied search term, and display the results in a Google Sheet.

  1. Create a new Google Sheet, and open the script editor (Tools > Script editor)
  2. Copy, paste, and save the following code. The onOpen function will run when the spreadsheet is opened and will add a custom menu named Search Google Drive to your spreadsheet’s menu bar. The custom menu has one option Search in all files, which will run the function named search.
    function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
      ss.addMenu("Search Google Drive", searchMenuEntries);
    }
    function search() {
      // Prompt the user for a search term
      var searchTerm = Browser.inputBox("Enter the string to search for:");
      // Get the active spreadsheet and the active sheet
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      // Set up the spreadsheet to display the results
      var headers = [["File Name", "File Type", "URL"]];
      sheet.clear();
      sheet.getRange("A1:C1").setValues(headers);
      // Search the files in the user's Google Drive for the search term
      // See documentation for search parameters you can use
      // https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)
      var files = DriveApp.searchFiles("title contains '"+searchTerm.replace("'","\'")+"'");
      // create an array to store our data to be written to the sheet
      var output = [];
      // Loop through the results and get the file name, file type, and URL
      while (files.hasNext()) {
        var file = files.next();
        var name = file.getName();
        var type = file.getMimeType();
        var url = file.getUrl();
        // push the file details to our output array (essentially pushing a row of data)
        output.push([name, type, url]);
      }
      // write data to the sheet
      sheet.getRange(2, 1, output.length, 3).setValues(output);
    }
  3. Save and reload your spreadsheet so that the Search Google Drive menu appears in your menu bar. Select Search Google Drive then Search in all files and enter a search term. You should see the results displayed in your spreadsheet.
    Custom menu in Google Sheets

Section 2: Importing data from a CSV file

In this section, we assume you have one or more CSV files saved in your Google Drive. We’ll write code to import a CSV file from your Google Drive into a spreadsheet.

  1. In Google Drive, open the spreadsheet you just saved, then click Tools > Script editor).
  2. Replace the existing onOpen function with the following code.
    function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
      var csvMenuEntries = [{name: "Load from CSV file", functionName: "importFromCSV"}];
      ss.addMenu("Search Google Drive", searchMenuEntries);
      ss.addMenu("CSV", csvMenuEntries);
    }
  3. Copy the following code, and paste it at the bottom of your script. The importFromCSV function prompts the user for the name of the CSV file, and then searches through their Google Drive to find the matching file. It then calls the parseCsv Utiltity, which is a handy tool for converting CSV data to a Javascript array.
    function importFromCSV() {
      var fileName = Browser.inputBox("Enter the name of the file in your Google Drive to import (e.g. myFile.csv):");
      var searchTerm = "title = '"+fileName+"'";
      // optionally you might want to specify that you are looking for a .csv by file type using:
      // var searchTerm = "title = '"+fileName+"' mimeType = 'text/csv'";
      // read more about searching for files at
      // https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)
      // search for our file
      var files = DriveApp.searchFiles(searchTerm)
      var csvFile = "";
      // Loop through the results
      while (files.hasNext()) {
        var file = files.next();
        // assuming the first file we find is the one we want
        if (file.getName() == fileName) {
          // get file as a string
          csvFile = file.getBlob().getDataAsString();
          break;
        }
      }
      // parseCsv will return a [][] array we can write to a sheet
      var csvData = Utilities.parseCsv(csvFile);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      // boom data to a sheet
      sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    }
  4. Save your code and reload your spreadsheet, so that the CSV menu appears in your menu bar. Select CSV then Load from CSV file and enter the file name to import. You should see the contents of the CSV file appear in your spreadsheet. For example, if you had the following CSV data saved in a file in your Docs List, then you would see the results like those shown in the image below.
    First Name,Last Name,Department,Extension
    Jane,Smith,Engineering,6113
    John,Doe,Marketing,1877
    Sam,Jones,Sales,2233
    

    Test data in Google Sheet

Section 3: Saving a selected Range to a CSV file

In this final section we add code to save a selected Range in a spreadsheet to a CSV file in your Google Drive.
Note: This section uses the DriveApp.createFile() method.

  1. In Google Drive, open the spreadsheet you just saved, then click Tools > Script editor.
  2. Replace the existing onOpen function with the following code.
    function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
      var csvMenuEntries = [{name: "Save as CSV file", functionName: "saveAsCSV"},
                          {name: "Load from CSV file", functionName: "importFromCSV"}];
      ss.addMenu("Search Google Drive", searchMenuEntries);
      ss.addMenu("CSV", csvMenuEntries);
    }
  3. Copy the following code, and paste it at the end of your script..
    function saveAsCSV() {
      // Prompts the user for the file name
      var fileName = Browser.inputBox("Save CSV file as (e.g. myCSVFile):");
      // Check that the file name entered wasn't empty
      if (fileName.length !== 0) {
        // Add the ".csv" extension to the file name
        fileName = fileName + ".csv";
        // Convert the range data to CSV format
        var csvFile = convertRangeToCsvFile_(fileName);
        // Create a file in Drive with the given name, the CSV data and MimeType (file type)
        DriveApp.createFile(fileName, csvFile, MimeType.CSV);
      }
      else {
        Browser.msgBox("Error: Please enter a CSV file name.");
      }
    }
    function convertRangeToCsvFile_(csvFileName) {
      // Get the selected range in the spreadsheet
      var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection();
      try {
        var data = ws.getValues();
        var csvFile = undefined;
        // Loop through the data in the range and build a string with the CSV data
        if (data.length > 1) {
          var csv = "";
          for (var row = 0; row < data.length; row++) {
            for (var col = 0; col < data[row].length; col++) {
              if (data[row][col].toString().indexOf(",") != -1) {
                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);
      }
    }
  4. Save your code and reload your spreadsheet. Select the range in your spreadsheet that you want to save as a CSV file. Select CSV and then Save as CSV file. Enter the name for the file.
    Running Save as CSV file
    The file will be created and will show up in your Google Drive.
    myTestFile in Google Drive

Summary

Congratulations, you’ve completed this tutorial. You should now be able search, open, and create files using the DriveApp service. To learn more, check out the documentation for the DriveApp services.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License.
chevron_left
chevron_right
css.php