Import CSV Data to Google Sheets from Gmail using Google Apps Scripts

Save yourself time by using this script to automatically pull data from a CSV file you receive as a Gmail attachment into a Google Sheet. I find this script especially useful when working with CSV files I receive on a recurring basis.

The below script is meant to find an email that meets your search criteria and import the data to a specified sheet. To add the script, click on Extensions > App Scripts in the top menu.

  function importCSVFromGmail() {

  var threads = GmailApp.search("CSV"); // enter search criteria here
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];


  var sheet = SpreadsheetApp.getActiveSheet(); // runs in the current active sheet
  var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");

    sheet.clearContents().clearFormats(); // clears target sheet
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }

Some notes about the script:

• The script will only retrieve a CSV file from one email
• I have the script set up to perform a search to identify the email. Your target email has to be the only or latest email in the search criteria

In order to make calling the script easier, you can either schedule the script to run on a defined frequency from the Script Editor, or you can add this code at the end to create a custom menu. Next time you refresh your sheet, you’ll see a new menu item at the top and you can run the script from that menu.

function onOpen(e) {
   SpreadsheetApp.getUi()
       .createMenu('Custom Menu')
       .addItem('Import CSV Data', 'importCSVFromGmail')
       .addToUi();
 }

Thanks for reading!