Geek Answers Handbook

Search table column for text in a row and return the result to another column

Using google apps script and spreadsheet, I am trying to do a simple thing, but cannot understand the problem. I have a sheet with an empty column and a column with text. Columns are not next to each other. I am trying to find the text in each cell in one column, and if the text is found, set the cell value in the empty field as “Yes”.

Example (sorry, there is no code - I spent hours with it, but what was so confusing for me is best to give an example):

Column A with text Empty Column F abcd efg hij klmn opq rstu vwxzy Yes

What is the simplest code to find column A for "xyz" and return "Yes" in column F?

I have looked and tried about a dozen different code examples on the Internet and cannot make them work. Appreciate any help with this !!

EDIT (finally hopefully) for my use (I have some helper utilities that get me the column number based on the header name, this code is not included in this, fyi):

 var sskey = SpreadsheetApp.openById('**********************') function otherfunction(){ addCustomValue('POCs', 'Groups', 'Champion', 'Champion', 'Yes'); } function addCustomValue(sheetNamestr, searchColnamestr, writeColnamestr, searchKeystr, writeValstr) { var sheet = sskey.getSheetByName(sheetNamestr); var searchColnum = MyUtilities.getColIndexByName(sheet, 1, searchColnamestr); var writeColnum = MyUtilities.getColIndexByName(sheet, 1, writeColnamestr); var data = sheet.getDataRange().getValues(); for (n=0; n<data.length; ++n) { if (data[n][searchColnum-1].toString().match(searchKeystr)==searchKeystr){ data[n][writeColnum-1] = writeValstr}; } sheet.getRange(1,1,data.length,data[0].length).setValues(data); }

Thanks Serge! Now I can run this on my tables based on any columns and conditions!

+6
source share
2 answers

This is a possible simple script that does what you need. (If your worksheet contains formulas or a custom function, then you need to change it to take it into account)

 function test(){ var sh = SpreadsheetApp.getActiveSheet(); var data = sh.getDataRange().getValues(); // read all data in the sheet for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A if(data[n][0].toString().match('xyz')=='xyz'){ // if column A contains 'xyz' then set value in index [5] (is column F) data[n][5] = 'YES' }; } Logger.log(data) sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet }
+11
source 
 function myFunction() { //Variable to keep track of the sheet var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //Start at row 1, end at the last row of the spreadsheet for(var i=1;i<sheet.getLastRow();i++){ var value = sheet.getRange(i, 1).getValue(); //Compare the value of the cell to 'xyz', if it is then set column 4 for that row to "Yes" if(value == 'xyz'){ sheet.setActiveRange(sheet.getRange(i, 4)).setValue('Yes'); } } }
+3
source

More articles:


All Articles