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
*
*/
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
*
*/
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,
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./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./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,
- stackoverflow./a/1789952/5297207 – Talya S Commented Mar 20, 2016 at 16:05
- I'm not exactly sure on how to apply that – Oneezy Commented Mar 20, 2016 at 16:45
- I need to only look for substrings, not the string then the substring. I'm playing with it – Oneezy Commented Mar 20, 2016 at 16:47
- There is no functionality within Excel that is going to help you with this. – user4039065 Commented Mar 20, 2016 at 17:28
- I know.. I'm using Google Sheets. And and a custom script (that I'm trying to make work) – Oneezy Commented Mar 20, 2016 at 17:30
2 Answers
Reset to default 7Using 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 plete 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./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./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);
};
function deleteRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var toDelete = [];
var re = new RegExp('old','gi');
for (var row = 0; row < values.length; row++) {
for(var column = 0;column<values[row].length;column++){
if (re.exec(values[row][column])){
toDelete.push(row); } } }
for(var deleteRow = toDelete.length-1; deleteRow >= 0;deleteRow--){
sheet.deleteRow(toDelete[deleteRow]+1);
}
SpreadsheetApp.flush();
};