Google Apps 脚本:表格表单数据操作，如果某些单元格为空白，则删除行，同时保留某些列

Question

这个问题是以下内容的延续： Google Apps 脚本：表格表单数据操作和删除行(如果某些单元格为空白)

我有一张"表单回复"表，用户最多可以填写 5 次相同的问题。以下代码将在"粘贴值"工作表中逐行组织这些行。除此之外，我还尝试按时间戳列按升序对列进行排序(以便来自谷歌表单的新回复将显示在顶部)。

第一个问题是我的粘贴值表(A、O、P、Q、R、S)中有其他列不在我的表单表中。当新表单转到顶部时，这些列中的信息不会随它们应该在的正确行一起移动;他们只是呆在原地。

第二个问题是，如果我尝试添加一个表单提交触发器，以便每次提交新表单时都会更新"粘贴值"工作表，则行将从头到尾累积

例如，如果我有：
Row1 Row2 Row3 然后提交一个新表单，它就变成了：
Row1 Row2 Row3 Row1


Row2 Row3





Row4

这会导致我不想要的行重复。

我的最终目标是实现以下目标：

1)每次有人提交新表格时，答案都会排在最前面;所有内容在"表单响应"和"粘贴值"中按升序(按时间戳列)排序

2) 添加编辑时触发器，以便每次用户提交表单时，仅将新行添加到"粘贴值"中，而不是一遍又一遍地复制所有行

3) 列：A、O、P、Q、R、S 和 T 是我希望能够在"粘贴值"中自由编辑的列，这意味着每次将新行添加到"粘贴值"时，这些列中的相应信息都会随该行一起移动，并且不受"编辑时"触发器的影响。

这是谷歌表格的例子： https://docs.google.com/spreadsheets/d/1QUzPxxPB6CDL7Y5Dh92w6nsEM0QFUAaGG9LomjnGgLM/edit?usp=sharing

表单回复表是谷歌表单接收的数据。粘贴值"这是目标"表是我希望它成为的样子

行操作的代码：

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);
}

以及按时间戳列升序对每一行进行排序的代码：

// 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 } );
}
}

更新了实现将行操作粘贴值(并从表单响应的每一行返回每一行(拆分它们))的尝试：

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);
}

Answer 1

Tanaike 的代码是一件艺术品，但我认为它基于一个假设，即你只会运行一次脚本。

您说过用户将填写Google表单。然后，您可以对其进行操作，以便将具有相同列的行传输到一列。但具有讽刺意味的是，您随后将其反汇编以生成"粘贴值"的结果。

我建议一个简单得多的过程：

• 在收到任何表单响应之前，创建并安装"粘贴值"的标头。
• 编写手动安装为OnFormSubmit' trigger的脚本。使用触发器返回的对象数据将相关数据复制到"粘贴值"的最后一行(加 1)。您可以考虑调整表单，以便从下拉列表中选择提交者的名称 - 以确保拼写一致。
• 逐步对"粘贴值"进行排序;即，将代码添加到FormSubmit触发器。

这使您能够在"粘贴值"上编写注释和其他注释，这些注释将在排序后与相关行保持一致。

法典

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
}

---

可能具有 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

}