如果与单元格值匹配，应用程序脚本可从Google表格中删除行？ [英] Apps Script to Delete Row from Google Sheet if Match with Cell Value?

问题描述

我正在处理我的脚本，以便删除我的Google表格文件的POHistory标签中与我的POTemplate标签的单元格E9中的值相比，第一个单元格中的值匹配的任何行。虽然我在执行命令时看不到任何响应。不知道这里缺少什么。本质上，我收到一条错误消息，指出我想要删除的行是超出界限。感谢您的期待。

 函数deleteOld（）{
 var app = SpreadsheetApp; 
 var orderSheet = app.getActiveSpreadsheet（）。getSheetByName（POTemplate）; 
 var historySheet = app.getActiveSpreadsheet（）。getSheetByName（POHistory）; 
 var poNO = orderSheet.getRange（E9）。getValue（）; 
 var lastRow = historySheet.getLastRow（）; 
 var myRange = historySheet.getRange（A2：A+ lastRow）; 
 var data = myRange.getValues（）; 
 for（i = 0; i< data.length; i ++）{
 if（data [i] [0]！= poNO）{
 continue; 
} else {
 historySheet.deleteRow（i）; 
}}} 
  

解决方案

删除一行， i 值不再对应相同的范围。例如，你有一个包含5个元素的数组：

  [A，B，C，D，E] 
  

如果您删除元素 0 ，则会得到 p>

  [B，C，D，E] 
  

现在你增加你的迭代器，使得 i ++; // i = 1 ，所以你操作的下一个值不是B，而是C。

你可以通过运行

 函数test（）{
 var letters = [A，B ，C，D，E]; 
 for（var i = 0; i< letters.length; i ++）{
 Logger.log（i：+ i +|| Letter：+ letters [i]）; 
 letters.shift（）; 
 
 
 
 
 $ b要修复你的代码，添加一个 row 独立于数组迭代器的变量 i 。
 
 
 
试试这个作为模板： 
 
 
 函数deleteOld（）{
 var app = SpreadsheetApp; 
 var orderSheet = app.getActiveSpreadsheet（）。getSheetByName（POTemplate）; 
 var historySheet = app.getActiveSpreadsheet（）。getSheetByName（POHistory）; 
 var poNO = orderSheet.getRange（E9）。getValue（）; 
 var lastRow = historySheet.getLastRow（）; 
 var myRange = historySheet.getRange（A2：A+ lastRow）; 
 var data = myRange.getValues（）; 
 var row = 1; 
 for（i = 0; i< data.length; i ++）{
 if（data [i] [0]！= poNO）{
 row ++; //转到下一行
 continue; 
} else {
 historySheet.deleteRow（row）; 
 row--; //删除一行
} 
} 
} 
  
 
I am working for my script to delete any rows in the POHistory tab of my Google Sheet file that have a matching value in their first cell when compared to the value found in Cell E9 of my POTemplate tab. I am not seeing any response when executing the command, though.  Not sure what is missing here.  I, essentially, am getting an error message stating the rows I am trying to delete are "out of bounds".  Thank you for looking.    
function deleteOld() {
  var app = SpreadsheetApp;
  var orderSheet = app.getActiveSpreadsheet().getSheetByName("POTemplate");
  var historySheet = app.getActiveSpreadsheet().getSheetByName("POHistory");
  var poNO = orderSheet.getRange("E9").getValue();
  var lastRow = historySheet.getLastRow();
  var myRange = historySheet.getRange("A2:A" + lastRow); 
  var data = myRange.getValues();
 for(i = 0; i < data.length; i++){
   if(data[i][0] != poNO){
   continue;
   }else{
  historySheet.deleteRow(i);
 }}}

解决方案
Because you're deleting a row, the i value no longer corresponds to the same range. For example, you have an array with 5 elements:
[A, B, C, D, E]
If you delete element 0, you'll get
[B, C, D, E]
Now you increment your iterator so i++; // i = 1 and so the next value that you'll operate on is not "B", but instead "C".


You can try yourself by running
function test() {
  var letters = ["A", "B", "C", "D", "E"];
  for (var i=0; i<letters.length; i++) {
    Logger.log("i : " + i + " || Letter: " + letters[i]);
    letters.shift();
  }
}
To fix your code, add a row variable that is independent of your array iterator i.

Try this as a template:
function deleteOld() {
  var app = SpreadsheetApp;
  var orderSheet = app.getActiveSpreadsheet().getSheetByName("POTemplate");
  var historySheet = app.getActiveSpreadsheet().getSheetByName("POHistory");
  var poNO = orderSheet.getRange("E9").getValue();
  var lastRow = historySheet.getLastRow();
  var myRange = historySheet.getRange("A2:A" + lastRow); 
  var data = myRange.getValues();
  var row = 1;
  for(i = 0; i < data.length; i++) {
    if(data[i][0] != poNO){
      row++; // Go to the next row
      continue;
    } else {
      historySheet.deleteRow(row);
      row--; // Deleted a row
    }
  }
}


                        
这篇关于如果与单元格值匹配，应用程序脚本可从Google表格中删除行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！