0

Currently running the below script to Copy an excel attachment from Gmail. It works fine when the file isn't password protected but throws the following error when the excel file is password protected.

GoogleJsonResponseException: API call to drive.files.insert failed with error: Bad Request

I can't find any functionality to enter a password. I expect this feature may not exist. Another post suggested triggering VBA to decrypt the file first.

Question:

  1. Does the password functionality exist in the drive API
  2. How can I trigger a VBA script to decrypt to file. (i'm not looking for the VBA code just the googlescript trigger)
function getExcelFile(thread) 
{
  
  var label = GmailApp.getUserLabelByName("TO PROCESS");

  if(label != null){
    var threads = label.getThreads();
    for (var i=0; i<threads.length; i++) {
       
  //Steps to get the attachement
  var thread = GmailApp.getUserLabelByName("TO PROCESS").getThreads(0,1); //pulls all threads of all emails with tag
      var messages = thread[0].getMessages(); //pulls messages in first thread
  var len = messages.length; //Gets number of messages in first thread
  var message = messages[len-1] //get first message in given thread
  var attachments = message.getAttachments(); // Get attachment 
  
  //Steps to process the attachement
  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; //Converts Excel to "Google Sheet" in google drive and gets file Id
  var filename = xlsxBlob.getName(); //gets the converted files file name
  var tabName = filename.substring(13).slice(0,filename.length-18); // process the filename string into just the date to be Tab Name
  
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // Location of converted Excel file -> now google sheet
  var destination = SpreadsheetApp.openById("1e_pepsold-SHbnDXcQ0pvdN9AGiZ31O5XcLFU8YfcwE"); //Location of Weekly schedule
  sheet.copyTo(destination); //Copy the converted sheet into the working Weekly schedule sheet
LANCE
  • 111
  • 11
  • Maybe that was the reason for the password. If you know the password then you can remove – Cooper Jan 12 '21 at 17:07
  • @Cooper - I'm sorry I don't understand what you mean by "maybe that was the reason for the password." The excel file is password protected because it contains personal information in it. I do have the password. I want to enter the password with a script and copy the data to my google drive. Basically I get this attachment sent to me each week and I want to copy the contents to a google sheet in my google drive so I can update my calendar and view a single sheet that combines each week. I can do this manually but want to automate the process. – LANCE Jan 12 '21 at 18:41

0 Answers0