Google Apps脚本（表格形式数据处理，如果某些单元格为空，则删除行，同时维护某些列） _单元格

【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}