Trouble Uncompressing Zip and then parsing CSV with Google Apps Script

Question

Trying to create a Google Apps Script that reads emails from a specific email address and copy the contents of a CSV file into a Google sheet.

Problem is sometimes the files come in as zip attachments. I'm trying to work logic into the script to uncompress the zip if it's a zip attachment and then post the data to the Google sheet.

Currently getting the following error message: Cannot find function getDataAsString in object Blob.

Any ideas / recommendations for getting this to work with potential zip files would be great.

You can find my full code below:

function myFunction() {
    var threads = GmailApp.search("from:[email protected]");
    var message = threads[0].getMessages()[0];
    var attachment = message.getAttachments()[0];
    Logger.log(attachment.getContentType());
    // Is the attachment a CSV file
    if (attachment.getContentType() === "text/csv") {
        var sheet = SpreadsheetApp.getActiveSheet();
        var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");
      Logger.log("Found a CSV file");
        // 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);
    }
    if (attachment.getContentType() === "application/zip") {
        var sheet = SpreadsheetApp.getActiveSheet();
        var files = Utilities.unzip(attachment);
      Logger.log(files);
        var newDriveFile = DriveApp.createFile(files[0]);
        var csvData = Utilities.parseCsv(files.getDataAsString(), ",");
        Logger.log("Found a ZIP file");
        // 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);
    }
}

Ritz Ritz · Accepted Answer · 2017-11-15T20:30:59

Utilities.unzip will return array of blobs. So you need to refer the particular blob inside the array of blobs. Just modify the below code snippet.

var files = Utilities.unzip(attachment)[0];

Also, it is safe to mention the charset so that special characters are displayed properly.

var csvData = Utilities.parseCsv(attachment.getDataAsString('ISO-8859-1'), ",");

Entire Function:

function exportData() {
    var threads = GmailApp.search("from:[email protected]");
    var message = threads[0].getMessages()[0];
    var attachment = message.getAttachments()[0];
    Logger.log(attachment.getContentType());
    // Is the attachment a CSV file
    if (attachment.getContentType() === "text/csv") {
        var sheet = SpreadsheetApp.getActiveSheet();
        var csvData = Utilities.parseCsv(attachment.getDataAsString('ISO-8859-1'), ",");
      Logger.log("Found a CSV file");
        // 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);
    }
    if (attachment.getContentType() === "application/zip") {
        var sheet = SpreadsheetApp.getActiveSheet();
        var files = Utilities.unzip(attachment)[0];
        var csvData = Utilities.parseCsv(files.getDataAsString('ISO-8859-1'), ",");
        Logger.log("Found a ZIP file");
        // 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);
    }
}

Trouble Uncompressing Zip and then parsing CSV with Google Apps Script

1 Answers