Google Apps脚本：工作表表单数据操作，如果某些单元格为空白，则删除行，同时维护某些列 [英] Google Apps Script: Sheets Forms Data Manipulation, Deleting Rows if Certain Cells are Blank, while Maintaining Certain Columns
问题描述
此问题是以下内容的继续：
Google Apps脚本：如果某些单元格为空白，则表格表单数据操作和删除行
This question is a continuation of the following: Google Apps Script: Sheets Forms Data Manipulation and Deleting Rows if Certain Cells are Blank
我有一张表格表单响应中，用户最多可以填写同一问题5次。以下代码将在粘贴值工作表中逐行组织这些行。除此之外，我还尝试按时间戳列按升序对列进行排序（这样，来自Google表单的新回复将显示在顶部）。
I have a sheet "Form Responses" where users can fill out the same question up to 5 times. The following code will organize these lines row by row in the 'Paste Values' sheet. In addition to this, I have tried to sort the columns by the Timestamp column in ascending order (so that new replies from the google form will show up at the top).
第一个问题是我的粘贴值表中有其他列（A，O，P，Q，R，S），这些列不在表单中片。当新表格到达顶部时，这些列中的信息不会随着它们应该位于的正确行而移动。他们只是留在原地。
The first issue with this is that I have additional columns in my Paste Values sheet (A, O, P, Q, R, S) that are not in my Forms Sheet. When a new form goes to the top, the information in these columns do not move with the correct row they were supposed to be in; they just stay where they are.
第二个问题是，如果我尝试添加在表单上提交触发器，那么每次提交新表单时粘贴值表都会更新，将会从头到尾累积
The second issue with this is that if I try to add an On Form Submit trigger, so that the Paste Values sheet updates every time a new form gets submitted, the rows will accumulate from start to finish
例如，如果我有：
Row1
Row2
Row3
然后提交新表单，它变为：
Row1
Row2
Row3
Row1
Row2
Row3
Row4
So for example if I have:
Row1
Row2
Row3
And then a new form gets submitted, it becomes:
Row1
Row2
Row3
Row1
Row2
Row3
Row4
这会导致重复出现我不想要的行。
This leads to a duplication of rows which I don't want.
我的最终目标是实现以下目标：
My end goal is to achieve the following:
1）每次有人提交新表格时，答案就会排在最前面；
1) every time someone submits a new form, the answers come to the top; everything is sorted in ascending order (by Timestamp column) in both 'Form Responses' and 'Paste Values'
2）在表单响应和粘贴值中，所有内容均按升序排序（按时间戳记列）2）添加一个on edit触发器，以便每次用户提交表单，仅将新行添加到粘贴值中，而不是一遍又一遍地重复所有行
2) Add an on edit trigger so that each time a user submits a form, only the new row(s) are added to 'Paste Values', rather than all of the rows duplicating over and over again
3）列： ，O，P，Q，R，S和T是我希望能够在粘贴值中自由编辑的列，这意味着每次将新行添加到粘贴值时，这些中的相应信息列与该行一起移动，并且不受编辑时触发器的影响。
3) Columns: A, O, P, Q, R, S, and T are columns that I want to be able to edit freely in 'Paste Values', meaning that every time a new row is added to 'Paste Values', the corresponding information in these columns move with that row, and are not affected by an On Edit trigger.
以下是Google工作表示例：
> https://docs.google.com/spreadsheets/d/1QUzPxxPB6CDL7Y5Dh92w6nsEM0QFUAaGG9LomjnGgLM/edit?usp=sharing
Here is the google sheet example: https://docs.google.com/spreadsheets/d/1QUzPxxPB6CDL7Y5Dh92w6nsEM0QFUAaGG9LomjnGgLM/edit?usp=sharing
表单响应表是Google表单接收的数据。粘贴值这是目标表就是我想要成为的表
Form Responses sheet is the data received by the google form. Paste Values 'this is the goal' sheet is what I would like it to become
行处理代码：
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var src = ss.getSheetByName("Form Responses");
var dst = ss.getSheetByName("Paste Values");
var values = src.getDataRange().getValues();
var header = values.splice(0, 1)[0].splice(0, 13);
var res = values.reduce(function(ar, e) {
var h = e.splice(0, 3);
h.unshift("");
for (var i = 0; i < 5; i++) {
var temp = e.splice(0, 10);
if (temp.filter(String).length == 0) continue;
if (temp.length < 10) temp.splice(temp.length, 10 - temp.length, "");
ar.push(h.concat(temp));
}
return ar;
}, []);
if (dst.getRange("A1").getValue() != "Status") res.unshift(["Status"].concat(header));
dst.getRange(dst.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
}
并按Timestamp列按升序对每一行进行编码的代码：
And code for sorting each row in ascending order by the Timestamp column:
// Sort Timestamp column from newest to oldest:
function Sort_Timestamp(event){
var spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses");
var editedCell = spreadsheet1.getActiveRange().getColumnIndex();
var columnToSortBy = 1;
var tableRange = "A:AZ";
if(editedCell == columnToSortBy){
var range = spreadsheet1.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: false } );
}
}
已更新尝试将行处理转换为粘贴值（并从表单响应的每一行返回每一行（拆分它们））：
Updated attempt to achieve the Row Manipulation into Paste Values (and return each row (splitting them) from each row of Form Responses):
function so55716140_01(event) {
// setup this function as an installable trigger OnFormSubmit
// set up spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourcename = "Form Responses";
var targetname = "Paste Values";
var sourcesheet = ss.getSheetByName(sourcename);
var targetsheet = ss.getSheetByName(targetname);
// get the response data - assumes 10 questions plus timestamp
var sourcerow = event.range.getRow();
// Logger.log("DEBUG: Response row = "+sourcerow); //DEBUG
var sourcerange = sourcesheet.getRange(sourcerow, 1, 1, 12); // getRange(source row, column #, number of rows, number of columns); so 13 goes to column N in form resonses "do you want to submit another form"
var sourcerange2 = sourcesheet.getRange(sourcerow, 13, 1, 10); //want to start at column 13 (col M)
var sourcerange3 = sourcesheet.getRange(sourcerow, 23, 1, 10); // third round column W
var sourcerange4 = sourcesheet.getRange(sourcerow, 33, 1, 10); // fourth round
var sourcerange5 = sourcesheet.getRange(sourcerow, 43, 1, 9); // fifth round
//Logger.log("DEBUG: Source range = "+sourcerange.getA1Notation()); //DEBUG
var sourcedata = sourcerange.getValues();
var sourcedata2 = sourcerange2.getValues();
var sourcedata3 = sourcerange3.getValues();
var sourcedata4 = sourcerange4.getValues();
var sourcedata5 = sourcerange5.getValues();
// setup the target
var Bvals = targetsheet.getRange("B1:B").getValues();
var Blast = Bvals.filter(String).length;
//Logger.log("DEBUG: Blast = "+Blast); //DEBUG
var targetrange = targetsheet.getRange(Blast + 1, 2, 1, 12); //you're starting at the next row. so if there is data in column B3 row 2, you'll start at column B3 row 3
// starting at column 2 in Paste Values (timestamp), only on that one row, and paste in the next 12 values (started at column 2)
var targetrange2 = targetsheet.getRange(Blast + 2, 4, 1, 10); //trying blast + 2, and you're only pasting 10 values
//Logger.log("DEBUG: Target range = "+targetrange.getA1Notation()); //DEBUG
var targetrange3 = targetsheet.getRange(Blast + 3, 4, 1, 10);
var targetrange4 = targetsheet.getRange(Blast + 4, 4, 1, 10);
var targetrange5 = targetsheet.getRange(Blast + 5, 4, 1, 9);
// paste the response to the target
targetrange.setValues(sourcedata);
targetrange2.setValues(sourcedata2);
targetrange3.setValues(sourcedata3);
targetrange4.setValues(sourcedata4);
targetrange5.setValues(sourcedata5);
}
推荐答案
Tanaike的代码是艺术，但我认为这是基于您只能运行一次脚本的假设。
Tanaike's code is a work of art, but I think it is based on an assumption that you would only run the script once.
您已经说过用户将填写Google表单。然后，您可以对其进行操作，以便将具有相同列的行转移到一列。但是具有讽刺意味的是，您随后将其分解以生成粘贴值上的结果。
You've said that users will fill out a Google Form. You then manipulate this so that rows with identical columns will be transferred to one column. But ironically you then disassemble this to produce the results on "Paste Values".
我建议使用一个简单得多的过程：
I suggest a far less complicated process:
- 在收到任何表单响应之前，为粘贴值 创建并安装标题。
- 编写一个脚本是作为
OnFormSubmit'触发器手动安装的。使用触发器返回的对象数据将相关数据复制到粘贴值的最后一行（加1）。您可以考虑调整表单，以便从下拉列表中选择提交者的名称-确保拼写一致。 - 逐步对粘贴值进行排序；也就是说，将代码添加到
FormSubmit触发器。
- create and install a header for "Paste Values" before any form responses are received.
- write a script that is manually installed as an
OnFormSubmit' trigger. Use the object data returned by the trigger to copy the relevant data to the last row (plus 1) of "Paste Values". You might consider adjusting the form so that the name of the Submitter is selected from a dropdown - to ensure consistent spelling. - sort "Paste Values" progressively; that is, add the code to the
FormSubmittrigger.
这使您可以将您的注释和其他注释写在粘贴值上，这些注释和其他注释将在排序后与相关行保持一致。
This enables you to write your notes and other comments on Paste Values, and these will remain aligned with the relevant row after the sort.
CODE
function so55716140_01(event) {
// setup this function as an installable trigger OnFormSubmit
// set up spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourcename = "Form Responses 2";
var targetname = "Paste Values";
var sourcesheet = ss.getSheetByName(sourcename);
var targetsheet = ss.getSheetByName(targetname);
// get the response data - assumes 10 questions plus timestamp
var sourcerow = event.range.getRow();
// Logger.log("DEBUG: Response row = "+sourcerow); //DEBUG
var sourcerange = sourcesheet.getRange(sourcerow, 1, 1, 11);
//Logger.log("DEBUG: Source range = "+sourcerange.getA1Notation()); //DEBUG
var sourcedata = sourcerange.getValues();
// setup the target
var Bvals = targetsheet.getRange("B1:B").getValues();
var Blast = Bvals.filter(String).length;
//Logger.log("DEBUG: Blast = "+Blast); //DEBUG
var targetrange = targetsheet.getRange(Blast + 1, 2, 1, 11);
//Logger.log("DEBUG: Target range = "+targetrange.getA1Notation()); //DEBUG
// paste the response to the target
targetrange.setValues(sourcedata);
// OP to add sort code according to preference
}
< hr>
可能具有5个部分的表单代码
function ejb_op3(event) {
// setup this function as an installable trigger OnFormSubmit
// set up spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourcename = "Form Responses 4";
var targetname = "Paste Values";
var sourcesheet = ss.getSheetByName(sourcename);
var targetsheet = ss.getSheetByName(targetname);
// get the response row and range
var sourcerow = event.range.getRow();
//Logger.log("DEBUG: Response row = "+sourcerow); //DEBUG
// range is from Column A to Column AZ: 52 columns; 3=common; 4x10 (40) = printer "section" questions PLUS "do you want to submit another form"; the final (5th) 1x9 printer "section" questions; 3+40+9=52
var sourcerange = sourcesheet.getRange(sourcerow, 1, 1, 52);
//Logger.log("DEBUG: Source range = "+sourcerange.getA1Notation()); //DEBUG
// get the response data
var sourcedata = sourcerange.getValues();
// find the number of rows already populated on the target
var Bvals = targetsheet.getRange("B1:B").getValues();
var Blast = Bvals.filter(String).length;
//Logger.log("DEBUG: Blast = "+Blast); //DEBUG
// establish some variables
var datastart = 3; // the first 3 columns are common data
var dataqty = 10; // the first 4 responses have 10 columns of response data
var printcount = 0; // status counter
var responsecount = 0; // column status counter
var responsedata = []; // array to compile responses
// process the first section
if (printcount == 0) {
responsedata = [];
// get the timestamp, submitter name and email
responsedata.push(sourcedata[0][0]);
responsedata.push(sourcedata[0][1]);
responsedata.push(sourcedata[0][2]);
//get the responses for the next 10 questions
for (i = datastart; i < (datastart + dataqty); i++) {
responsedata.push(sourcedata[0][i]);
}
// define the target range
// the last line (Blast)plus one line plus the print count; column B; 1 row; 13 columns
var targetrange = targetsheet.getRange(Blast + 1 + printcount, 2, 1, 13);
// paste the values to the target
targetrange.setValues([responsedata]);
// update variables
responsecount = i; // copy the value of i
printcount++; // update status counter
responsedata = []; // clear the array ready for the next section
}
// end opening response
// build routine for 2nd to 4th sections
for (z = 2; z < 5; z++) {
//Make sure not to double count the first section
if (printcount > 0 && printcount < 5) {
// test if the next section exists
if (sourcedata[0][i - 1] == "Yes") {
// Yes for next section
//Logger.log("DEBUG: value = "+sourcedata[0][i-1]); //DEBUG
// get the timestamp, submitter name and email
responsedata.push(sourcedata[0][0]);
responsedata.push(sourcedata[0][1]);
responsedata.push(sourcedata[0][2]);
//get the responses for the next 10 questions
for (i = responsecount; i < (responsecount + dataqty); i++) {
responsedata.push(sourcedata[0][i]);
//Logger.log("DEBUG: data: "+sourcedata[0][i]);//DEBUG
}
// define the target range
// the last line (Blast) plus one line plus the print count; column B; 1 row; 13 columns
targetrange = targetsheet.getRange(Blast + 1 + printcount, 2, 1, 13);
// paste the values to the target
targetrange.setValues([responsedata]);
// update variables
responsecount = i;
printcount++;
responsedata = [];
} else {
// NO for next section
}
// end routine if the next section exists
} // end routine for the next section
} // end routine for sections 2, 3 and 4
// checking for 5th response
if (printcount == 4) {
// test if response exists
if (sourcedata[0][i - 1] == "Yes") {
// Yes for next section
//Logger.log("DEBUG: value = "+sourcedata[0][i-1]); //DEBUG
// get the timestamp, submitter name and email
responsedata.push(sourcedata[0][0]);
responsedata.push(sourcedata[0][1]);
responsedata.push(sourcedata[0][2]);
//get the responses for the next 9 (nine) questions
for (i = responsecount; i < (responsecount + dataqty - 1); i++) {
responsedata.push(sourcedata[0][i]);
//Logger.log("DEBUG: data: "+sourcedata[0][i]);//DEBUG
}
// define the target range
// the last line (Blast) plus one line plus the print count; column B; 1 row; 12 columns only
targetrange = targetsheet.getRange(Blast + 1 + printcount, 2, 1, 12);
// paste the values to the target
targetrange.setValues([responsedata]);
} else {
// NO for next section
}
}
// end routine for the 5th section
}
这篇关于Google Apps脚本：工作表表单数据操作，如果某些单元格为空白，则删除行，同时维护某些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
