0

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.

ross
  • 2,684
  • 2
  • 13
  • 22
Aki
  • 137
  • 1
  • 4
  • 17
  • 1
    This is not Java, it's Google Apps Script which is based on JavaScript (also, not Java). – ross Jul 24 '19 at 15:19
  • 1
    I apologize for my poor English skill. Can I ask you about your question? In your question, `excel files` and `CSV files` are written. The process for CSV files works fine. You want to do the same process for Excel (xlsx file?) files. Is my understanding correct? By the way, I couldn't open your shared Spreadsheet. – Tanaike Jul 25 '19 at 00:02
  • @Tanaike you are absolutely right, the CSV function works fine, but I want to be able to use this script for excel files. I've also changed the sharing option. Thanks! – Aki Jul 25 '19 at 09:15
  • thanks @ross, shows how much help I needed! – Aki Jul 25 '19 at 09:16
  • 2
    Thank you for replying and updating the sharead sample. I could open it. From your replying, I found that you want to use XLSX file instead of CSV file for the attachment file. 1. In this case, is the XLSX file always zipped? 2. In your case, if there are several attachment files, the Spreadsheet has the values of the last attachment file. How about this? 3. Do you want to put all values of XLSX file to the existing Spreadsheet? 4. At that time, you use `sheet.clearContents().clearFormats()`. So how about overwriting the Spreadsheet by the XLSX file? – Tanaike Jul 25 '19 at 23:02
  • 1.-no, it should come in a xlsx format. I would like for the script to work for zipped xslx files though 2.Only one file should be attached for each email, so that shouldn’t be a problem 3. yes 4. . So how about overwriting the Spreadsheet by the XLSX file? Overwriting the gsheet is fine, so long as the data in the xlsx file ends up in the gsheet – Aki Jul 26 '19 at 16:02
  • 3
    Have you read [Importing XLSX file from the monthly e-mail (in Gmail) to the designated Google Sheet](https://stackoverflow.com/a/55505658/1330560)? It seems to be exactly what you are trying to do, and @Tanaike gave an answer that the OP said "...worked perfectly!" – Tedinoz Jul 27 '19 at 08:47
  • @Tedinoz no I haven't! that is fantastic, thank you!! – Aki Jul 27 '19 at 09:33
  • 1
    I'm glad your issue was resolved. – Tanaike Jul 27 '19 at 23:20

0 Answers0