如何通过Google Apps脚本在Spreadheet中下载/导出表格 [英] How To Download / Export Sheets In Spreadheet Via Google Apps Script

问题描述

任务是自动化菜单选项文件|下载为|纯文本完成的手动过程

我想要能够控制保存的文件名称，这不能通过菜单完成。

在调用时，用户将坐在电子表格中的工作表上。最终，我会做一个菜单选项，但是对于测试，我只是创建一个可以手动运行的函数。

在阅读了其他可能技术的其他线程之后这是我想出来的。

它为文件创建一个自定义名称，使呼叫和响应代码为200。

理想情况下，我想避免打开/保存对话框。换句话说，只需保存文件，无需额外的用户干预。我想保存在一个特定的文件夹，我已经尝试了一个完整的文件规格，但结果是一样的。

如果我复制显示的URL在记录器中并将其粘贴到浏览器中，它启动打开/保存对话框，以便字符串工作。

这是代码作为一个函数。

  function testExportSheet（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var oSheet = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
 var sId = ss.getId（）; 
 var ssID = sId +& gid =+ oSheet.getSheetId（）; 
 var url =https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=
 + ssID +& exportFormat = tsv; 
 Logger.log（url）; 
 var fn = ss.getName（）+ - + oSheet.getSheetName（）+.csv; 
 var sHeaders = {Content-Disposition：attachment; filename = \+ fn +\}; 
 var sOptions = {contentType：text / html，headers：sHeaders}; 
 Logger.log（sOptions）; 
 
 x = UrlFetchApp.fetch（url，sOptions）
 Logger.log（x.getResponseCode（））; 
} 
  

解决方案

CSV直接进入本地硬盘驱动器，如下所示：

1. 使用函数convertRangeToCsvFile_（）从当前工作表中获取CSV内容，从本页面的教程中， https://developers.google.com/apps-script / articles / docslist_tutorial＃section3

     var csvFile = convertRangeToCsvFile _（...）; 
     




2. 然后选择使用云端硬盘与本地计算机同步的驱动器文件夹

     var localFolder = DocsList.getFolderById（055G ... GM）; 
     




3. 最后将CSV文件保存到本地文件夹中

     localFolder.createFile（sample.csv，csvFile）; 
     

就是这样。 $ b

The task is to automate the manual process accomplished by the menu option "File | Download As | Plain Text"

I want to be able to control the saved file name, which cannot be done via the menu.

At the time this is invoked, the user would be sitting on the sheet in the spreadsheet. Ultimately, I'd make it a menu option, but for testing I'm just creating a function that I can run manually.

After reading several other threads for possible techniques, this is what I've come up with.

It builds a custom name for the file, makes the call, and the response code is 200.

Ideally, I'd like to avoid the open / save dialog. In other words, just save the file without additional user intervention. I'd want to save in a specific folder and I've tried it with a complete file spec, but the result is the same.

If I copy the URL displayed in the Logger and paste it into a browser, it initiates the open / save dialog, so that string works.

Here's the code as a function.

function testExportSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var oSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sId = ss.getId();
var ssID=sId + "&gid=" + oSheet.getSheetId();
var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="
  + ssID + "&exportFormat=tsv"; 
Logger.log(url);
var fn =  ss.getName() + "-" + oSheet.getSheetName() + ".csv";
var sHeaders = {"Content-Disposition" : "attachment; filename=\"" + fn + "\""};
var sOptions = {"contentType" : "text/html", "headers" : sHeaders};
Logger.log(sOptions);

x = UrlFetchApp.fetch(url, sOptions)
Logger.log(x.getResponseCode());
}

解决方案

I have exported a spreadsheet as CSV directly into a local hard drive as follows:

1. Get the CSV content from current sheet using a variation of function convertRangeToCsvFile_() from the tutorial on this page https://developers.google.com/apps-script/articles/docslist_tutorial#section3

   var csvFile = convertRangeToCsvFile_(...);
   

2. Then select a drive folder that is syncing to a local computer using Drive

   var localFolder = DocsList.getFolderById("055G...GM");
   

3. And finally save the CSV file into the "local" folder

   localFolder.createFile("sample.csv", csvFile);
   

That's it.

这篇关于如何通过Google Apps脚本在Spreadheet中下载/导出表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！