Google Apps脚本：将电子表格保存为本地备份的ODS

问题描述

我可以用手。我的公司广泛使用Google表格，当我们失去互联网连接时，我们需要一种方法来访问文件。

我无法获得本网站上的任何示例，无法通过脚本从Google表格创建xls或ods。

我用脚本创建了一个可以从本地Google云端硬盘文件夹访问的csv备份。当与每小时触发器一起使用时，此脚本创建在最后一小时修改的任何电子表格的每张表的csv文件，将它们放在一个文件夹中，并将其压缩到专门用于备份的文件夹中。从那里我可以移动zip到我们的本地服务器。

  function backUpMaker（）{
 var backupFolderId ='< ;备份文件夹的ID>'; 
 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'）{//跳过用于在我们的许多电子表格上进行验证的隐藏表单
 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; 
} 
  

我如何修改它以保存为ods而不是csv？或者有更好的方法来保存备份？感谢您的帮助！

工作代码如下，感谢@ serge-insas的输入：

注意：要进行此操作，请输入备份文件夹的id键，然后通过注释掉其他选项来选择ods或xlsx。然后你需要为backUpMaker（）设置一个定时的每小时触发器。日志将捕获已临时移动的文件的错误。这里：

  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 +备份：+名称+。 + 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; 
} 
  

解决方案

以获取ods文件。

2个区别：urlFetch中请求的格式变为ods，返回的对象不是字符串，而是一个blob。

除此之外，我保留了你写的所有表格选择功能，它似乎也很好。 （你做得很好！）

下面是我从你的代码中复制的全部代码，并按照说明进行了更改。我的测试工作正常。

  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'）{//跳过用于在我们的许多电子表格上进行验证的隐藏表单
 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; 
} 
  

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;
}

解决方案

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;
}

这篇关于Google Apps脚本：将电子表格保存为本地备份的ODS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！