使用GAS将Google电子表格转换为XLSX或ODS [英] Converting google spreadsheet to XLSX or ODS using GAS
本文介绍了使用GAS将Google电子表格转换为XLSX或ODS的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想将几个Google电子表格转换为Excel（首选xlsx）。
我已经阅读了几个关于如何实现这个目标的线程，但是我无法运行它。
我读过的线程中有 Google Apps脚本：将电子表格另存为用于本地备份的ODS 和谷歌应用程序脚本，以电子邮件谷歌电子表格Excel版本
为了保持接近我找到的代码，并得到更多的信息，为什么它不工作，我修改了我发现的代码，以便它只处理几个文件，并且try-catch已被删除。
** ****************不工作代码**************
函数contentODS（key，gid）
{
var file = DocsList.getFileById（key）;
var fetchParameters = oAuth（）;
var url =https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=+ key +& gid =+ gid +& exportFormat =消耗臭氧层物质;
var response = UrlFetchApp.fetch（url，fetchParameters）;
var fileBlob = response.getBlob（）;
返回fileBlob;
}
函数oAuth（）
{// https://stackoverflow.com/questions/24493203/google-apps-script-save-spreadsheet-as-ods- for-local-backup
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=+范围）;
oauthConfig.setAuthorizationUrl（https://accounts.google.com/OAuthAuthorizeToken）;
oauthConfig.setAccessTokenUrl（https://www.google.com/accounts/OAuthGetAccessToken）;
$ b var requestData =
{
oAuthServiceName：spreadsheets，
oAuthUseToken：always，
muteHttpExceptions：true，
method：DELETE，
};
返回requestData;
函数eachSheet（key，newFolderId）
{
var ss = SpreadsheetApp.openById（key）;
var ssId = ss.getId（）;
var ssName = ss.getName（）;
var howManySheets = ss.getNumSheets（）; （var sheetIndex = 0; sheetIndex< howManySheets; sheetIndex ++）
{
var activeSheet = ss.getSheets（）[sheetIndex];
。
var activeName = activeSheet.getName（）;
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）;
函数backUpMaker（）
{
var backupDirectory ='Backup';
var folders = DriveApp.getRootFolder（）。getFoldersByName（backupDirectory）;
var backupFolderId = undefined;
if（folders.hasNext（））backupFolderId = folders.next（）。getId（）;
else return; //文件doe不存在
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 name = sheet.getName（）;
var filesToSave = ['My-Agenda'，'My-Kalender'];
var numFiles = filesToSave.length;
for（var i = 0; i< numFiles; i ++）
{
if（name == filesToSave [i]）
{
var sheetId = sheet.getId（）;
var csv = eachSheet（sheetId，newFolderId）;
休息;
}
}
}
var backupFolder = DocsList.getFolderById（backupFolderId）;
var newFiles = newFolder.getFiles（）;
var numFiles = newFiles.length;
if（numFiles> 0）
{
var nameZipFile = timeNow +'.zip';
backupFolder.createFile（Utilities.zip（newFiles，nameZipFile））;
}
newFolder.setTrashed（true）;
在 var response = UrlFetchApp.fetch时运行backupmaker失败（url，fetchParameters）;
并指示验证服务电子表格失败
我应该如何使它工作。 您可以使用高级云端硬盘服务以获取导出URL并使用脚本的OAuth2令牌下载文件。
函数exportAsExcel（spreadsheetId）{
var file = Drive.Files.get（spreadsheetId）;
var url = file.exportLinks ['application / vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
var token = ScriptApp.getOAuthToken（）;
var response = UrlFetchApp.fetch（url，{
headers：{
'Authorization'：'Bearer'+ token
}
}）;
return response.getBlob（）;
}
函数test（）{
var spreadsheetId ='SPREADSHEET ID HERE';
DriveApp.createFile（exportAsExcel（spreadsheetId））;
}
I want to convert a few google spreadsheets to Excel (xlsx preferred).
I've read several threads about how to achieve this, but I can't get it running.
Amongst the threads I've read are Google Apps Script: Save Spreadsheet as ODS for Local Backup and Google apps script to email google spreadsheet excel version
In order to stay close to code I've found AND get more info about why it doesn't work, I modified code I found such that it just handles a few files and also try-catch has been removed.
****************** Not working code **************
function contentODS(key, gid)
{
var file = DocsList.getFileById(key);
var fetchParameters = oAuth();
var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + key + "&gid=" + gid + "&exportFormat=ods";
var response = UrlFetchApp.fetch(url, fetchParameters);
var fileBlob = response.getBlob();
return fileBlob;
}
function oAuth()
{ // https://stackoverflow.com/questions/24493203/google-apps-script-save-spreadsheet-as-ods-for-local-backup
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",
"muteHttpExceptions": true,
"method": "DELETE",
};
return requestData;
}
function eachSheet(key, newFolderId)
{
var ss = SpreadsheetApp.openById(key);
var ssId = ss.getId();
var ssName = ss.getName();
var howManySheets = ss.getNumSheets();
for (var sheetIndex=0; sheetIndex < howManySheets; sheetIndex++)
{
var activeSheet = ss.getSheets()[sheetIndex];
var activeName = activeSheet.getName();
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);
}
}
function backUpMaker()
{
var backupDirectory = 'Backup';
var folders = DriveApp.getRootFolder().getFoldersByName(backupDirectory);
var backupFolderId = undefined;
if (folders.hasNext()) backupFolderId = folders.next().getId();
else return; // File doe not exist
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 name = sheet.getName();
var filesToSave = [ 'My-Agenda', 'My-Kalender' ];
var numFiles = filesToSave.length;
for (var i=0; i<numFiles; i++)
{
if (name == filesToSave[i])
{
var sheetId = sheet.getId();
var csv = eachSheet(sheetId, newFolderId);
break;
}
}
}
var backupFolder = DocsList.getFolderById(backupFolderId);
var newFiles = newFolder.getFiles();
var numFiles = newFiles.length;
if (numFiles > 0)
{
var nameZipFile = timeNow + '.zip';
backupFolder.createFile(Utilities.zip(newFiles, nameZipFile));
}
newFolder.setTrashed(true);
}
Running backupmaker fails at var response = UrlFetchApp.fetch(url, fetchParameters);
and indicates verification for service spreadsheets failed
Waht should I do to make it work.
解决方案
You can use the Advanced Drive Service to get the export URL and use the script's OAuth2 token to download the file.
function exportAsExcel(spreadsheetId) {
var file = Drive.Files.get(spreadsheetId);
var url = file.exportLinks['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
return response.getBlob();
}
function test() {
var spreadsheetId = 'SPREADSHEET ID HERE';
DriveApp.createFile(exportAsExcel(spreadsheetId));
}
这篇关于使用GAS将Google电子表格转换为XLSX或ODS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
