CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsexport-to-excel
abbas alyasere
abbas alyasere

Reputation: 39

How to "Schedule Automatic Backups" of your Google Sheets as Excel

How to "Schedule Automatic Backups" of your Google Sheets as Excel in specific folder in google drive I have a code to save a backup It was prepared with Google Sheet code editor But it saves the file as Google Sheet, but it needs to be saved as Excel I found this code https://gist.github.com/abhijeetchopra/99a11fb6016a70287112 But I need an excel backup

Upvotes: 1

Views: 1591

Answers (1)

NightEye
NightEye

Reputation: 11204

Modifying the reference you provided, try to export the file to xlsx and then have it created by using its blob into the desired directory.

Script:

function makeCopy() {
  // generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");

  // gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate
  var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Copy " + formattedDate;

  // gets the destination folder by their ID. REPLACE xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx with your folder's ID that you can get by opening the folder in Google Drive and checking the URL in the browser's address bar
  var destination = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");

  var ss = SpreadsheetApp.getActive();

  // get xlsx export link 
  var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";

  var params = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };

  var blob = UrlFetchApp.fetch(url, params).getBlob();

  blob.setName(name + ".xlsx");
  destination.createFile(blob);
}

Output:

output

Note:

  • The reference you included already have the steps to do the scheduling of automatic backups so that should suffice for the automation.

Upvotes: 4

Related Questions