How to Import CSV Files into Google Sheets Using Google Apps Script

If you use Microsoft Excel, you are probably used to using and importing data from external sources, eg CSV files. We managed to have this same facility in Google Sheets using Google Apps Script.

What is Google Apps Script?

THE Apps Script is a scripting platform developed by Google for developing lightweight applications on the G Suite platform. It is based on JavaScript 1.6, but it also includes some functionality from 1.7, 1.8 and a subset of the ECMAScript 5 API. The projects of the Script apps run on Google's cloud infrastructure. According to the Google , the Apps Script "provides easy ways to automate tasks on third-party products and services." Apps Script is also available for Google Docs and Slides.

You find t All scripts in this tutorial on example worksheet , make a copy on your drive to be able to view the scripts and edit the spreadsheet.

Also check out : Transferring files over SFTP on Google Cloud using FileZilla

Accessing the Apps Script

To access Google Apps Scripts, create a blank spreadsheet or copy the example worksheet ; click in Tools > Script Editor :

A new tab with the Google Apps Script text editor will open:

Creating the Scripts

We can easily import CSV files into Google Sheets using the function Utilities.parseCsv() from Google Apps Script. The codes below show how to import and display data from a CSV file by URL, saved in Google Drive, or as an attachment in Gmail.

Authorizing Google Apps Scripts

For all the examples below, when running the scripts we need to authorize Google Apps Scripts to access some functionality of the Google APIs.

Probably, because your script is not yet approved, the screen below will appear:

click in Show Advanced > Access Project and proceed to authorization:

Importing the CSV file from an email attachment into Gmail

function importCSVDoGmail() {
  
  var emails = GmailApp.search("from:henrique@marquesf.com"); // We filter our emails
  var email = emails[0] .getMessages()[0] ; // We get the first message from the email thread
  var attachment = email.getAttachments()[0] ; // We get the first attachment of the email
  
  // We validate if this attachment is a CSV
  if (attachment.getContentType() === "text/csv") {
    
    var sheet = SpreadsheetApp.getActiveSheet(); // We select the object of the active sheet
    var csv = Utilities.parseCsv(sheet.getDataAsString(), ",");
    
    // We clean the contents of the spreadsheet before importing the data
    sheet.clearContents().clearFormats();
    // We import all data from cell A1
    sheet.getRange(1, 1, csv.length, csv[0] .length).setValues(csv);
  } 
}

In our emails variable we will perform a filter search in our Gmail to return the first matching email, we can use any Gmail search operator within the function GmailApp.search("operator:search") , check here the full list of operators .

Importing Google Drive CSV File

function importCSVDoGoogleDrive() {

    var file = DriveApp.getFilesByName("data.csv").next();
    var csv = Utilities.parseCsv(file.getBlob().getDataAsString());
    var sheet = SpreadsheetApp.getActiveSheet();
    spreadsheet.getRange(1, 1, csv.length, csv[0] .length).setValues(csv);

}

In the example above we are looking for the file date.csv which is at the root of Google Drive, change this path as needed.

Download and import the CSV file from an external website

function importCSVDaWeb() {

    // CSV file download URL
    var csvUrl = "https://marquesfernandes.com/wp-content/uploads/2020/01/example_csv.csv";
    var csv = UrlFetchApp.fetch(csvUrl).getContentText();
    var data = Utilities.parseCsv(csv);

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

}

Remembering that the service UrlFetchApp it only makes HTTP requests, it is not yet possible to connect to FTP servers.