OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Convert .xlsx file exceeding file size to Google Sheets

  • Thread starter Thread starter ssubr
  • Start date Start date
S

ssubr

Guest
An .xlsx file is generated by a separate team, usually around 55mb, and uploaded to a Google Drive folder. There is only one tab (which does include some references) that is relevant to me, and the end goal is to have only that tab (pasted as values) in a separate Google sheet that I can pull values from. My current plan is:

  1. Convert the .xlsx file to Google Sheet, so I can operate on it using Apps Script.
  2. Copy the one relevant tab to a static Google Sheet, which will be overwritten with the new data each week. This will be referenced to.

I've already written most of the code, which works, but I'm facing issues with the file size limit of 50mb. I have a script below which successfully converts .xlsx files to Google Sheet format (tested), but fails on this specific file since it's ~55mb.

Code:
function convertExceltoGoogleSpreadsheet2(fileName) {
  try {
    fileName = fileName || 'filename.xlsx';

    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName().replace(/\.xlsx?/, ''),
      key: fileId,
    };
    Drive.Files.insert(resource, blob, {
      convert: true,
    });
  } catch (f) {
    Logger.log(f.toString());
  }
}

Is there any workaround here, assuming I have no say in the source data process or file generation? I have thought about converting the .xlsx file to .csv to reduce the file size, but that doesn't work as I can't even touch it since it exceeds the maximum file size. Is there a way to somehow just extract one tab as values into a different sheet to bypass the file size limit? I'm sure there is a way to get around this, but can't figure it out. If I can access the file somehow within Apps Script I can do the rest.

Continue reading...
 

Latest posts

D
Replies
0
Views
1
Danish Karmally
D
Top