excel - Google Apps Script: Save Spreadsheet as ODS for Local Backup

Question

I could use a hand. My company uses Google Sheets extensively, and we need a way to access files when we lose our Internet connection.

I could not get any of the examples found on this site to work for creating xls or ods from Google Sheets via script.

I did script a way to create csv backups, accessible from a local Google Drive folder. When used with an hourly trigger, this script creates csv files of every sheet of any spreadsheet modified in the last hour, puts them in a folder, and zips it inside a folder specifically for backups. From there I can move the zip to our local server.

function backUpMaker() {
  var backupFolderId = '<Id of Backup Folder>';
  var timeNow = new Date();  
  var newFolder = DocsList.createFolder(timeNow);
  var newFolderId =newFolder.getId();
  newFolder.addToFolder(DocsList.getFolderById(backupFolderId));
  newFolder.removeFromFolder(DocsList.getRootFolder());
  var sheets = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (sheets.hasNext()) {
    var sheet = sheets.next();
    var lastUpdate = sheet.getLastUpdated();
    var timeHourAgo = new Date();
    timeHourAgo = timeNow - 3600000;
    if(lastUpdate >= timeHourAgo){
      var sheetId = sheet.getId();
      var csv = eachSheet(sheetId,newFolderId); 
    }
  }
  var backupFolder = DocsList.getFolderById(backupFolderId);
  try{
    backupFolder.createFile(Utilities.zip(newFolder.getFiles(), timeNow + '.zip'));
  } catch(err) {
    Logger.log(err);
  }
  newFolder.setTrashed(true);
}

function eachSheet(key,newFolderId) {
  var ss = SpreadsheetApp.openById(key);
  var ssId = ss.getId();
  var ssName = ss.getName();
  var howManySheets = ss.getNumSheets();
  try{
   for (var sheetIndex=0; sheetIndex < howManySheets; sheetIndex++) {   
     var activeSheet = ss.getSheets()[sheetIndex];
     var activeName = activeSheet.getName();
     if(activeName != 'Dropdowns'){     //Skip a hidden sheet used for validation on many of our spreadsheets
      var activeId = activeSheet.getSheetId();
      var time = new Date();
      var fileName = time + " Backup: " + ssName + " " + activeName + ".csv";
      var csv = contentCSV(ssId,activeId);
      var folder = DocsList.getFolderById(newFolderId);  
      folder.createFile(fileName, csv, 'text/plain');
     }
   } 
  } catch(err) {
    Logger.log(err)
  }
}

function contentCSV(key,gid) {
  var file = DocsList.getFileById(key);  
  var response =     UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + key + 
                "&gid=" + gid + "&exportFormat=csv", oAuth());
  var fileText = response.getContentText();  
  return fileText;
}

function oAuth() {
  var oauthConfig = UrlFetchApp.addOAuthService("spreadsheets");
  var scope = "https://spreadsheets.google.com/feeds" 
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");    
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");   
 var requestData = {
    "oAuthServiceName": "spreadsheets",
    "oAuthUseToken": "always",
  };
  return requestData;
}

How would I modify this to save as ods rather than csv? Or is there a better way to save backups? Thank you for your help!

Working code follows, thanks to input by @serge-insas :

Note: To make this work, enter your backup folder's id key, then choose ods or xlsx by commenting out the other choice. Then you need to set up a timed hourly trigger for backUpMaker(). Logs will catch errors for files that have been moved temporarily. Here goes:

function backUpMaker() {
  var backupFolderId = '0B5--------------------1ZX1k';
  //var exportFormat = 'ods';
  var exportFormat = 'xlsx';
  var timeNow = new Date();  
  var newFolder = DocsList.createFolder(timeNow);
  var newFolderId =newFolder.getId();
  newFolder.addToFolder(DocsList.getFolderById(backupFolderId));
  newFolder.removeFromFolder(DocsList.getRootFolder());
  var sheets = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (sheets.hasNext()) {
    var sheet = sheets.next();
    var lastUpdate = sheet.getLastUpdated();
    var timeHourAgo = new Date();
    timeHourAgo = timeNow - 3600000;
    if(lastUpdate >= timeHourAgo){
      try{
      var key = sheet.getId();
      var name = sheet.getName();
      var fileName = timeNow + " Backup: " + name + "." + exportFormat;
      var blob = contentBackup(key,exportFormat);
      var folder = DocsList.getFolderById(newFolderId);  
      folder.createFile(blob).rename(fileName);
      } catch(err){
        Logger.log(err);
      }
    }
  }
  var backupFolder = DocsList.getFolderById(backupFolderId);
  try{
    backupFolder.createFile(Utilities.zip(newFolder.getFiles(), timeNow + '.zip'));
  } catch(err) {
    Logger.log(err);
  }
  newFolder.setTrashed(true);
}

