Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
841 views
in Technique[技术] by (71.8m points)

google apps script - GoogleJsonResponseException: API call to drive.files.insert failed with error: Bad Request

I'm trying to trigger a script to copy an attached excel file from a Gmail Email and copy it into a google sheets sheet. Any email with the tag "TO PROCESS" should be copied. I'm getting this error message:

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

This is error is occuring at the line:

var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id; //Converts Excel to "Google Sheet" in google drive and gets file Id

Full code below:

function emailTrigger() {

  var label = GmailApp.getUserLabelByName("TO PROCESS");

  if(label != null){
    var threads = label.getThreads();
    for (var i=0; i<threads.length; i++) {
      getExcelFile(threads[i]);
      //Process them in the order received
      threads[i].removeLabel(label);
    }
  }
}

function getExcelFile(thread) 
{
//Function to pull Weekly Schedule Excel file from Janettas email and update phone spreadsheet and calendar
  
  var messages = thread.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 of first message
  
  //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
  }

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The error was occurring because the specific excel spreadsheet was encrypted. There were no issues with the code and it can be used as intended.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...