I have the following script which is required to remove characters in a string leaving only the characters which follow the ">" character. For example, "GOOD > WEEKEND". When the script runs the output should be "WEEKEND". As there is one space after ">" i'm using slice(v.indexOf(">") 2) The problem is everytime the script runs it removes another two more characters. To get round this I'm trying to include an if function so that the characters will only be removed if ">" is detected in the string with regex. Any help will be appreciated to get it to run successfully. An example can be viewed at https://docs.google.com/spreadsheets/d/1xBBbwA9j3mcR3iBTekGPGil92c6iInKyYXcq6NQpgpg/edit?usp=sharing Thank you

function UpdateZoneRate(){
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('JOBS'); // adjust this to the name of your sheet
  var zoneRate =  sh.getRange('A2:A' sh.getLastRow()).getValues();
  
    for (var i=0;i<zoneRate.length;i  ){
    
   if (zoneRate[i][0].match(/([>])\w /) )

      var zoneValues = sh.getRange('A2:A' sh.getLastRow()).getValues().flat().map(v=>[v.slice(v.indexOf(">") 2) || null])};

  sh.getRange(2,53,zoneValues.length,1).setValues(zoneValues);
}

CodePudding user response：

You can do that with a plain vanilla spreadsheet formula without resorting to scripting, like this:

=arrayformula( regexreplace(A2:A52, ".*> ?(.*)", "$1") )

To do the same with a script, try this:

function updateZoneRate() {
  const range = SpreadsheetApp.getActive().getRange('JOBS!A2:A');
  const zoneRate = range.getDisplayValues()
    .flat()
    .map(value => [value.replace(/.*> ?(.*)/, '$1')]);
  range.offset(0, 1).setValues(zoneRate);
}