我想将一些谷歌电子表格转换为Excel(首选xlsx)。
我已经阅读了几个有关如何实现此目标的线程,但我无法运行它。
我读过的主题包括Google Apps 脚本:将电子表格保存为 ODS 以进行本地备份 and Google Apps 脚本通过电子邮件发送 Google 电子表格 Excel 版本
为了接近我发现的代码并获取有关其不起作用的更多信息,我修改了我发现的代码,使其仅处理几个文件,并且 try-catch 也已被删除。
****************** 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);
}
运行 backupmaker 失败var response = UrlFetchApp.fetch(url, fetchParameters);
并指示服务电子表格验证失败
我应该做什么才能让它发挥作用。
您可以使用高级驾驶服务获取导出 URL 并使用脚本的 OAuth2 令牌下载文件。
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));
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)