I have recently been trying to import a folder from a zip file into sheets. I have tried the below code but it only returns the first file. I have tried multiple ways to get it to return a file by name but to no avail.
Please would someone be able to show me how I can get the script to search the extracted folder for a certain file and import that instead.
function getCSV() {
var myLabel = GmailApp.getUserLabelByName("Auction_HVT");
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 attachmentName = extracted.getName()
var data = Utilities.parseCsv(string);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("HVT");
sheet.clearContents();
var range = sheet.getRange(1,1, data.length,data[0].length);
range.setValues(data);
}
CodePudding user response：
I believe your goal is as follows.
- You want to retrieve a file (CSV file) from a zip file by searching the filename.
- You want to put the CSV data from the retrieved file.
In this case, how about the following modification? Utilities.unzip() returns Blob[]. So you can search the specific file from Blob[].
Modified script:
From:
var csv = attachments[0];
var extracted = Utilities.unzip(csv);
var attachmentName = extracted.getName()
var data = Utilities.parseCsv(string);
To:
var searchFilename = "sample.csv"; // Please set the filename of the CSV file you want to retrieve.
var csv = attachments[0].setContentTypeFromExtension();
var extracted = Utilities.unzip(csv);
var csv = extracted.find(e => e.getName().slice(-searchFilename.length) == searchFilename);
if (!csv) {
throw new Error(searchFilename " was not found.");
}
var string = csv.getDataAsString();
var data = Utilities.parseCsv(string);