function contentBackup(key,exportFormat) {
  try{
    if(exportFormat == 'xlsx'){
      exportFormat = 'xls';
    }  
    var file = DocsList.getFileById(key);  
    var response =     UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + key + 
                  "&exportFormat=" + exportFormat, oAuth());
    var fileBlob = response.getBlob();  
    return fileBlob;
    } catch(err) {
    Logger.log(err)
    }
}

function oAuth() {
  var oauthConfig = UrlFetchApp.addOAuthService("spreadsheets");
  var scope = "https://spreadsheets.google.com/feeds" 
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");    
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");   
 var requestData = {
    "oAuthServiceName": "spreadsheets",
    "oAuthUseToken": "always",
  };
  return requestData;
}

Answer 1

Your code needed very few changes to get ods files.

2 differences : the requested format in the urlFetch becomes "ods" and the returned object is not a string anymore but a blob.

Beside that, I kept all the sheet selection functions that you wrote and it seems to work fine as well. (nice job you did !).

Below is the full code I reproduced from yours and changed as described. My tests were working fine.

function backUpMaker() {
  var backupFolderId = '0B3qS--------------TXdlY2M';
  var timeNow = new Date();  
  var newFolder = DocsList.createFolder(timeNow);
  var newFolderId =newFolder.getId();
  newFolder.addToFolder(DocsList.getFolderById(backupFolderId));
  newFolder.removeFromFolder(DocsList.getRootFolder());
  var sheets = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (sheets.hasNext()) {
    var sheet = sheets.next();
    var lastUpdate = sheet.getLastUpdated();
    var timeHourAgo = new Date();
    timeHourAgo = timeNow - 3600000;
    if(lastUpdate >= timeHourAgo){
      var sheetId = sheet.getId();
      var csv = eachSheet(sheetId,newFolderId); 
    }
  }
  var backupFolder = DocsList.getFolderById(backupFolderId);
  try{
    backupFolder.createFile(Utilities.zip(newFolder.getFiles(), timeNow + '.zip'));
  } catch(err) {
    Logger.log(err);
  }
  newFolder.setTrashed(true);
}

function eachSheet(key,newFolderId) {
  var ss = SpreadsheetApp.openById(key);
  var ssId = ss.getId();
  var ssName = ss.getName();
  var howManySheets = ss.getNumSheets();
  try{
   for (var sheetIndex=0; sheetIndex < howManySheets; sheetIndex++) {   
     var activeSheet = ss.getSheets()[sheetIndex];
     var activeName = activeSheet.getName();
     if(activeName != 'Dropdowns'){     //Skip a hidden sheet used for validation on many of our spreadsheets
      var activeId = activeSheet.getSheetId();
      var time = new Date();
      var fileName = time + " Backup: " + ssName + " " + activeName + ".ods";
      var blob = contentODS(ssId,activeId);
      var folder = DocsList.getFolderById(newFolderId);  
      folder.createFile(blob).rename(fileName);
     }
   } 
  } catch(err) {
    Logger.log(err)
  }
}

function contentODS(key,gid) {
  var file = DocsList.getFileById(key);  
  var response =     UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + key + 
                "&gid=" + gid + "&exportFormat=ods", oAuth());
  var fileBlob = response.getBlob();  
  return fileBlob;
}

function oAuth() {
  var oauthConfig = UrlFetchApp.addOAuthService("spreadsheets");
  var scope = "https://spreadsheets.google.com/feeds" 
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");    
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");   
 var requestData = {
    "oAuthServiceName": "spreadsheets",
    "oAuthUseToken": "always",
  };
  return requestData;
}