CODEWITHSUNDEEP
google-sheets
John
John

Reputation: 13

Creating a Macro in google spreadsheet to search and then write text

What I am trying to accomplish is I would like to search for a term in one cell, if that cell has the term write text to another cell. My specific example would be I would like to search for the term 'DSF' in column 4. If I find 'DSF' it would then write 'w' in column 5 & write '1.2' in column 3. This is searched per row.

I do understand the the .setvalue will write the needed text, but I do not understand how to create a search function. Some help would be greatly appreciated.

EDIT

Here is the code I am working with at the moment. I am modifying it from something I found.

function Recalls()
{
 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 D
   if(data[n][3].toString().match('dsf')=='dsf'){ data[n][4] = 'w'}{ data[n][2] = '1.2'};// if column D contains 'dsf' then set value in index [4](E)[2](C)
 }
 //Logger.log(data)
 //sh.getRange(1,1,data.length,data[3].length).setValues(data); // write back to the sheet
 }

With the Logger.log(data) not using the // It works properly but it overwrites the sheet, which will not work since I have formulas placed in a lot of the cells. Also, Maybe I did not realize this but Is there a way to do a live update, as in once I enter text into a cell it will research the sheet? Otherwise having to 'run' the macro with not save me much time in the long run.

Upvotes: 1

Views: 4361

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

Try this. It runs when the sheet is edited. It only captures columns C,D,&E into the array and only writes back those columns. That should solve overwriting your formulas. It looks for 'DSF' or 'dsf' in column D (or contains dsf with other text in the same cell either case). Give it a try and let me know if I didn't understand your issue.

function onEdit(){
 var sh = SpreadsheetApp.getActiveSheet();
 var lr = sh.getLastRow()// get the last row number with data
 var data = sh.getRange(2,3,lr,3).getValues(); // get only columns C.D,& E.  Starting at row 2 thur the last row
 //var data = sh.getDataRange().getValues();// read all data in the sheet
 for(n=0;n<data.length-1;++n){ // iterate row by row and examine data in column D
 //  if(data[n][0].toString().match('dsf')=='dsf'){
 if(data[n][1].match(/dfs/i)){ //changed to find either upper or lower case dfs or with other text in string.
  data[n][2] = 'w';
  data[n][0] = '1.2'};
  }
  sh.getRange(2,3,data.length,data[3].length).setValues(data); // write back to the sheet only Col C,D,& E
 }

Upvotes: 1

Related Questions