How can I automatically download a .csv file from a hyperlink in a GMAIL message and add its contents to a Google Spreadsheet

Question

I receive an email with a hyperlink that when clicked starts a download of a csv file to my Gmail account. It's not an actual attachment. When I receive this email (which has a unique subject line), I need a way to automatically add the contents of the downloaded .csv

Trigger: An email with a specific subject line is received to my gmail account

Action 1: Download a .csv file from a hyperlink within the body of the email

Action 2: Add the contents of the .csv file to a Google Sheet file

I need an already built service that does this or suggestions on how to approach it.

If I can get this Google script to run, I should be able to find a working solution. The problem is the script keeps giving me errors.

function downloadFile(fileURL,folder) {

  var fileName = "";
  var fileSize = 0;

  var response = UrlFetchApp.fetch(fileURL, {muteHttpExceptions: true});
  var rc = response.getResponseCode();

  if (rc == 200) {
    var fileBlob = response.getBlob()
    var folder = DocsList.getFolder(folder);
    if (folder != null) {
      var file = folder.createFile(fileBlob);
      fileName = file.getName();
      fileSize = file.getSize();
    }
  }

  var fileInfo = { "rc":rc, "fileName":fileName, "fileSize":fileSize };
  return fileInfo;
}

I tried connecting with a Zapier integration, but the solutions provided are not sufficient. There are a few scripts that attempt this with an actual attachment, but I'm struggling with this being a hyperlink download. I feel like this should actually be easier, but I'm probably wrong. — Paul Jump
I've also tried the instructions here with no luck: ctrlq.org/code/20053-save-gmail-to-google-spreadsheet — Paul Jump
@PaulJump Why did you unaccept this answer? Does this suffice for you? — Douglas Gaskell

Douglas Gaskell Douglas Gaskell · Accepted Answer · 2016-04-27T01:20:24

This is something I recently tackled at work, fully automating data pulls from my emails to a database. I am not going to write a solution for you, but I will provide you with the information and links you need to do it yourself.

Note: Your question is very broad, and covers a large range of different problems, each of which should be tackled one at a time with their own question (Many of which already have multiple answers on StackOverflow). This is a process to follow with linked documentation, and a couple code snippets so you can do it yourself and tackle each problem along the way.

The Proposed Process:

Open the email with the GmailApp Service
Extract the link via the script below
Get the CSV from the link via the code linked below. This utilizes UrlFetchAp, the Blob datatype, and the parseCsv utility (which you have to escape commas first, because it's buggy)
Modify the contents of the resulting array to your liking
Use the SpreadsheetApp Service to open a spreadsheet and get a range
Set the values of that range to your array of data.

Extract href link from email (assumes only 1 link):

//Retrieves a URL from a HTML string from an href. Only applicable if there is only one link in the string
function GetHrefURLsFromString(string){
  var href = string.match(/href="([^"]*)/)[1];
  if(href){
    return href;
  } else {
    throw "No URL Found"
  } 
}

Extract CSV from link:

//Gets a CSV from a provided link, and parses it.
function GetCSVFromLink(link){
  var urlData = UrlFetchApp.fetch(link);
  if(urlData.getBlob().getContentType() == 'csv'){
    var stringData = urlData.getContentText();
    var escapedStringData = stringData.replace(/(?=["'])(?:"[^"\\]*(?:\\[\s\S][^"\\]*)*"|'[^'\\]\r\n(?:\\[\s\S][^'\\]\r\n)*')/g, '\r\n');
    var CSV = Utilities.parseCsv(escapedStringData);
    return CSV;    
  }
  Logger.log('DataType Not CSV')
  return null;
}

How can I automatically download a .csv file from a hyperlink in a GMAIL message and add its contents to a Google Spreadsheet

1 Answers