Skip to content

Instantly share code, notes, and snippets.

@shaneapen
Created June 17, 2022 16:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save shaneapen/3b385e9bfda71be3eab24e6e1557050f to your computer and use it in GitHub Desktop.
Save shaneapen/3b385e9bfda71be3eab24e6e1557050f to your computer and use it in GitHub Desktop.
Google AppScript: Copy rows from one sheet to another based on cell value
const ss = SpreadsheetApp.getActive();
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Custom Functions')
.addItem('Filter by text', 'filterByText')
.addToUi();
}
const filterByText = () => {
const ui = SpreadsheetApp.getUi()
const sourceSheet = ss.getSheetByName('FY21-22');
// var response = ui.prompt("Enter the search text");
// response = response.getResponseText().toLowerCase();
var response = "paypal"
// Create new sheet
let targetSheet = ss.getSheetByName(response);
if (targetSheet != null) {
ss.deleteSheet(targetSheet);
}
targetSheet = ss.insertSheet();
targetSheet.setName(response);
const rows = sourceSheet.getDataRange().getValues();
const targetData = []
for (let row of rows) {
try {
let col = row[2] || '';
col = col.toLowerCase();
if (col.includes(response)) {
targetData.push(row)
}
} catch (err) {}
}
console.log(targetData.length)
const tCellRange = targetSheet.getRange(1,1,targetData.length, targetData[0].length);
tCellRange.setValues(targetData)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment