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

问题描述

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

  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（例如：for column A enter 1），，Browser.Buttons.OK）; 
 
 ss.toast（删除重复的...，， -  1）; 
 
 var dataCopy1 = DATA_SHEET.getDataRange（）。getValues（）; 
 var deleted_rows = 0; 
 var rangeToCopy =''; 
 
 if（dataCopy1.length> 0）{
 var i = 1; （（dataCopy1 [i] [FIRST_COLUMN]）。search（value_to_check）！= -1）{$ b（$ i 
 
 while（i< DATA_SHEET.getMaxRows（） -  deleted_rows） 
 $ b ss.deleteRow（i）; 
 deleted_rows ++; 
} 
 i ++; （完成！+ deleted_rows +'rows removed'，，5）; 
 
 
 
 
 ss.toast 
} 
  

提前感谢您的帮助

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

• 在这里， String.search（）方法可能是不合适的选择，对于
  有两个原因。 .search（）将匹配子字符串，因此
  （'Testing'）。search（'i'）找到一个匹配;您可能想要查找确切的
  匹配。此外， .search（）包含对正则表达式
  匹配的支持，因此用户可能会惊讶地发现他们的输入将
  解释为正则表达式; .indexOf（）可能是更好的选择。

使用 .getLastRow（）
而不是 .getMaxRows（）。



• 删除行时，电子表格 dataRange 的大小将会小
  ;您确实考虑了这一点，但由于您将
  循环至最大尺寸，因此此代码会使代码复杂化。
  您可以通过从最大值循环来简化操作。




• 列号的输入容易出错，所以让用户输入
  字母;您可以将其转换为数字。 您尚未为 ss 定义值（在此范围内
  
  
  

  以下是更新的代码：

   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）; //转换，例如A - > 1 
   
   // prendo quello attivo（获得活动工作表）
   var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
   var DATA_SHEET = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
   
   ss.toast（删除重复项...，，-1）; 
   
   var dataCopy1 = DATA_SHEET.getDataRange（）。getValues（）; 
   var deleted_rows = 0; （dataCopy1.length> 0）{
   var i = DATA_SHEET.getLastRow（）; 
   
   if //从底部开始
   
   while（i> 0）{
   if（dataCopy1 [i-1] [FIRST_COLUMN-1] === value_to_check）{
   
   ss.deleteRow（i）; 
   deleted_rows ++; 
  } 
   i--; （完成！+ deleted_rows +'rows removed'，，5）;
  
  } 
   
   ss.toast 
  } 
    

  
  

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

  thanks in advance for any help

  解决方案

  There are a few things to be improved:

  • 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.

  • 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.

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

  • 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.

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

  Here's the updated code:

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

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