CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Trapper 444
Trapper 444

Reputation: 15

Google Spreadsheet: Search for a word and then change it

I have two Sheets, in Sheet1 I have 2 values, the value "FROM" (which is the word i am trying to find and modify) and the value "TO" (which is the value i am trying to change it too), and in the Sheet2 I have a Column with a bunch of names trying to modify the one with the same value as FROM into TO, I have been attempting to modify the code myself but I've been unable to fix it so I am asking for help if any of you could help me with this

 function NChange() {
   var ISheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CO duties');
   
   var from = ISheet.getRange('M8').getValue();  
   var to = ISheet.getRange('N8').getValue();
   var sheeta = ISheet.getRange('M11').getValue();
   var Sheets = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheeta);
   var into = setValue(to) ;
   var tf = Sheets.createTextFinder(from);
   var all = tf.findAll();
  
  for (var i = 0; i < all.length; i++) {
    Logger.log(Sheets, all[i].getSheet().getName(), all[i].getA1Notation(), all[i].getValue());
  }
}

Upvotes: 0

Views: 54

Answers (1)

Cooper
Cooper

Reputation: 64082

Here's a simple example:

The code simply creates a text finder for every word in the from list and replaces it with the corresponding word in the to list. It will find all occurrences of the from word in the target sheet.

Sheet1:

From|To
one|1
two|2
three|3
four|4
five|5
six|6
seven|7
eight|8
nine|9
ten|10

Sheet2:

From
one
two
three
four
five
six
seven
eight
nine
ten

The code:

function NChange() {
  const ss=SpreadsheetApp.getActive();
  const datash=ss.getSheetByName('Sheet1');
  const fromlist=datash.getRange(2,1,datash.getLastRow()-1,1).getValues().flat();
  const tolist=datash.getRange(2,2,datash.getLastRow()-1,1).getValues().flat();
  const targetsh=ss.getSheetByName('Sheet2');
  fromlist.forEach(function(w,i){//w is the from word and i is the index
    let f=targetsh.createTextFinder(w).replaceAllWith(tolist[i])
  });
}

textFinder

flat()

Here's a version for multiple sheets:

function NChangeForMultipleSheets() {
  const sheetlist=["Sheet2","Sheet3"];
  const ss=SpreadsheetApp.getActive();
  const datash=ss.getSheetByName('Sheet1');
  const fromlist=datash.getRange(2,1,datash.getLastRow()-1,1).getValues().flat();
  const tolist=datash.getRange(2,2,datash.getLastRow()-1,1).getValues().flat();
  const targetsh=ss.getSheetByName('Sheet2');
  fromlist.forEach(function(w,i){
    sheetlist.forEach(function(name){
      let s=ss.getSheetByName(name)
      let f=s.createTextFinder(w).replaceAllWith(tolist[i]);
    });
  });
}

Upvotes: 1

Related Questions