How to Import CSV Files into Google Spreadsheets with Google Apps Script

You can easily import CSV files into Google Spreadsheet using the Utilities.parseCsv() method of Google Apps Script. The snippets here show how to parse and import data from CSV files that are on the web, saved on Google Drive or available as a Gmail attachments.

Import CSV from an email attachment in Gmail

function importCSVFromGmail() {
  var threads = GmailApp.search('from:reports@example.com');
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  // Is the attachment a CSV file
  if (attachment.getContentType() === 'text/csv') {
    var sheet = SpreadsheetApp.getActiveSheet();
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ',');

    // Remember to clear the content of the sheet before importing new data
    sheet.clearContents().clearFormats();
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
}

Import CSV file that exists in Google Drive**

function importCSVFromGoogleDrive() {
  var file = DriveApp.getFilesByName('data.csv').next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Fetch and import CSV file from an external website

function importCSVFromWeb() {
  // Provide the full URL of the CSV file.
  var csvUrl = 'https://ctrlq.org/data.csv';
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

This method can however not be used for importing CSV files hosted on FTP servers as the UrlFetchApp service of Google Scripts doesn’t allow connecting to FTP servers, yet.

Awards & Recognition