Home > Blockchain >  Find and replace Google Sheet script
Find and replace Google Sheet script

Time:04-19

I am looking to automate the find and replace function of (Ctrl H) with a script linked to a button, the reason being is that on the sheet we use the replaced text is always "zold" "original text" the sheet is a multi page log that would require find and replace on multiple sheets matching the exact text. I can set up a simple find and replace but cant seem to get it to work when referencing the active cell. any pointers would be appreciated.

here's what I have attempted, looking through the documentation I'm not sure how to get the current cell from 'getActiveRange' to be combined into the text that would then replace the search criteria.

function markOld() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var textFinder = sheet.createTextFinder();
  .matchEntireCell(true)
  .matchCase(true)
  .matchFormulaText(false)
  .ignoreDiacritics(false)
  .replaceAllWith('zold' .getActiveRange());
}

CodePudding user response:

Assuming most of your code is correct...

function markOld() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet();
  let cell_value = sheet.getCurrentCell().getValue(); // Find value of current cell https://developers.google.com/apps-script/reference/spreadsheet/sheet#getcurrentcell
  sheet.createTextFinder(cell_value) // <-- Removed the ';' and added cell_value
       .matchEntireCell(true)
       .matchCase(true)
       .matchFormulaText(false)
       .ignoreDiacritics(false)
       .replaceAllWith('zold'   cell_value);
}
  • Related