根据另一个特定单元格区域中的值，从特定范围中删除Google电子表格中的行 [英] Delete row in google spreadsheet from a specific range based on values from another specific cell range

问题描述

我需要一些有关脚本的帮助.

I need a little help with my script.

首先，这是ss的链接: https://docs.google.com/spreadsheets/d/1TgG64irjoxkV9wKlT5bEnaNEWlGyOlNxVoLxOmQZ2BA/edit?usp=sharing

First, here is a link to the ss: https://docs.google.com/spreadsheets/d/1TgG64irjoxkV9wKlT5bEnaNEWlGyOlNxVoLxOmQZ2BA/edit?usp=sharing

我要基于工作表概述"(范围:C4:C12)中的值从工作表"Team 1"(范围:A15:A41)中删除行.

I want to delete rows from sheet "Team 1" (range: A15:A41), based on values from sheet "Overview" (range: C4:C12).

基本上，如果在A15:A41中找到C4:C12中的任何值，则应删除找到的行.

Basically, if any value from C4:C12 is found in A15:A41, the row where is found should be deleted.

例如对于我的ss:C4 = 3，这意味着应该在工作表"Team 1"中删除第17行.

Ex. for my ss: C4 = 3, that means in sheet "Team 1", row 17 should be deleted.

到目前为止，我的脚本是

My script so far:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName('Overview');
  var s2 = ss.getSheetByName('Team 1');
  var r1 = s1.getRange("C4:C12");
  var v1 = r1.getValues();
  var r2 = s2.getRange("A2:A28");
  var v2 = r2.getValues();

for (var i = v2.length - 1; i >= 0; i--)
  if (v2[i][0] == v1)
    s2.deleteRow(i + 2);
};

我不知道如何比较整个范围内的数据，而不仅仅是单个单元格中的数据.

I don't know how to make it to compare data from the whole range, not just from a single cell.

谢谢.

推荐答案

我相信您的目标如下.

• 您要删除表 Team 1 的行，方法是将表 Overview 中的单元格"C4:C12"与单元格"A2:A28"中的单元格进行比较表格 Team 1 .

• You want to delete the rows of the sheet Team 1 by comparing the cells "C4:C12" in the sheet Overview with the cells "A2:A28" in the sheet Team 1.

为此，该修改如何?

• 在这种情况下，需要比较"C4:C12"和"A2:A28"的所有值.但是，在循环中将每个 v2 的值与所有 v1 的值进行比较时，处理成本将很高.因此，首先，它创建一个用于搜索值的对象，然后使用该对象运行搜索.

• In this case, it is required to compare all values of "C4:C12" and "A2:A28". But when each value of v2 is compared with all values of v1 in the loop, the process cost will be high. So at first, it creates an object for searching the values, and the search is run using the object.

修改脚本后，它如下所示.

When your script is modified, it becomes as follows.

for (var i = v2.length - 1; i >= 0; i--)
  if (v2[i][0] == v1)
    s2.deleteRow(i + 2);

至:

var obj = v1.reduce((o, [e]) => (Object.assign(o, {[e]: true})), {});
for (var i = v2.length - 1; i >= 0; i--)
  if (obj[v2[i][0]])
    s2.deleteRow(i + 2);

参考文献:

• reduce()
• Object.assign()

这篇关于根据另一个特定单元格区域中的值，从特定范围中删除Google电子表格中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！