I have a script that takes CSV files from a gmail attachment, and pushes this to a google sheet. Currently the script will unzip files as well.
I would like this certain script to be able to extract excel files as well. How can I do this?
the script takes off rows at the bottom of the attached CSV file, as this is redundant data. Currently, the sheet has a variable that tells how many rows are to be taken off at the bottom of the sheet, which is set in the "Settings sheet" in the google doc. Is it possible to change this to how many rows are taken off the top, and how many are taken off the bottom of the attached file?
A copy of the excel file in question:https://docs.google.com/spreadsheets/d/1bNXAC3-I5EvUeHB3FYmJCkP3dw5Mk3Xe_DtYosLrWqM/edit?usp=sharing. this is currently set to "view if you have the link" instead of edit. The script is shared underneath.
var SPREADSHEET_ID = '1epX44htKWKhYXHw8ofF0mQ_Egqxlf3wUo-IuBN8H9Tc';
/* ------------- no changes below needed ------------- */
var spreadSheet = SpreadsheetApp.openById(SPREADSHEET_ID);
var GMAIL_LABEL = spreadSheet.getRangeByName('gmailLabel').getValue();
var SPREADSHEET_NAME = spreadSheet.getRangeByName('sheetName').getValue();
var ROWS_TO_REMOVE = spreadSheet.getRangeByName('rowsToRemove').getValue();
var sheet = spreadSheet.getSheetByName(SPREADSHEET_NAME);
/**
* Get all the starred threads within our label and process their attachments
*/
function main() {
var labels = getSubLabels(GMAIL_LABEL);
for(var i=0; i<labels.length; i++) {
var threads = getUnprocessedThreads(labels[i]);
for(j=0; j<threads.length; j++) {
processThread(threads[j], labels[i]);
}
}
}
/**
* Get all the given label and all its sub labels
*
* @param {string} name
* @return {GmailLabel[]}
*/
function getSubLabels(name) {
var labels = GmailApp.getUserLabels();
var matches = [];
for(var i=0; i<labels.length; i++){
var label = labels[i];
if(
label.getName() === name ||
label.getName().substr(0, name.length+1) === name+'/'
) {
matches.push(label);
}
}
return matches;
}
/**
* Get all starred threads in the given label
*
* @param {GmailLabel} label
* @return {GmailThread[]}
*/
function getUnprocessedThreads(label) {
var from = 0;
var perrun = 50; //maximum is 500
var threads;
var result = [];
do {
threads = label.getThreads(from, perrun);
from += perrun;
for(var i=0; i<threads.length; i++) {
if(!threads[i].hasStarredMessages()) continue;
result.push(threads[i]);
}
} while (threads.length === perrun);
Logger.log(result.length + ' threads to process in ' + label.getName());
return result;
}
/**
* @param {GmailThread} thread
* @param {GmailLabel} label where this thread was found
*/
function processThread(thread, label) {
var messages = thread.getMessages();
for(var j=0; j<messages.length; j++) {
var message = messages[j];
if(!message.isStarred()) continue;
Logger.log('processing message from '+message.getDate());
var attachments = message.getAttachments();
for(var i=0; i<attachments.length; i++) {
var attachment = attachments[i];
try {
var unZippedfile = Utilities.unzip(attachment);
var csvData = Utilities.parseCsv(unZippedfile[0].getDataAsString(), ",");
var data = csvData.splice(0, csvData.length - ROWS_TO_REMOVE);
}
catch(error) {
var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");
var data = csvData.splice(0, csvData.length - ROWS_TO_REMOVE);
}
finally {
writeToGoogleSheet(data);
}
}
message.unstar();
}
}
/**
* Get the extension of a file
*
* @param {string} name
* @return {string}
*/
function getExtension(name) {
var re = /(?:\.([^.]+))?$/;
var result = re.exec(name);
if(result && result[1]) {
return result[1].toLowerCase();
} else {
return 'unknown';
}
}
function writeToGoogleSheet(csvData) {
sheet.clearContents().clearFormats();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
I don't have any experience with Java and have no idea where to start.