Need a Script to pull specific columns from an excel report that I receive to Gmail multiple times a day

[Rezwana Islam]

Multiple times I day I receive automated reports of HR data to my Gmail from Oracle Fusion. I do not have access to Oracle Fusion. I would like to automatically pull a few columns containing relevant data to compile a .csv report. How can I do that?

[Enrique Barrueto]

Show me an example to see what kind of data you have...

El mar, 15 feb 2022 a la(s) 14:53, Rezwana Islam (rezwan...@hbc.com) escribió:

Multiple times I day I receive automated reports of HR data to my Gmail from Oracle Fusion. I do not have access to Oracle Fusion. I would like to automatically pull a few columns containing relevant data to compile a .csv report. How can I do that?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/db383ac2-10fa-404c-a084-83665c06c0aan%40googlegroups.com.

[Rezwana Islam]

This is a sample of the type of data:

[Edward Ulle]

Is it an attachment or embedded in the email?  And if an attachment what type?

[Rezwana Islam]

It is attached as an .xlsx file. 

I only need the data in the 'person number' and 'name' columns from the file. 

[Edward Ulle]

Here is an example of converting an xlsx attachment to Google sheet https://stackoverflow.com/questions/55494934/importing-xlsx-file-from-the-monthly-e-mail-in-gmail-to-the-designated-google

[Martin Molloy]

You can use the add on "Save Emails and Attachments" to automate the process of taking attachments from an email and saving the attachment to your Google Drive. You can set it up to run automatically on receipt of the email.

This is the link to install it - https://workspace.google.com/marketplace/app/save_emails_and_attachments/513239564707

It's created by Digital Inspiration and costs $99/year. It's free to try. I have no connection with the developers, I just use it and find it very useful.

You can then use my add-on SheetsIE to automate the process of importing those saved files into a google sheet and outputting a sheet or CSV file. You can set it up to run every hour. 

It costs $144 a year. This is the link if you want to install it - https://workspace.google.com/marketplace/app/sheetsie/369803151024

Again it's free to try.

Let me know if you have any questions.

Martin 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/07ddb4d4-6c79-4287-b013-851b77963bc7n%40googlegroups.com.

[Edward Ulle]

I can't guarantee this will work.  It works for my test case but I don't have an Excel file that matches your specifics.

function onOpen() {

  var menu = SpreadsheetApp.getUi().createMenu("File Picker");

  menu.addItem("Get XLSX","getXLSX");

  menu.addToUi();

}

function getXLSX() {

  try {

    var threads = GmailApp.getInboxThreads();

    // you will need to change the subject "XLSX Test" to your subject

    var thread = threads.find( email => email.getFirstMessageSubject() == "XLSX Test" );

    var messages = thread.getMessages();

    // assuming the attachement is attached to message 1

    var attachments = messages[0].getAttachments();

    var xlsxBlob = attachments[0]; // Is supposes that attachments[0] is the blob of xlsx file.

    var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;

    var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // There is the data in 1st tab.

    var data = sheet.getDataRange().getValues();

    Drive.Files.remove(convertedSpreadsheetId); // Remove the converted file.

    var spread = SpreadsheetApp.getActiveSpreadsheet();

    // you will need to change Sheet2 to your sheet name

    var sheet = spread.getSheetByName("Sheet2");

    // I can't really test this part because I don't use Excel and have no test file with more than 1 column

    function extractColumns(row) {

      row = row.slice(3);   // remove columns D and up

      row = row.slice(1,1); // remove column B

      return row;

    }

    data.forEach( extractColumns );

    console.log(data);

    sheet.getRange(1,1,data.length,data[0].length).setValues(data);

  }

  catch(err) {

    console.log(err);

  }

}

[Edward Ulle]

Oops the part I couldn't test, well I tested it with a simple case.  Please replace the with the following.

    function extractColumns(row) {

      row = row.slice(0,3);   // remove columns D and up

      row.splice(1,1); // remove column B

      return row;

    }

    data.map( extractColumns );

[Ed Sambuco]

You might find this recent post to Stackoverflow interesting:

https://stackoverflow.com/questions/71031024/error-i-want-to-convert-the-file-from-xlsx-to-csv-using-google-script

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2Ph%3DNagw6zP_A41ZC3ggxU9ZuVMK-_w_TYjHDLvxbnvdXw%40mail.gmail.com.