Google AppScript: Copy row from one sheet to another based on cell value

This handy script will help you copy rows from one sheet to another based on a cell value. It’s particularly useful when analysing reports or documents such as bank statements.

Watch this quick video to see the script in action.

AppScript code to copy rows from one sheet to another based on cell value

1. Open the Google sheet and navigate to Extensions –> App Script
2. Copy & paste the following code into the editor.
3. Switch back into the sheet. Place the cursor at the header of the column you want to search the text.
4. A new menu option called Custom Funtions will be visible. Click on the Filter by Text menu under this menu. Enter the text and click Ok.
5. If matching text is found under this column, a new sheet will be created with just the rows containing the search text.

Note: This is just a script that I made to serve my usecase. Not much thought have been given to refine it. Feel free to share your ideas and suggest improvements in the comments below. And I’m aware that you can do this pretty much with the sheet queries alone but the App Script solution should be more flexible and serves as a blueprint for you to make changes.

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)
}

You may also like our free Crypto Portfolio Tracker sheet for WazirX.