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

September 06, 2017

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.

Google Apps Script Google Sheets

ShareWhatsAppTwitterFacebook

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory