CODEWITHSUNDEEP
javascriptcsvgoogle-apps-scriptgoogle-sheetsgmail
Munkey
Munkey

Reputation: 956

Extract CSV from ZIP file in gmail thread and write data to a google sheets

I'm working on a script that does the following. Looks for a specific label in gmail, "test". Find the first message and get the attachment (Which is a ZIP, containing a single CSV) Extract the CSV and the write it to a google spreadsheet.

I've managed to do this with a CSV attachment, but not a zip with a CSV inside.

Here's where I'm at so far. I'm sure it's something simple I'm missing, but it seems to be reading from the ZIP and writing weird chars rather than reading from the CSV inside.

    function getCSV() {
 var myLabel = GmailApp.getUserLabelByName("test");
 var threads = myLabel.getThreads(0,1);
 var msgs = GmailApp.getMessagesForThreads(threads);
 var attachments = msgs[0][0].getAttachments();

 Logger.log(attachments);

var csv =  attachments[0];

Logger.log(csv);

 var extracted = Utilities.unzip(csv);

 Logger.log("unzipped data "+ extracted);
 var toString = extracted.getBlob.getDataAsString();

 Logger.log(toString);

 var data = Utilities.parseCsv(toString);
 Logger.log(data);


//var ss = SpreadsheetApp.getActiveSpreadsheet();
//var sheet = ss.getSheetByName("TicketDump");
//sheet.clearContents();
//var range = sheet.getRange(1,1, data.length,data[0].length);
//range.setValues(data);
// 

}

In case it sheds any light on my problem, here is the script I made to import CSV attachments to google sheets, this works.

function getCSV() {



 var myLabel = GmailApp.getUserLabelByName("test");
 var threads = myLabel.getThreads(0,1);
 var msgs = GmailApp.getMessagesForThreads(threads);
 var attachments = msgs[0][0].getAttachments();

 var csv =  attachments[0].getDataAsString();
 var data = Utilities.parseCsv(csv);



var a = data.length ;
var b = data[0].length; 


var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("TicketDump");
var super = sheet.getRange("E1").getFormula();
var ticketLink = sheet.getRange("F2:F").getFormulas();
sheet.clearContents();
var range = sheet.getRange(sheet.getLastRow()+1,5, a, b);
var range = sheet.getRange(1,1, a, b);
range.setValues(data);
sheet.getRange("E1").setFormula(super);
sheet.getRange("F1").setValue("Header name");
sheet.getRange("F2:F").setFormulas(ticketLink);


}

Upvotes: 1

Views: 2403

Answers (1)

Munkey
Munkey

Reputation: 956

Found our where I was going wrong.

it was the toString part of the code. I didn't tell it which entry

This part;

var toString = extracted.getBlob.getDataAsString();

should be something like

var toString = extracted[0].getDataAsString();

Final code looks like this 

    function getCSV() {
 var myLabel = GmailApp.getUserLabelByName("test");
 var threads = myLabel.getThreads(0,1);
 var msgs = GmailApp.getMessagesForThreads(threads);
 var attachments = msgs[0][0].getAttachments();



var csv =  attachments[0];
var extracted = Utilities.unzip(csv);
var string = extracted[0].getDataAsString();
var data = Utilities.parseCsv(string);




var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("TicketDump");
sheet.clearContents();
var range = sheet.getRange(1,1, data.length,data[0].length);
range.setValues(data);


}

Upvotes: 2

Related Questions