使用脚本从Google云端硬盘表单导出电子表格或CSV文本文件？ [英] Export spreadsheet or CSV text file from Google Drive Forms using script?

问题描述

我们有一个Google表单，将其提交保存到Google Spreadsheet中。

使用脚本管理器，我们如何导出电子表格内容或最新的表单提交信息到本地Excel电子表格或制表符分隔的文本文件在我的硬盘上？

这是一个两步过程：

将数据放入本地硬盘。

我们如何使用Google电子表格脚本执行＃1和/或＃2？

我们在电子表格上创建了​​一个OnFormSubmit回调函数，但我们只能将事件消息（表单提交的数据）记录到Drive中的一个弹出窗口。

<$ p $函数OnFormSubmit（e）{

var sheet = SpreadsheetApp.getActiveSheet（）;
var row = SpreadsheetApp.getActiveSheet（）。getLastRow（）;

var newMessage = e;

Logger.log（newMessage）;
}

有一个CreateFile方法，但不清楚我可以如何使用它。

解决方案

我可以使用下面的代码在我的Google云端硬盘上创建一个制表符分隔的文本文件， Google电子表格，用于收集表单中的数据。

代码由Google电子表格更改事件触发。这允许代码在提交新表单时运行，但它也将更新电子表格中任何更改事件的文本文件。这允许您编辑现有的条目。

一旦文件在您的驱动器上，一个简单的计划批处理文件可以将文件复制到您的谷歌驱动器以外的任何位置。 p>

  function saveAsTabDelimitedTextFile（）{
 //获取电子表格名称
 var fileName = SpreadsheetApp.getActiveSheet（）。getSheetName （）; 
 
 //将.txt扩展名添加到文件名
 fileName = fileName +.txt; 
 
 //将范围数据转换为制表符分隔的格式
 var txtFile = convertRangeToTxtFile_（fileName）; 
 
 //删除现有文件
 deleteDocByName（fileName）; 
 
 //使用给定的名称和数据在文档列表中创建一个文件
 DocsList.createFile（fileName，txtFile）; 
} 
 
 
函数convertRangeToTxtFile_（txtFileName）{
 try {
 var txtFile = undefined; 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheets（）[0]; 
 var rows = sheet.getDataRange（）; 
 var data = rows.getValues（）; 
 
 //遍历范围中的数据并使用数据
构建一个字符串if（data.length> 1）{
 var txt =; 
 for（var row = 0; row< data.length; row ++）{
 //加入每一行的列并将回车添加到每一行的结尾
 txt + = data [ row] .join（\t）+\r\\\
; 
} 
 txtFile = txt; 
} 
返回txtFile; 
} 
 catch（err）{
 Logger.log（err）; 
 Browser.msgBox（err）; 
} 
} 
  

We have a Google Form that saves its submits to a Google Spreadsheet.

Using the Script manager, how do we export that spreadsheet contents or the latest form submit message to a local Excel spreadsheet or tab-delimited text file on my harddrive?

This would be a 2 step process:

1. catch form submit or spreadsheet change event and externalize the data into the cloud Drive.
2. place that data on my local hard drive.

How do we do #1 and/or #2, using the Google spreadsheet script?

We have created a OnFormSubmit callback function on the spreadsheet, but we can only log the event message (the form submitted data) to a popup window in Drive.

function OnFormSubmit(e) {

  var sheet = SpreadsheetApp.getActiveSheet();
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();

  var newMessage = e;

  Logger.log(newMessage);
}

There is a CreateFile method, but its not clear how I could use that.

解决方案

I was able to use the code below to create a tab delimited text file on my Google Drive that's a copy of the original Google spreadsheet that collects the data from the form.

The code is triggered on the Google spreadsheet change event. This allows the code to run when a new form is submitted, but it also will update the text file on any change event in the spreadsheet. This allows you to edit existing entries.

Once the file is on your drive, a simple scheduled batch file can copy the file to any location outside your google drive.

function saveAsTabDelimitedTextFile() {
  // get Spreadsheet Name
  var fileName = SpreadsheetApp.getActiveSheet().getSheetName();

  // Add the ".txt" extension to the file name
  fileName = fileName + ".txt";

  // Convert the range data to tab-delimited format
  var txtFile = convertRangeToTxtFile_(fileName);

  // Delete existing file
  deleteDocByName(fileName);

  // Create a file in the Docs List with the given name and the data
  DocsList.createFile(fileName, txtFile);
}


function convertRangeToTxtFile_(txtFileName) {
  try {
    var txtFile = undefined;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var rows = sheet.getDataRange();
    var data = rows.getValues();

    // Loop through the data in the range and build a string with the data
    if (data.length > 1) {
      var txt = "";
      for (var row = 0; row < data.length; row++) {
        // Join each row's columns and add a carriage return to end of each row
        txt += data[row].join("\t") + "\r\n";
      }
      txtFile = txt;
    }
    return txtFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

这篇关于使用脚本从Google云端硬盘表单导出电子表格或CSV文本文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！