如果某些单元格为空白，则表格形成数据操作并删除行 [英] Sheets Forms Data Manipulation and Deleting Rows if Certain Cells are Blank

问题描述

此问题类似于"Google表格中的表单数据操作"( https ://webapps.stackexchange.com/questions/88736/forms-data-manipulation-in-google-sheets )，但需要更多的自动化功能:

This question is similar to "Forms Data Manipulation In Google Sheets" (https://webapps.stackexchange.com/questions/88736/forms-data-manipulation-in-google-sheets) but requires a bit more automation:

背景:用户填写Google表单以提出请求，并可以选择重复同样的问题来填写第二，第三，第四和第五个请求.我已经创建了一个表格来处理这些行，以便将具有相同列的行转移到一列.

Background: Users fill out a google form for a request and have the option of repeating those same questions to fill out a second, third, fourth, and fifth request. I have created a sheet that will manipulate these rows so that rows with identical columns will be transferred to one column.

这是我的示例表: https://docs.google.com/spreadsheets_lg/ZR7Z6O1Z6O7Z1O6Z1Z6O7Z1O7Z1O7Z1O7Z1O7Z1O6a7B edit#gid = 1967901028

表单响应:该表单返回用户填写表单的响应

Form Responses: sheet that returns the responses from users filling out form

操纵行:工作表使用以下命令返回操纵行: = OFFSET('表单响应'！$ A $ 2，上限((row()-row($ B $ 1))/5,1)-1，column()-column($ B $ 1)，1，COUNTA($ B $ 1:$ D $ 1))在单元格B2中， 和

Manipulated Rows: sheet that returns manipulated rows using: =OFFSET('Form Responses'!$A$2,ceiling((row()-row($B$1))/5,1)-1,column()-column($B$1),1,COUNTA($B$1:$D$1)) in cell B2, and

= OFFSET('Form Responses'！$ A $ 2，ceiling((row()-row($ B $ 1))/5,1)-1，mod(row()-(row($ B $ 1) +1)，5)* COUNTA($ E $ 1:$ N $ 1)+ COUNTA($ B $ 1:$ D $ 1)，1，COUNTA($ E $ 1:$ N $ 1)) 在单元格E2中

=OFFSET('Form Responses'!$A$2,ceiling((row()-row($B$1))/5,1)-1,mod(row()-(row($B$1)+1),5)*COUNTA($E$1:$N$1)+COUNTA($B$1:$D$1),1,COUNTA($E$1:$N$1)) in cell E2

粘贴值:此工作表将返回操作行的粘贴值，不包括偏移量"公式，然后删除任何包含空白单元格E-N的行.这是反映在粘贴值"标签中的应用程序脚本:

Paste Values: this sheet returns a paste values of Manipulated Rows, excluding the Offset formula and then deleting any rows that have blank cells E-N. Here is the apps script reflected in the 'Paste Values' tab:

var ss = SpreadsheetApp.getActive();
var sheet = SpreadsheetApp.getActiveSheet();

//Duplicate sheet 'Manipulated Rows' as paste values
function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Paste Values');
  var source = ss.getRange('Manipulated Rows!A1:T100000');
  source.copyTo(ss.getRange('Paste Values!A1'), {contentsOnly: true});
  deleteRows(sheet);
}

//Function to Delete empty rows:
function deleteRows(sheet) {
  var rows = sheet.getDataRange();
  var range_manipulated_rows = ss.getSheetByName('Manipulated Rows!A1:T100000');
  var range_paste_values = ss.getSheetByName('Paste Values!A1:T100000');
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (range_manipulated_rows == range_paste_values && row[4] == '' && row[5] == '' && row[6] == '' && row[7] == '' && row[8] == '' && row[9] == '' 
        && row[10] == '' && row[11] == '' && row[12] == '' && row[13] == '') { // if paste values tab is equal to manipulated rows tab and cell E-N are blank
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

我想通过创建一个应用程序脚本使此操作更加自动化，该脚本将直接将表单响应"表转换为粘贴值"表，而无需使用操纵行".就像在粘贴值"工作表中一样，它需要删除所有单元格E-N均为空白的所有行.

推荐答案

• 您要使用Google Apps脚本直接将表单响应"的值转换为粘贴值".
• 从地址"到您还有其他打印机请求吗?"有5个周期. "D"至"AZ"列中的一个.数据可能是1个周期和3个周期.但是最多5个周期是恒定的.

从您的问题和评论中，我可以理解以上内容.这个示例脚本怎么样?

From your question and comments, I could understand above. How about this sample script?

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

注意:

• 在此示例脚本中，使用Form Responses和Paste Values的工作表名称.如果要更改工作表名称，请修改脚本.
• 在此示例脚本中，自动设置Paste Values表的标题行.如果您不想设置此项，请修改脚本.

Note:

• In this sample script, the sheet names of Form Responses and Paste Values are used. If you want to change the sheet name, please modify the script.
• In this sample script, the header row of the sheet of Paste Values is automatically set. If you don't want to set this, please modify the script.
• splice()
• reduce()
• concat()
• splice()
• reduce()
• concat()

这篇关于如果某些单元格为空白，则表格形成数据操作并删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！