CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Joe
Joe

Reputation: 37

Google Apps Script - copy cells into a new sheet on condition

I am having a spreadsheet with thousands of rows and some entries have "#N/A" in the first column as a result of a lookup. I want those rows to be copied entirely on a new sheet.

Example:

ID Other Data
#N/A row1
123 row2
#N/A row3
#N/A row4
#N/A row5
456 row6

The new sheet should contain the rows with "#N/A" in the ID field

ID Other Data
#N/A row1
#N/A row3
#N/A row4
#N/A row5

Upvotes: 1

Views: 1398

Answers (3)

Kristkun
Kristkun

Reputation: 5953

You can refer to this sample code on how to filter data from sheet and write the to a new sheet using apps script

Sample Code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Filter N/A', 'FilterNA')
      .addToUi();
}

function FilterNA(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();

  var data = sheet.getDataRange().getDisplayValues();
  var newData = data.filter(row => ["ID","#N/A"].includes(row[0]));
  newSheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);
}

What it does?

  1. Create a custom menu to trigger the copy function
  2. Select the active sheet and create a new sheet using insertSheet()
  3. Read the data in your source sheet using getDataRange() and getDisplayValues()
  4. Filter the array using Array.filter(), in this example I created a list of strings that I want to filter ("ID" and "#N/A") then check if current row's column 1 is either ID or N/A using Array.includes()
  5. Write the filtered data in the new sheet.

Output:

enter image description here

Upvotes: 0

J. G.
J. G.

Reputation: 1832

connect the sheets with a query, is how I would do it:

=query(importrange("https://docs.google.com/spreadsheets/d/ID",
"'SHEET 1'!A2:Z"),
"select * where Col1 = '#N/A'")

You'll have to ok the connection between the sheets.

Upvotes: 1

Alistair McEvoy
Alistair McEvoy

Reputation: 351

Just use the FILTER formula: =FILTER(A2:B100, A2:A100="#N/A")

Upvotes: 1

Related Questions