Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to replace part of a string using the slice method with an IF condition

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);
}

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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);
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading