脚本来复制和排序表单提交数据 [英] Script to copy and sort form submission data

问题描述

我使用Google表单创建了一个电子表格，我想通过datestamp Z-A自动排序。每当有人填写表格时，排序都会被触发。

我认为这样做的方法是：

1. 询问是否存在电子表格中的表单回复副本...
   
   
   
   • 如果有，请清除所有内容...
   
   
   • 其他...
   
   


2. 将电子表格复制到表单回复副本...
   
3. 根据时间戳进行排序

以下是我迄今为止所修补的内容。它仅在第一次记录响应时起作用。我不是一个编码器，所以任何帮助表示赞赏。如果有人也可以用基本语法指向我的一个命令列表，我将不胜感激。

 函数CopySheet（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var msheet = ss.getSheetByName（Form Responses）; 
 msheet.copyTo（ss）; 
 
 var CopySheet = ss.getSheetByName（Form Responses的副本）; 
 CopySheet.sort（1，false）; //这里1是列号。 1表示
 //是A列，true表示升序，如果你想下降，则使
 // false。 
}; 
  

解决方案

code> QUERY（）。例如，如果您将此函数放置在副本表格的单元格A1中，并将该关键字替换为表单响应电子表格，则最终将获得反向时间戳记响应的副本：

  =查询（ImportRange（spreadsheet_key，Form Responses！A：Z），select * order by Col1 desc）
  

这些数据会定期刷新（〜5分钟），因此它会反映新的表单提交，但不是实时的。 / p>
$ b 注意：当使用 ImportRange（）作为查询，你需要使用 ColN 符号来引用Query字符串中的列。

或者，你可以生成电子表格中的表单提交触发器函数接收表单提交，并将排序后的表单响应复制到您的副本表单中。以下功能可以做到这一点。您需要将其设置为电子表格提交事件的触发功能。有关如何测试此功能的信息，请参阅如何测试GAS中的触发器函数？。

 函数copyFormSubmissions（e）{
 var sourceSheet = e.range.getSheet（）; 
 var data = sourceSheet.getDataRange（）。getValues（）; 
 var headers = data.splice（0,1）[0]; //从数据中删除标题
 data.sort（reverseTimestampOrder）; //排序2d数组
 data.splice（0,0，headers）; //替换头文件
 
 var destId =--copy-sheet-ID--; 
 var destSheet = SpreadsheetApp.openById（destId）.getSheetByName（'Sheet1'）; 
 destSheet.clear（）; 
 destSheet.getRange（1,1，data.length，data [0] .length）.setValues（data）; 
}; 
 
函数reverseTimestampOrder（a，b）{
 //时间戳在第一列（第零）
 return（b [0] -a [0]）; 
} 
  

如果有人也可以将我指向列表

Google Apps脚本API类和方法参考是这里。如果您正在学习，请尝试使用教程（同一地点），并且我建议您通过某种形式的电子学习熟悉Javascript - CodeAcademy.com是一个很好的开始，因为它引入了所有语言结构，而不关注网页开发，使其与Google Googls Apps Script非常相关。

I'm using Google forms to create a spreadsheet that I want sorted automatically by datestamp Z-A. The sorting will be triggered whenever anyone fills out a form.

I think the way to do it is:

1. ask if there is a "copy of Form responses" on spreadsheet...
   • if yes, clear all contents...
   • else...
2. copy spreadsheet to "copy of form responses"...
3. sort according to timestamp

Below is what I've cobbled so far. It works only the first time a response is recorded. I'm not a coder so any help is appreciated. If someone could also point me to a list of commands with basic syntax I'd be grateful.

function CopySheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var msheet = ss.getSheetByName("Form Responses"); 
    msheet.copyTo(ss);

    var CopySheet = ss.getSheetByName("Copy of Form Responses"); 
    CopySheet.sort(1,   false); // here 1 is for column no. 1 that 
               // is "Column A" and true is for ascending, make it 
               // false if you want descending.
};

解决方案

You can accomplish this without a script, by using QUERY() in the copy sheet. For instance, if you put this function in cell A1 of your copy sheet, and substitute the key for your form response spreadsheet, you'll end up with a reverse-timestamp-sorted copy of the responses:

=Query(ImportRange(spreadsheet_key,"Form Responses!A:Z"), "select * order by Col1 desc")

This data will be refreshed periodically (~5 mins), so it will reflect new form submissions, but not in real-time.

Note: When using ImportRange() as source data for Query, you need to refer to columns in the Query string using ColN notation.

Alternatively, you could produce a form submission trigger function in the spreadsheet receiving the form submissions, and have it copy the sorted form responses to your copy sheet. The following function does that. You need to set it up as a trigger function for Spreadsheet Form Submission Events. For information on how to test such a function, see How can I test a trigger function in GAS?.

function copyFormSubmissions(e) {
  var sourceSheet = e.range.getSheet();
  var data = sourceSheet.getDataRange().getValues();
  var headers = data.splice(0,1)[0]; // remove headers from data
  data.sort(reverseTimestampOrder);  // Sort 2d array
  data.splice(0,0,headers);          // replace headers

  var destId = "--copy-sheet-ID--";
  var destSheet = SpreadsheetApp.openById(destId).getSheetByName('Sheet1');
  destSheet.clear();
  destSheet.getRange(1,1,data.length,data[0].length).setValues(data);
};

function reverseTimestampOrder(a,b) {
  // Timestamp is in first (zero-th) column
  return (b[0]-a[0]);
}

If someone could also point me to a list of commands with basic syntax I'd be grateful.

The Google Apps Script API classes and methods reference is here. If you're learning, try the tutorials (same place), and I recommend you get familiar with Javascript through some form of e-learning - CodeAcademy.com is a good place to start, since it introduces all the language constructs without focusing on web page development, making it very relevant for Googls Apps Script.

这篇关于脚本来复制和排序表单提交数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！