3

I receive an XLSX file from our client on regular basis, and I would like to automate the process of importing it from Gmail (it's automatically labeled) into Google Sheets. So far I managed to make it work for CSV files, but XLSX files seem to be trickier. Can someone help to adjust this code I have for CSV files?

function getCSV() 
{
  var thread = GmailApp.getUserLabelByName(‘Reconciliation’).getThreads(0,1);
  var messages = thread[0].getMessages();
  var len = messages.length;
  var message=messages[len-1] //get last message
  var attachments = message.getAttachments(); // Get attachment of first message

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

  var sheet = SpreadsheetApp.openById("some id").getSheetByName(‘Data’);
  sheet.clearContents();
  var range = sheet.getRange(1, 1, data.length, data[0].length);
  range.setValues(data);
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Banker
  • 63
  • 1
  • 6
  • Apps Script does not have any built-in methods for reading Excel files. You will need to convert the Excel attachments into Drive files, using the Drive API, with the `convert` option set to "true". Then you will need to access the converted Google Sheets file and use standard `SpreadsheetApp` functionality (or the Sheets API) to obtain the data you want. – tehhowch Apr 03 '19 at 14:17

1 Answers1

5
  • You want to put the data from xlsx file attached to an email to the existing Spreadsheet.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

When you use this script, please enable Drive API at Advanced Google Services and API console. You can see about this at here.

Flow of modified script:

  1. Retrieve a blob of xlsx file.
  2. Convert xlsx format to Google Spreadsheet.
  3. Retrieve values from the converted Spreadsheet.
  4. Remove the converted file.
  5. Put the values to the sheet of Data in the existing Spreadsheet.

Modified script:

Please modify as follows.

From: 
var csv =  attachments[0].getDataAsString();
var data = Utilities.parseCsv(csv);
To: 
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.

Note:

  • In this modification, it supposes the following points. If your situation is different from the following points, please modify it.
    1. attachments[0] is the blob of xlsx file.
    2. About the xlsx file, the data you want to put is in a 1st tab.

Reference:

If I misunderstood your question and this didn't work, I apologzize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165