Automated Download of a Google Sheet

by

I would like to somehow automate the export of a Gsheet file that I have in my drive, as an .xlsx file and place it in the same folder as the original Gsheet.

I tried making some App Scripts, but nothing has worked perfectly so far.

function downloadGoogleSheet() {
var spreadsheet = SpreadsheetApp.openById('[SHEET_ID]');    
var sheet = spreadsheet.getSheetByName('Sheet1');   

// Get the data from the sheet
var data = sheet.getDataRange().getValues();

var folderId = '[FOLDER_ID]'; 


// Check if a file with the same name already exists and delete it
var existingFile = 
DriveApp.getFolderById(folderId).getFilesByName(fileName);
while (existingFile.hasNext()) {
var file = existingFile.next();
file.setTrashed(true); // Delete the existing file
}

// Create a new Excel file in Google Drive
var excelBlob = Utilities.newBlob(data.join('\n'), 'application/vnd.openxmlformats-   officedocument.spreadsheetml.sheet', 
fileName);
DriveApp.getFolderById(folderId).createFile(excelBlob);
}

The Script above manages to make the new .xlsx file, but the issue is that both the name of the file and the name of the sheet in the xlsx output file has the .xlsx extension. That makes Microsoft Excel in my desktop unable to open it. But if I manually remove the .xlsx part from the sheet name the problem is fixed. So if someone alter the above script to change the sheet name of the output excel file, or have any other ideas, I would appreciate it. Thank you!

  • So I guess you’re saying that fileName is something like budgetReport.xlsx so then filename.slice(0,-5) will just be budgetReport. Will that work? I’m guessing you would use that in the second to last line of your code.

    – 




Modification points:

  • fileName is not declared.
  • I think that in the current stage, unfortunately, the XLSX data cannot be created by var excelBlob = Utilities.newBlob(data.join('\n'), 'application/vnd.openxmlformats- officedocument.spreadsheetml.sheet',fileName);

When these points are reflected in your script, how about the following modification?

Modified script:

Please set fileName, spreadsheetId, and folderId.

function downloadGoogleSheet() {
  var fileName = "sample.xlsx"; // Please set your filename.
  var spreadsheetId = "[SHEET_ID]"; // Please set your spreadsheet ID.

  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var parentFolder = DriveApp.getFileById(spreadsheetId).getParents().next();
  var existingFile = parentFolder.getFilesByName(fileName);
  while (existingFile.hasNext()) {
    var file = existingFile.next();
    file.setTrashed(true);
  }
  var url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?format=xlsx&gid=${sheet.getSheetId()}`;
  var excelBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob().setName(fileName);
  parentFolder.createFile(excelBlob);
}

  • When this script is run, a new XLSX file including “Sheet1” is created to the same folder of the Spreadsheet of spreadsheetId. When the same filename is existing, the file is removed and a new XLSX file is created.

  • If you want to use the value of fileName of the Spreadsheet, how about the following modification?

    function downloadGoogleSheet() {
  var spreadsheetId = "[SHEET_ID]"; // Please set your spreadsheet ID.

  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var fileName = spreadsheet.getName();
  if (!(/\.xlsx$/i).test(fileName)) {
    fileName += ".xlsx";
  }
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var parentFolder = DriveApp.getFileById(spreadsheetId).getParents().next();
  var existingFile = parentFolder.getFilesByName(fileName);
  while (existingFile.hasNext()) {
    var file = existingFile.next();
    file.setTrashed(true);
  }
  var url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?format=xlsx&gid=${sheet.getSheetId()}`;
  var excelBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob().setName(fileName);
  parentFolder.createFile(excelBlob);
}

Note:

References:

Added:

About your following reply,

The Answer works almost perfectly. The only issue is that the Gsheet contains a formula at the first row of each column, since it sources data from another Gsheet. The xlsx file created have the formulas of the first row and not the data. Can we extract the data only?

In this case, how about the following sample script?

Sample script:

function downloadGoogleSheet() {
  var spreadsheetId = "[SHEET_ID]"; // Please set your spreadsheet ID.

  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var fileName = spreadsheet.getName();
  if (!(/\.xlsx$/i).test(fileName)) {
    fileName += ".xlsx";
  }
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var parentFolder = DriveApp.getFileById(spreadsheetId).getParents().next();
  var existingFile = parentFolder.getFilesByName(fileName);
  while (existingFile.hasNext()) {
    var file = existingFile.next();
    file.setTrashed(true);
  }
  var tempSheet = sheet.copyTo(spreadsheet);
  var tempRange = tempSheet.getDataRange();
  tempRange.copyTo(tempRange, { contentsOnly: true });
  SpreadsheetApp.flush();
  var url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?format=xlsx&gid=${tempSheet.getSheetId()}`;
  var excelBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob().setName(fileName);
  parentFolder.createFile(excelBlob);
  spreadsheet.deleteSheet(tempSheet);
}

Leave a Comment