0

I have a script with google apps script that transform a excel file to google sheets and later load the data on a different google sheet. Now, the excel file its become larger and i have had problems with the code because the Drive.Files.Insert method is don't working.

Here is my code:

function correoaviso() {

var subject = "Actualización de reporte";
var body = "Ventanilla virtual";
var htmlTemplate = HtmlService.createTemplateFromFile("mailing");
htmlTemplate.cuenta = "mail"
var formattedDate = Utilities.formatDate(new Date(), "GMT-4", "dd-MM-yyyy HH:mm:ss");
htmlTemplate.fecha = formattedDate;
var htmlBody= htmlTemplate.evaluate().getContent();
var emails = "mails"//

GmailApp.sendEmail(emails, subject, body,{ htmlBody: htmlBody }); 

}

function bringData() {

//llamar consolidador y eliminar su contenido
var url = "url file";
var ss = SpreadsheetApp.openByUrl(url);
var ws = ss.getSheetByName("hoja1"); 
var rango= ws.getRange(2, 1,ws.getLastRow(), ws.getLastColumn());
   // rango.clearContent();
//transformar archivo a google sheets
var folder = DriveApp.getFolderById('folderID')
var excelFile = folder.getFilesByType(MimeType.MICROSOFT_EXCEL).next();
var fileId = excelFile.getId();
var folderId = Drive.Files.get(fileId).parents[0].id;
var blob = excelFile.getBlob();
var resource = {
  title: excelFile.getName(),
  mimeType: MimeType.GOOGLE_SHEETS,
  parents: [{id: "ID"}],
};
var options = {
    uploadType: "multipart"
};



var originFile = Drive.Files.insert(resource, blob,options);
var idOriginFile = originFile.getId()
var ssOriginFile = SpreadsheetApp.openById(idOriginFile);
var wsOriginFile = ssOriginFile.getSheetByName("Info"); 
var rangoOriginFile= wsOriginFile.getRange(2, 1,wsOriginFile.getLastRow(), 
wsOriginFile.getLastColumn());
var rangoOriginFileA1 = rangoOriginFile.getA1Notation()

var values = rangoOriginFile.getValues();
ws.getRange(rangoOriginFileA1).setValues(values);
   
var files = folder.getFilesByName(ssOriginFile.getName());
while (files.hasNext()){
var file = files.next();
folder.removeFile(file);
    }  
    
 var files = folder.getFilesByType(MimeType.MICROSOFT_EXCEL);
while (files.hasNext()){
var file = files.next();
folder.removeFile(file);
    }
correoaviso()
    
 }

When execute the function, the code send me the next error: "GoogleJsonResponseException: API call to drive.files.insert failed with error: Request Too Large"

I tried changed the option line with:"resumable" and "multipart", but either doesnt work.

What it's the problem? what thing i have to do?

Greetings!

Luis Germán Orrego Campano
  • 155
  • 12
  • 1
    Does this answer your question? [Error: 413 Request Too Large - Python Google Drive API with resumable MediaIoBaseUpload Request](https://stackoverflow.com/questions/53077734/error-413-request-too-large-python-google-drive-api-with-resumable-mediaiobas) – Linda Lawton - DaImTo Dec 28 '20 at 13:20
  • You're probably exceeding an [Apps Script Quota](https://developers.google.com/apps-script/guides/services/quotas). If the size of the file you want to convert exceeds 50MB Apps Script will not be able to process it all at once. Can you request that whomever supplies the excel file split it so that no one file exceeds 50MB? – TheAddonDepot Dec 28 '20 at 15:55

0 Answers0