Retrieve row from a table (with a specific value) and insert specific row to a new sheet

Question

I'm new and couldn't figured it out. This is the Scenario

1)I have one table where i have 3 column, one of those columns has a category value.

2)I'm working on a script where I select the range from the table and look up for that value in that specific column.

3)If the row has that value im looking for, i want to copy the whole row of the table and paste it on a new spread sheet.

function myFunction() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

var range =sheet.getRange('B1:B35');

var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");

var verduras ='verduras';

var rule = SpreadsheetApp.newDataValidation().requireTextEqualTo('Category').copy();

range.setDataValidation(rule); range.copyValuesToRange(destination, 1, 2, 1, 2);

}

SOLUTION:

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TableWithInformation");
  var columnToSearch = 2; // B column 
  var lastrow = sheet.getLastRow();
  Logger.log(sheet.getLastRow());
  var range =sheet.getRange(1,columnToSearch,sheet.getLastRow(),columnToSearch);
  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report");
  var values = range.getValues();

  //Destination  
  var numberOfColumns = 3;
  var numberOfRows = 1 ;
  var startColumn = 1;
  var startRow = 2;

  // equivalent to destination.getRange('A1:C3');     
  var count = 0
  var destRow = destination.getLastRow()+1
  for(var i=0;i<values.length;i++){
    Logger.log("i is now: " +i);
    Logger.log("Current row value: " +values[i][0]) 

    if(values[i][0] == 'Category'){
      Logger.log("*** Value found in cloumn: " +values[i][0] +"**** \n Count: " +i);
      count++;

      var rangeToCopy = sheet.getRange((i+1), 1,numberOfRows,numberOfColumns);

      var destinationRange = destination.getRange(destRow, startColumn,numberOfRows,numberOfColumns);
      destRow++;
      Logger.log("Next Destination row: " + destRow);
      rangeToCopy.copyTo(destinationRange);


    }
  }

}

rckrd rckrd · Accepted Answer · 2014-11-08T12:34:18

I don't really get what you are trying to achieve with the data validation rule.

I would use a loop and search for the value the you are looking for. If the value is found in the range, then copy the value or values for that range.

Below is an example, you need to modify it to copy the whole row if this what you want. This will only copy the value in column B .

EDIT:

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var columnToSearch = 2; // B column 

  Logger.log(sheet.getLastRow());
  var range =sheet.getRange(1,columnToSearch,sheet.getLastRow(),columnToSearch);
  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  var values = range.getValues();

  //Destination  
  var numberOfColumns = 3;
  var numberOfRows = 1 ;
  var startColumn = 1;
  var startRow = 1;
                         // equivalent to destination.getRange('A1:C3');     
  var destinationRange= destination.getRange(startRow, startColumn,numberOfRows,numberOfColumns); 
  var count = 0;
  for(var i=0;i<values.length;i++){
    Logger.log("i is now: " +i);
    Logger.log("Current row value: " +values[i][0]) 
    if(values[i][0] == 'Category'){
      Logger.log("*** Value found in cloumn: " +values[i][0] +"**** \n Count: " +i);
      count++;
      var rangeToCopy = sheet.getRange(i, 1,numberOfRows,numberOfColumns);
      rangeToCopy.copyTo(destinationRange);
    }
  }

}

I think it will be easier if you learn the some basic control structures and javascript. There are many good resources online, for example:

http://www.codecademy.com/en/tracks/javascript

Also have a look at google reference pages to understand what the specific google apps functions does.

https://developers.google.com/apps-script/reference/spreadsheet/range

Retrieve row from a table (with a specific value) and insert specific row to a new sheet

2 Answers