如果某些“字词”在Google表格中删除，在单元格中找到 [英] Delete row in Google Sheets if certain "word" is found in cell
问题描述
我拥有超过3000行的Google表格。有些行包含不相关的词。所以我需要一种批量删除这些词的方法。例如，单元格将包含如下内容：
＃| Product
-------------------------------
1 |酷新产品
2 |旧产品
3 |产品旧
我想删除包含单词old的所有行。 p>
我发现了一个执行一半工作的脚本，但它需要单词来匹配整个单元格，而不仅仅是单元格的一部分。
以下代码中的第17行是需要调整的内容：
16 |
17 | if（row [1] =='old'）
18 |
以下是代码：
/ **
*删除活动电子表格中B列中包含'word'的行
*有关使用Spreadsheet API的更多信息，请参阅
* https://developers.google.com/apps-script/service_spreadsheet
* /
函数readRows（） {
var sheet = SpreadsheetApp.getActiveSheet（）;
var rows = sheet.getDataRange（）;
var numRows = rows.getNumRows（）;
var values = rows.getValues（）;
var rowsDeleted = 0;
for（var i = 0; i< = numRows - 1; i ++）{
var row = values [i];
if（row [1] =='old'）{
sheet.deleteRow（（parseInt（i）+1） - rowsDeleted）;
rowsDeleted ++;
}
}
};
$ b / **
*为活动电子表格添加一个自定义菜单，其中包含一个菜单项
*，用于调用上面指定的readRows（）函数。
* onOpen（）函数在定义时会在
*电子表格打开时自动调用。
*有关使用Spreadsheet API的更多信息，请参阅
* https://developers.google.com/apps-script/service_spreadsheet
* /
函数onOpen（）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）;
var entries = [{
name：删除B列为'old'的行'，
functionName：readRows
}];
sheet.addMenu（删除行，条目）;
};
它在右上角添加了一个菜单。看起来像这样，
使用 indexOf 通过更改...来获得所需的效果。
：
if（row [1] =='old'）
To：
if（row [1] .indexOf（old）> -1 ）
这里发生了什么：
'indexOf'进入并且发现
的位置是单词old的第一次出现，然后返回一个数字长度
值。如果找不到该单词，结果将为-1。所以，只要
指定的大于> -1，就会是真的。你会很好的！
以后是其他人需要的完整代码，
/ **
*删除活动电子表格中B列中包含'word'的行
*有关使用Spreadsheet API，请参阅
* https://developers.google.com/apps-script/service_spreadsheet
* /
函数readRows（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var rows = sheet.getDataRange（）;
var numRows = rows.getNumRows（）;
var values = rows.getValues（）;
var rowsDeleted = 0;
for（var i = 0; i <= numRows - 1; i ++）{
var row = values [i]; （row [1] .indexOf（old）> -1）{
sheet.deleteRow（（parseInt（i）+1） - rowsDeleted）;
if
rowsDeleted ++;
}
}
};
$ b / **
*为活动电子表格添加一个自定义菜单，其中包含一个菜单项
*，用于调用上面指定的readRows（）函数。
* onOpen（）函数在定义时会在
*电子表格打开时自动调用。
*有关使用Spreadsheet API的更多信息，请参阅
* https://developers.google.com/apps-script/service_spreadsheet
* /
函数onOpen（）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）;
var entries = [{
name：删除B列为'old'的行'，
functionName：readRows
}];
sheet.addMenu（删除行，条目）;
};
I have a Google Sheet with over 3000 rows. Some of the rows contain words that are not relevant..So I need a way to delete these in bulk. For example, cells will contain something like:
# | Product
-------------------------------
1 | Cool new product
2 | Old product
3 | Product that's old
I want to delete all of the rows that contain the word "old".
I found a script that's doing half the job, but it requires the "word" to match the entire cell, not just some of the cell.
Line 17 in the code below is what needs to be adjusted:
16 |
17 | if (row[1] == 'old')
18 |
Here's the code:
/**
* Deletes rows in the active spreadsheet that contain 'word' in column B
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[1] == 'old') {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Remove rows where column B is 'old'",
functionName : "readRows"
}];
sheet.addMenu("Remove Rows", entries);
};
It adds a menu at the top right..Looks like this,
Using the indexOf trick, I've managed to get the desired effect by changing...
This:
if (row[1] == 'old')
To This:
if (row[1].indexOf("old") > -1)
What's happening here:
The 'indexOf' goes in and finds the position of the first occurrence of the word "old", then returns back a number length value. If it doesn't find the word, the result will be -1. So, as long as you specify greater than "> -1", it will be true..and you'll be good!
Here's the complete code if anyone else needs this in the future,
/**
* Deletes rows in the active spreadsheet that contain 'word' in column B
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[1].indexOf("old") > -1) {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Remove rows where column B is 'old'",
functionName : "readRows"
}];
sheet.addMenu("Remove Rows", entries);
};
这篇关于如果某些“字词”在Google表格中删除，在单元格中找到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
