【Google Apps脚本（表格形式数据处理，如果某些单元格为空，则删除行，同时维护某些列）】天下之事常成于困约，而败于奢靡。这篇文章主要讲述Google Apps脚本：表格形式数据处理，如果某些单元格为空，则删除行，同时维护某些列相关的知识，希望能为你提供帮助。
这个问题是以下内容的延续：Google Apps Script: Sheets Forms Data Manipulation and Deleting Rows if Certain Cells are Blank
我有一张表格“表格回复”，用户可以在同一时间内填写相同的问题5次。以下代码将在“粘贴值”表中逐行组织这些行。除此之外，我还尝试按时间戳列按升序对列进行排序（以便谷歌表单中的新回复显示在顶部）。
第一个问题是我的“粘贴值”表（A，O，P，Q，R，S）中的其他列不在我的表单中。当新表单到达顶部时，这些列中的信息不会随着它们应该存在的正确行而移动;
他们只是呆在原地。
第二个问题是，如果我尝试添加一个On Form Submit触发器，以便每次提交新表单时粘贴值表都会更新，行将从头到尾累积
所以，例如，如果我有：
ROW1
行2
ROW3然后提交一个新表单，它变为：
ROW1
行2
ROW3
ROW1
行2
ROW3
ROW4
这导致了我不想要的行重复。
我的最终目标是实现以下目标：
1）每当有人提交新表格时，答案就会出现;
在“表单响应”和“粘贴值”中，所有内容都按升序排序（按时间戳列）
2）添加一个on edit触发器，这样每次用户提交表单时，只有新行被添加到'Paste Values'，而不是所有的行一遍又一遍地重复
3）列：A，O，P，Q，R，S和T是我希望能够在“粘贴值”中自由编辑的列，这意味着每次将新行添加到“粘贴值”时，这些列中的相应信息随该行移动，并且不受On Edit触发器的影响。
这是google表格示例：https://docs.google.com/spreadsheets/d/1QUzPxxPB6CDL7Y5Dh92w6nsEM0QFUAaGG9LomjnGgLM/edit?usp=sharing
表格回复表是谷歌表格收到的数据。粘贴值'这是目标'表格是我希望它成为
行操作的代码：
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var src = https://www.songbingjia.com/android/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 13goes 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 = https://www.songbingjia.com/android/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的代码是一件艺术品，但我认为它基于一个假设你只运行一次脚本。
您已经说过，用户将填写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 = https://www.songbingjia.com/android/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 = https://www.songbingjia.com/android/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 = https://www.songbingjia.com/android/[];
// 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 = "https://www.songbingjia.com/android/+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 = https://www.songbingjia.com/android/[];
} 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 = "https://www.songbingjia.com/android/+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}
推荐阅读
- 如何从Xamarin Android Activity中调用MvxViewModel（）
- 如何下载以前保存的Google Apps脚本修订版（）
- Google App Script在doc中显示提示，但未使用电子表格权限
- 某些Google幻灯片AppScript是否已记录但不受支持（）
- 我是否需要更改我的Apps脚本代码，因为Google+登录功能已被弃用（）
- 来自GmailApp附件的DriveApp createFile无法正常工作
- Google Apps脚本 - 电子邮件库存通知（）
- 从服务器响应(请求)返回HTML，好的还是不好的做法（）
- 你需要了解的有关条件html注释的所有信息