Google Spreadsheets脚本删除任何找到字符串的行 [英] Google Spreadsheets script to delete any rows where a string is found

问题描述

我需要循环浏览表单中的所有行/列，并删除包含特定单词的行。理想情况下，我会使用正则表达式搜索表单，但只要找到一个字符串就可以帮助我移动。我在堆栈溢出中看到很多关于查找和删除空行的帖子，但找不到找到整个表并删除行的任何内容。

I need to loop through all rows/columns in a sheet and remove rows contain certain words. Ideally, I would search through the sheet using a regular expression, but just finding a string would help get me moving. I'm seeing a lot of posts on Stack Overflow about finding and deleting empty rows, but can't find anything about searching an entire sheet and deleting a row if found.

这是我到目前为止：

This is what I have so far:

/* Delete rows */
function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var deleted = 0;  // Counter (don't need if we loop backwards)
  var regExp = new RegExp('word');

  for (var row = 0; row < values.length; row++) {
    var regExpMatch = values[row][1].match(regExp);
    if (regExpMatch.length > 0) {
      sheet.deleteRow(row + 1 - deleted);
      deleted++;
    }
  }
  SpreadsheetApp.flush();
};

然而，这只会搜索列B，并且会引发错误TypeError：Can not read property'length'来自null，尽管我的电子表格的B列中存在word。如果我做了一个简单的版本，比如：

However this only searches Column B, and it throws an error "TypeError: Cannot read property 'length' from null" even though "word" exists in Column B in my spreadsheet. And if I do a simpler version like:

/* Delete rows */
function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var deleted = 0;  // Counter (don't need if we loop backwards)

  for (var row = 0; row < values.length; row++) {
    if (values[row][1].search("WordThatExistsInOneRow")) {
      sheet.deleteRow(row);
      deleted++;
    }
  }
  SpreadsheetApp.flush();
};

即使WordThatExistsInOneRow仅显示在一行中，它也会开始删除每一行。

It starts deleting every row, even if "WordThatExistsInOneRow" only shows up in 1 row.

推荐答案

您没有正确编写循环逻辑，您需要遍历每行然后遍历该行中的每列，然后指定行索引删除必须按照崇敬的顺序进行。试试这个：

You have not wrote your loop logic correctly, you need to loop over each row then each column in that row and then assign the row index for deletion which must be performed in revere order. Try this:

/* Delete rows */
function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var toDelete = [];

  for (var row = 0; row < values.length; row++) {
    for(var column = 0;column<values[row].length;column++){
      if (values[row][column].indexOf("WordThatExistsInOneRow") > -1){
        toDelete.push(row);
      }
    }
  }

  for(var deleteRow = toDelete.length-1; deleteRow >= 0;deleteRow--){
    sheet.deleteRow(toDelete[deleteRow]+1);
  }

  SpreadsheetApp.flush();
};

这篇关于Google Spreadsheets脚本删除任何找到字符串的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！