CODEWITHSUNDEEP
excelgoogle-sheets-api
jules c
jules c

Reputation: 11

Exporting a Google Sheet into an Excel using Google script

I am trying to automate a backup of a google sheet into an excel.

After trying different scripts seen here and there on Stackoverflow, the one I have is now running but nothing is happening.

Any ideas?

Here is the code:

function exportAsxlsx() {
  var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()
  var file = DriveApp.getFileById(spreadsheetId)

  var url           = 'https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/export?format=xlsx';
  var token         = ScriptApp.getOAuthToken();
  var response      = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var blobs   = response.getBlob();
  var folder = DriveApp.getFoldersByName('Exports');
  if(folder.hasNext()) {
    var existingPlan1 = DriveApp.getFilesByName('newfile.xlsx');
    if(existingPlan1.hasNext()){
      var existingPlan2 = existingPlan1.next();
      var existingPlanID = existingPlan2.getId();
      Drive.Files.remove(existingPlanID);
    }
  } else {
    folder = DriveApp.createFolder('BackUp');
  }
  folder = DriveApp.getFoldersByName('BackUp').next();
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Copy " + formattedDate;
  folder.createFile(blobs).setName(name + '.xlsx')
}

Upvotes: 0

Views: 281

Answers (1)

jules c
jules c

Reputation: 11

So, here is a script that works:

function exportAsxlsx() {
  var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()
  var file = DriveApp.getFileById(spreadsheetId)

  var url           = 'https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/export?format=xlsx';
  var token         = ScriptApp.getOAuthToken();
  var response      = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var blobs   = response.getBlob();
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Copy " + formattedDate;
  var folder = DriveApp.getFolderById("your folder ID");
  folder.createFile(blobs).setName(name + '.xlsx');
}

Upvotes: 1

Related Questions