使用Apps脚本以Excel格式将Google表格导出到Google云端硬盘 [英] Export a Google Sheet to Google Drive in Excel format with Apps Script

问题描述

我想将我的Google Spreadsheet备份创建到我的Google Drive文件夹中，但是作为Excel文件. 我设法创建了一个代码，该代码创建gsheet的副本并将其保存到文件夹中，但是我无法更改代码以将其另存为Excel文件.

I would like to create a backup of my Google Spreadsheet into my Google Drive folder, but as an Excel file. I managed to create a code that create a copy of the gsheet and save it into the folder, but I could not change the code to save it as an Excel file.

您能帮我吗?

function makeCopy() {
  var formattedDate = Utilities.formatDate(new Date(), "CET", "yyyy-MM-dd' 'HH:mm");
  var name = "Backup Copy " + formattedDate;
  var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");
  var file = DriveApp.getFileById("2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c")
  file.makeCopy(name, destination);
}

推荐答案

这个答案怎么样?我认为您的情况有几个答案.因此，请将此视为其中之一.

How about this answer? I think that there are several answers for your situation. So please think of this as one of them.

为了将电子表格转换为excel，可以使用https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx的端点.在此答案中，使用了它.

In order to convert spreadsheet to excel, the endpoint of https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx can be used. In this answer, this was used.

function makeCopy() {
  var formattedDate = Utilities.formatDate(new Date(), "CET", "yyyy-MM-dd' 'HH:mm");
  var name = "Backup Copy " + formattedDate;
  var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");

  // Added
  var sheetId = "2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c";
  var url = "https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); // Modified
  destination.createFile(blob);
}

如果我误解了你的问题，对不起.

If I misunderstand your question, I'm sorry.

从2020年1月开始，访问令牌不能与access_token=###之类的查询参数一起使用.

From January, 2020, the access token cannot be used with the query parameter like access_token=###. Ref So please use the access token to the request header instead of the query parameter. It's as follows.

var res = UrlFetchApp.fetch(url, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});

这篇关于使用Apps脚本以Excel格式将Google表格导出到Google云端硬盘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！