删除Google表格中的行，具体取决于其他Google表格单元格的值 [英] Delete Row in Google Sheet depending on other Google Sheet cell valuie

问题描述

是否可以编写一个Google脚本，根据给定范围的单元格值在另一个Google工作表中删除Google工作表中的一行?

Is it possible to write a google script that will delete a row in a Google Sheet, based on cell values for a given range, in another google sheet?

我已经对如何在同一个Google表格中执行所有操作进行了一些研究，但是由于我在编写Google表格脚本方面的有限经验，使我无法理解我所描述的内容以及方法.

I've done some research on how to do this all in the same google sheet, but my limited experience with writing google sheet scripts has prevented me from understanding if what I described is possible, and how.

这是我到目前为止拥有的脚本.如果F列中的单元格包含单词"id:123456"，这将删除我的活动电子表格范围内的行.我想要做的是修改此代码，以便如果找到单词"id:123456"，它将在另一个Google表格的另一列中查找，并删除包含"id:123456"的行.

This is a script I have so far. This will delete rows in a range of my active spreadsheet if a cell in Column F contains the word "id:123456". What I'd like to be able to do, is to modify this code so that if the word "id:123456" is found, it will look in another column of another Google Sheet, and delete rows that contain "id:123456".

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1'); // change to your own
  var values = s.getDataRange().getValues();

  for (var row in values)
    if (values[row][6] == 'id:123456')

      s.deleteRow(parseInt(row)+1);
};

推荐答案

我尚未测试此代码，因此您可能需要进行一些修改才能成功运行.但是基本想法很简单:一次打开两个工作表，然后根据第一个工作表中的if在第二个工作表中进行更改.

I haven't tested this code, so you might need to do some modifications to run successfully. But the basic idea is simple: have two sheets open at once, and based on the if from the first sheet make changes in the second sheet.

var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var ss2 = SpreadsheetApp.getByID(0); //Change to the other sheet's ID, or somehow open it.
var s1 = ss1.getSheetByName('Sheet1'); // change to your own
var s2 = ss2.getSheetByName('Sheet1'); // change to your own
var values = s1.getDataRange().getValues();

for (var row in values)
  if (values[row][6] == 'id:123456')
    var other_values = s2.getDataRange().getValues();
      for (var other_row in other_values)
        if (other_values[row][6] == 'id:123456')
          s2.deleteRow(parseInt(other_row)+1);

主要要担心和测试的问题:我复制了您的删除代码，但是由于我们要删除行，除非我们从底部开始，否则可能会开始丢失行. (假设10行中的第4行和第8行包含要删除的内容；您可能最终会删除4，将第8行变为7，但是我们仍然删除第8行，这意味着我们错过了应该删除的行，并且删除了本不应该的行.)

Main thing to be worried about and test: I copied your delete code, but since we're deleting rows, unless we start from the bottom we might start missing. (Suppose rows 4 and 8 out of 10 contain something to be deleted; you might end up deleting 4, which turns 8 to 7, but then we still delete row 8--meaning that we've missed a row we should have deleted and deleted a row we shouldn't have.)

这篇关于删除Google表格中的行，具体取决于其他Google表格单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！