MarquinSmith

When working hands on with data, the data can be in many different forms and in many different locations.

The most common format is arguably csv format, and the most prevalent distribution method is email. So this post is going to run through how to set up a process that loads a csv file that is received on email (to a gmail account) into a Google Sheet.

Tools:

• Gmail
• Google Sheets
• Google Apps Script (Getting started guide)

Conditions:

• The email with csv attached data arrives at a predictable time
• There is a single csv file attached to the email
• There is a consistent pattern to identify the specific email with the csv attachment (eg a specific sender , subject title combination)

Walkthrough

Inspired by https://www.labnol.org/code/20279-import-csv-into-google-spreadsheet

Create a Google Spreadsheet that the data from the csv will be loaded into.

In this spreadsheet open the script editor and paste in either one of the following code blocks. Modifications will be needed to be able to apply specifically to your use case.

Base functional code template:

function importCSVFromGmail() {

  var threads = GmailApp.search("from:reports@example.com");
  // example of getting thread for a specific email title
  // var threads = GmailApp.search("from:reports@example.com subject:\"email with data"");

  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  // Is the attachment a CSV file
  if (attachment.getContentType() === "text/csv") {

      // Select Google sheet and tab named "autoupload"
    var spreadsheet = SpreadsheetApp.openById("Spreadsheet_ID");
    var sheet = spreadsheet.getSheetByName('autoupload').activate();

    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);

  }
}

Modifications likely needed:

• line 3: change to the sender and subject that the csv data is coming from
• lines 11 and 12: change to the spreadsheet ID you are working with and the name of the tab to load data into

If there is are multiple emails that match the search criteria “GmailApp.search("from:reports@example.com subject:\"email with data"")” then the code will extract the csv data from the latest email.

Once you are happy the code is working as needed, it is necessary to schedule a time for the code to run automatically.

This is done within the “Triggers” area of apps script

The below is an example of the importSydneyLockdownDashboardCSVFromGmail function scheduled to run everyday once between the hours of 7am and 8am AEST

The same function can be set to run accordingly to multiple schedules if required.

With all this in place we have successfully scheduled a