replaceText wildcard in Google Sheet script

Question

I've been trying to work on replacing some text within a Google script but it's not producing what I'd like. At present I am using Cameron Roberts' script from here - How do I replace text in a spreadsheet with Google Apps Script? to make my replacements however I've not got it quite right.

Using that code I am trying to replace the word 'values' with '1. values' however if I run the code multiple times it produces '1. 1. values' etc as it just finds the 'values' string. What I'd like is a wildcard which just searches for 'values' and then puts '1. values' in but I can't seem to grasp the regular expression syntax well enough to fix it.

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    replaceInSheet(sheet,'values','1. values');
}

function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();

  //loop over the rows in the array
  for(var row in values){

    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      return original_value.toString().replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }

  //write the updated values to the sheet
  sheet.getDataRange().setValues(values);
}

well, `1. values` contains `values` so it takes that leaving `1. ` which will be `1. 1. values`. What do you want it to happen? Do nothing? — Robin Gertenbach, Nov 21 '16 at 16:34
Thanks for the reply. I'd like to replace 'values' with '1. values' but obviously each time this runs, as you say, I get an additional '1. ' prefixed.I guess what I'd like is the whole string which contains 'values' to be replaced to just show '1. values'. — davey4444, Nov 21 '16 at 21:01

score 0 · Accepted Answer · answered Nov 21 '16 at 19:38

0

If a small change in formatting is acceptable to you then you can capitalize one of the 'values', ex:

replaceInSheet(sheet,'values','1. Values');

answered Nov 21 '16 at 19:38

utphx

1,287
1
8
19

Of course! Thanks for this, it should work perfectly. – davey4444 Nov 21 '16 at 21:01

score 0 · Answer 2 · answered Nov 21 '16 at 21:02

This adds a prefix if the prefix doesn't already exist. Simulates a negative look behind that's why the reversing is going on

function addPrefix(s, find, prefix) {
  function reverse(x) {return x.split("").reverse().join("");}

  var sr = reverse(s);
  var findr = reverse(find);
  var prefixr = reverse(prefix);
  var findRegexpr = new RegExp(findr + "(?!" + prefixr + ")");

  return reverse(sr.replace(findRegexpr, findr + prefixr))
}

replaceText wildcard in Google Sheet script

2 Answers2