Google Apps Script:
Working with CSV

How to:

Reading CSV Data

To read CSV data from a file stored in Google Drive, you first need to get the file’s content as a string, then parse it. Google Apps Script makes fetching file content straightforward with the DriveApp service.

function readCSV() {
  var fileId = 'YOUR_FILE_ID_HERE'; // Replace with actual file ID
  var file = DriveApp.getFileById(fileId);
  var content = file.getBlob().getDataAsString();
  var rows = content.split("\n");
  
  for (var i = 0; i < rows.length; i++) {
    var cells = rows[i].split(",");
    Logger.log(cells); // Log each row's cells
  }
}

Writing CSV Data

Creating and writing to a CSV entails constructing a string with comma-separated values and newlines, then saving or exporting it. This example demonstrates creating a new CSV file in Google Drive.

function writeCSV() {
  var folderId = 'YOUR_FOLDER_ID_HERE'; // Replace with the ID of the Drive folder where the new file will be created
  var csvContent = "Name,Age,Occupation\nJohn Doe,29,Engineer\nJane Smith,34,Designer";
  var fileName = "example.csv";
  
  var folder = DriveApp.getFolderById(folderId);
  folder.createFile(fileName, csvContent, MimeType.PLAIN_TEXT);
}

Sample Output

When logging row cells from reading a CSV:

[John, 29, Engineer]
[Jane, 34, Designer]

When writing, a file named “example.csv” is created with the content:

Name,Age,Occupation
John Doe,29,Engineer
Jane Smith,34,Designer

Deep Dive

Historically, CSV files have been favored for their simplicity and human readability, making them accessible to non-programmers and useful for quick data-inspection tasks. However, Google Apps Script operates within the realm of Google’s ecosystem, where Google Sheets acts as a powerful, user-friendly alternative for CSV manipulation. Sheets not only provide a GUI for editing data but also support complex formulas, styling, and many more features that raw CSVs lack.

Despite the advantages offered by Google Sheets, direct CSV manipulation in Google Apps Script remains important for automated tasks, especially when dealing with external systems that generate or require data in CSV format. For instance, integrating with legacy systems, exporting data for use in other applications, or preprocessing before feeding data into Google Sheets.

Moreover, Google Apps Script’s ability to work with CSV files can be extended with the Utilities service for advanced encoding needs, or interfaced with external APIs for conversion, parsing, or validation tasks. However, for working with large datasets or requiring complex manipulations, consider leveraging Google Sheets APIs or exploring BigQuery for more robust data processing capabilities.

While simplicity remains a key reason for CSV’s popularity, these alternatives offer a richer set of features for dealing with data in the expansive Google Cloud ecosystem.