自动“下载”电子表格作为.xlsx发送到本地计算机事件 [英] Download "automatically" spreadsheet as .xlsx to local machine on event

问题描述

请参阅下面的功能，将有效的Google Spreadsheet转换为.xlsx文件。

  function downloadAsXlsx（）{
 
 var spreadSheet = SpreadsheetApp .getActiveSpreadsheet（）; 
 var ssID = spreadSheet.getId（）; 
 
 Logger.log（ssID）; 
 
 var url =https://docs.google.com/spreadsheets/d/\"+ssID+\"/export?format=xlsx; 
 var params = {method：GET，headers：{authorization：Bearer+ ScriptApp.getOAuthToken（）}}; 
 var response = UrlFetchApp.fetch（url，params）; 
 
 //保存到驱动器
 DriveApp.createFile（response）; 
 
 
 
 
 
 
 
如果您将上述URL中的ssID替换为实际值活动Google Spreadsheet的文件ID并将该URL复制并粘贴到浏览器中，活动电子表格会自动下载。这正是我需要添加到上面的脚本。
 
 
 
我的问题是如何更改和/或扩展上面的函数，以便文件被下载到本地机器在默认的下载文件夹中？
解决方案
 Google Spreadsheet 版Google云端硬盘。如果您将Google云端硬盘同步到您的电脑上，那么您最终还是会在电脑上看到一份副本......但这不是您要追踪的内容。您需要一种方法来触发Google工作表的HTTP下载，该工具使用系统对话框让您选择PC上的哪个位置来保存下载的文件。
 
 
 
要完成此操作，我们无法使用服务器端Google Apps脚本技术，因为服务器无法访问您的计算机资源。相反，我们需要利用浏览器。由于您有兴趣从有效的电子表格中完成此操作，因此使用自定义菜单项扩展Google Spreadsheet的UI以提供包含下载链接的对话框是合乎逻辑的。 
 
 
 
  
 
 
 
需要很少的代码才能支持这一点。该工作由您的浏览器完成，基于包含< a> 的一行HTML（定位符）标记。 
 
 
  / ** 
 *添加一个自定义菜单
 * 
 * @param {Object} e一个简单的onOpen触发器的事件参数。 
 * / 
函数onOpen（e）{
 SpreadsheetApp.getUi（）
 .createMenu（'Custom'）
 .addItem（'Download as XLSX'，' downloadXLS_GUI'）
 .addToUi（）; 
} 
 
 
 / ** 
 *显示一个带有单个下载链接的模式对话框。 
 * 
 * From：http://stackoverflow.com/a/37336778/1677912 
 * / 
函数downloadXLS_GUI（）{
 //获取当前电子表格的ID ，放在下载URL中
 var ssID = SpreadsheetApp.getActive（）。getId（）; 
 var URL ='https://docs.google.com/spreadsheets/d/'+ssID+'/export?format=xlsx'; 
 
 //显示带有下载链接的模式对话框。 
 var htmlOutput = HtmlService 
 .createHtmlOutput（'< a href ='+ URL +'>点击下载< / a>'）
 .setSandboxMode（HtmlService.SandboxMode.IFRAME ）
 .setWidth（80）
 .setHeight（60）; 
 SpreadsheetApp.getUi（）。showModalDialog（htmlOutput，'下载XLS'）; 
} 
  
 
See below the function that converts the active Google Spreadsheet to a .xlsx file. The script saves the file in Google Drive.
function downloadAsXlsx() {

var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); 
var ssID = spreadSheet.getId();

Logger.log(ssID);

var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export?format=xlsx";   
var params = {method:"GET", headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var response = UrlFetchApp.fetch(url, params);

// save to drive
DriveApp.createFile(response);

}
If you replace the ssID in the URL above by the actual file id of the active Google Spreadsheet and copy and paste the URL in the browser, the active spreadsheet is downloaded "automatically". That is exactly what I need to be added to above script.


My question is how to change and/or extend the function above so that the file is instead downloaded to the local machine in the default download folder?
解决方案
The code you posted in your question, as you've noticed, exports an XLS version of the Google Spreadsheet to Google Drive. If you're syncing Google Drive to your PC, then you end up with a copy on your PC as well... but that's not what you're after. You want a way to trigger an HTTP download of the Google Sheet that uses the system dialog to let you choose where on your PC to save the downloaded file.

To accomplish this, we can't use server-side Google Apps Script techniques, as the server has no access to your machine's resources. Instead, we need to leverage the browser. Since you're interested in doing this from the "active spreadsheet", it's logical to extend the UI of your Google Spreadsheet with a custom menu item to serve up a dialog that includes a download link. 



Remarkably little code is required to support this. The work is done by your browser, based on the single line of HTML containing the <a> (anchor) tag.
/**
 * Adds a custom menu
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Custom')
      .addItem('Download as XLSX', 'downloadXLS_GUI')
      .addToUi();
}


/**
 * Display a modal dialog with a single download link.
 *
 * From: http://stackoverflow.com/a/37336778/1677912
 */
function downloadXLS_GUI() {
  // Get current spreadsheet's ID, place in download URL
  var ssID = SpreadsheetApp.getActive().getId();
  var URL = 'https://docs.google.com/spreadsheets/d/'+ssID+'/export?format=xlsx';

  // Display a modal dialog box with download link.
  var htmlOutput = HtmlService
                  .createHtmlOutput('<a href="'+URL+'">Click to download</a>')
                  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
                  .setWidth(80)
                  .setHeight(60);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download XLS');
}


                        
这篇关于自动“下载”电子表格作为.xlsx发送到本地计算机事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！