根据行中单元格的内容删除谷歌电子表格中的一行 [英] Delete a row in google spreadsheet base on contents of cell in row

问题描述

我有这个脚本来删除某些行，如果选定列中的选定单元格具有提及的内容，但我不明白它在哪里失败

i have this script to delete certain rows, if the selected cell in the selected colum has the metioned content but i don't understand where it fails

    function DeleteRowByKeyword() {

  var value_to_check = Browser.inputBox("Enter the keyword to trigger delete Row","", Browser.Buttons.OK);

  //  prendo quello attivo
  var DATA_SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var FIRST_COLUMN = Browser.inputBox("Number of Column to look at (eg: for column A enter 1)","", Browser.Buttons.OK);

  ss.toast("removing duplicates...","",-1);

  var dataCopy1 = DATA_SHEET.getDataRange().getValues();
  var deleted_rows = 0;
  var rangeToCopy = '';

  if (dataCopy1.length > 0) {
    var i = 1;

    while (i < DATA_SHEET.getMaxRows() - deleted_rows) {
  if ((dataCopy1[i][FIRST_COLUMN]).search(value_to_check) != -1) {

    ss.deleteRow(i);
    deleted_rows++;
  } 
  i++;
    }
  }


  ss.toast("Done! " + deleted_rows + ' rows removed',"",5);
    }

在此先感谢您的帮助

推荐答案

有几点需要改进:

• 请记住，电子表格行和列的编号从1、对于SpreadsheetApp中的所有方法，而javascript数组从 0 开始编号.您需要在这些数字之间进行调整使用两者时的基础.

• Remember that spreadsheet rows and columns are numbered starting at 1, for all methods in the SpreadsheetApp, while javascript arrays start numbering from 0. You need to adjust between those numeric bases when working with both.

String.search() 方法在这里可能是不合适的选择，因为两个原因..search() 将匹配子字符串，所以('Testing').search('i') 找到匹配项；你可能想寻找确切的匹配.此外，.search() 包括对正则表达式的支持匹配，因此用户可能会惊讶地发现他们的输入已被解释作为正则表达式；.indexOf() 可能是更好的选择.

The String.search() method may be an inappropriate choice here, for two reasons. The .search() will match substrings, so ('Testing').search('i') finds a match; you may want to look for exact matches instead. Also, .search() includes support for regex matching, so users may be surprised to find their input interpreted as regex; .indexOf() may be a better choice.

要将操作限制为包含数据的行，请使用 .getLastRow()而不是 .getMaxRows().

To limit operations to rows that contain data, use .getLastRow() instead of .getMaxRows().

删除行时，电子表格dataRange的大小会得到更小；你确实考虑到了这一点，但是因为你在循环达到最大尺寸，代码会因这个要求而变得复杂.您可以通过从最大值开始循环来简化事情.

When deleting rows, the size of the spreadsheet dataRange will get smaller; you did take that into account, but because you're looping up to the maximum size, the code is complicated by this requirement. You can simplify things by looping down from the maximum.

列号的输入容易出错，所以让用户输入一个信;您可以将其转换为数字.

The input of a Column Number is error-prone, so let the user enter a letter; you can convert it to a number.

您尚未为 ss 定义值(在此函数内).

You had not defined a value for ss (within this function).

这是更新后的代码:

function DeleteRowByKeyword() {

  var value_to_check = Browser.inputBox("Enter the keyword to trigger delete Row", "", Browser.Buttons.OK);

  var matchCol = Browser.inputBox("Column Letter to look at", "", Browser.Buttons.OK);
  var FIRST_COLUMN = (matchCol.toUpperCase().charCodeAt(0) - 'A'.charCodeAt(0) + 1);  // Convert, e.g. "A" -> 1

  //  prendo quello attivo (get active sheet)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var DATA_SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  ss.toast("removing duplicates...", "", -1);

  var dataCopy1 = DATA_SHEET.getDataRange().getValues();
  var deleted_rows = 0;

  if (dataCopy1.length > 0) {
    var i = DATA_SHEET.getLastRow();  // start at bottom

    while (i > 0) {
      if (dataCopy1[i-1][FIRST_COLUMN-1] === value_to_check) {

        ss.deleteRow(i);
        deleted_rows++;
      }
      i--;
    }
  }

  ss.toast("Done! " + deleted_rows + ' rows removed', "", 5);
}

这篇关于根据行中单元格的内容删除谷歌电子表格中的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！