CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
willyloe
willyloe

Reputation: 61

Download .xlsx file and append it in a Google Sheet

I have an URL which downloads a .xlsx file. I would like to get its content and append it to one existing sheet.

With a CSV file I wasn't having problems with ".getContentText()", however for the .xlsx is not working. So I try to get the blob of the file, unzip it and then parse its content, however this does not work either. What am I missing?

function getXLSX() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var link = "XXXX";
        var urlData = UrlFetchApp.fetch(link);
        var urlData2 = urlData.getBlob().setContentType("application/zip");
        var stringData = Utilities.unzip(urlData2);
        var parseCsv = Utilities.parseCsv(stringData, ",");
        var CSV = Utilities.parseCsv(stringData);
        sheet.getRange(sheet.getLastRow()+1,1, CSV.length, CSV[0].length).setValues(CSV);
          }

Upvotes: 0

Views: 217

Answers (1)

RemcoE33
RemcoE33

Reputation: 1620

So try this out. For this you need to enable Drive API under services. change the things you need to change:

  1. Folder ID where the excel is in (i would make a dedicated folder) - line 3
  2. Sourcesheetname - line 23
  3. Sourcerange - line 25
  4. Targetsheetname - line 29

The script:

function getExcelData() {
  
  const searchFolderId = "searchFolderId";
  const mimeType = MimeType.MICROSOFT_EXCEL;
  const filesFromFolder = DriveApp.getFolderById(searchFolderId).getFilesByType(mimeType);
  let excelId = '';

  while (filesFromFolder.hasNext()){
    const file = filesFromFolder.next();
    excelId = file.getId();
  }

  const excel = DriveApp.getFileById(excelId);
  const tempFile = Drive.Files.insert(
          {title: "TempFile", parents: [{"id": searchFolderId}]},
          excel.getBlob(),
          {convert: true}
        );
  const tempID = tempFile.getId();
  const source = SpreadsheetApp.openById(tempID);

  //The sheettab name of the excel where you want the data from
  const sourceSheet = source.getSheetByName("sheetname");
  //The range you want the data from
  const sourceValues = sourceSheet.getRange("A1:G20").getValues();
  
  const target = SpreadsheetApp.getActiveSpreadsheet();
  //The targetsheetname
  const targetSheet = target.getSheetByName("TargetSheetName");
  
  targetSheet.getRange(targetSheet.getLastRow()+1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
  
  DriveApp.getFileById(tempID).setTrashed(true);
  DriveApp.getFileById(excelId).setTrashed(true);
  
}

Upvotes: 1

Related Questions