Google app script to fetch xlsx file from email and copy file to Google sheet

Question

I'm a beginner in using app script and I'm only copying samples at the moment. I'm hoping somebody can help me with the script below. It is meant to fetch an excel file from my Gmail and copy it to a Google sheet. I saw this script from one of the questions here, but can't make it work. Google sheet file is here: https://docs.google.com/spreadsheets/d/1rED5zphZKp7JIK9qaTpHLKBSMsSiLAvhBAXc6Tn0zqI/edit#gid=301517543

Script:

function getRawData() {
  var threads = GmailApp.search('subject:myRentokil Report Delivery: Canpac | Pest Activity');
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  var xlsxBlob = attachment[0]; // Is supposes that attachment[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 sheet = SpreadsheetApp.openById("1rED5zphZKp7JIK9qaTpHLKBSMsSiLAvhBAXc6Tn0zqI").getSheetByName("RawData");
  sheet.clearContents();
  var range = sheet.getRange(1, 1, data.length, data[0].length);range.setValues(data);

 }

Try turning on DriveAPI in the service section of the script editor. — RemcoE33
Hi it's working now, but, I had to remove the line "Drive.Files.remove(convertedSpreadsheetId);" this means that converted sheets will pile up on my drive. How can I automatically delete the temporary sheet. Error message shows: Error Document 15ntCXkDasViXnrqlTfI6hjWvAch_hXqS8a2r9ybmXRU is missing (perhaps it was deleted, or you don't have read access?) — Angelito Saliva

iansedano iansedano · Accepted Answer · 2021-02-23T10:42:59

Modified Script

function getRawData() {
    // This gets the threads
    var threads = GmailApp.search('subject:excel attachment');
    var message = threads[0].getMessages()[0]; // returns the first message from the first thread
    var attachment = message.getAttachments()[0]; // returns the first attachment
    
    var xlsxBlob = attachment[0]; // This is a blob, yes.
    
    var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
    var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];
    var data = sheet.getDataRange().getValues(); // gets values from first tab
    
    // You were overwriting some variables here, so changed to dataSheet and dataRange
    var dataSheet = SpreadsheetApp.openById("1rED5zphZKp7JIK9qaTpHLKBSMsSiLAvhBAXc6Tn0zqI").getSheetByName("RawData");
    var dataRange = dataSheet.getRange(1, 1, data.length, data[0].length);
    dataRange.setValues(data);
    
    // Moved this line to the end because you were deleting it before using the data inside it.
    // Changed to trash instead of remove.
    Drive.Files.trash(convertedSpreadsheetId); // Remove the converted file.
 }

I commented your script to make the steps clearer.
There were some variables that were initialized twice so just changed those names.
The reason for the error is that remove does not exist as a method in the Drive API v2.

https://developers.google.com/drive/api/v2/reference/files/trash

remove actually does seem to work and deletes the file, but it always returns the error. trash works as expected. If you want to skip the trash you can delete instead.

Google app script to fetch xlsx file from email and copy file to Google sheet

1 Answers

Modified Script