Google Spreadsheet:Retrieve row from a table (with a specific value) and insert specific row to a new sheet -

Google Spreadsheet:Retrieve row from a table (with a specific value) and insert specific row to a new sheet -

i'm new , couldn't figured out. scenario

1)i have 1 table have 3 column, 1 of columns has category value.

2)i'm working on script select range table , value in specific column.

3)if row has value im looking for, want re-create whole row of table , paste on 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 destination.getrange('a1:c3'); var count = 0 var destrow = destination.getlastrow()+1 for(var i=0;i<values.length;i++){ logger.log("i 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); } } }

i don't trying accomplish info validation rule.

i utilize loop , search value looking for. if value found in range, re-create value or values range.

below example, need modify re-create whole row if want. re-create 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 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 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 easier if larn basic command structures , javascript. there many resources online, example:

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

also have @ google reference pages understand specific google apps functions does.

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

google-apps-script google-spreadsheet