How to remove part of a sentence based on the frequency of a particular letter in using apps script?-CodePudding

There is a URL I scraped to google Sheets from the internet. I want to delete an original part of it. There I found a pattern like this. The symbol "/" is used several times in every URL. I want to remove the characters up to the fourth occurrence of "/".

The URL inside the cell is like this https://www.wensite.com/username/how-to-remove-part-of The result should be like this. how-to-remove-part-of

Since the URL is wrapped, remove it and copy the link using the code below.

function urlText(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet().getActiveSheet();
  var lC = ss.getLastColumn();

  for(i=2; i<lC; i  ){
  var cellVal = ss.getRange(2,i).getRichTextValue().getLinkUrl();

  var count = cellVal.length;
  var find =cellVal.lastIndexOf("/");
  var remove =cellVal.replace().endsWith(find);
  
  //ss.getRange(9,i).setValue(cellVal);


  }
}

CodePudding user response：

From your answer of I solved the problem using this code, when I saw it, I'm worried that getRange, getRichTextValue() and setValue are used in a loop, the process cost becomes high. So, as another approach, I would like to propose the following modification by reducing the process cost. Please think of this as one of several modifications.

Modified script:

function urlText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(2, 2, 1, sheet.getLastColumn() - 1);
  var values = range.getRichTextValues()[0].map(c => {
    var url = c.getLinkUrl();
    return url ? (url.split("/")[4] || null) : null; // or return url ? url.split("/").pop() : null;
  });
  range.offset(7, 0).setValues([values]);
}

I think that when this script is run, the same result as the script in your answer is obtained.

References:

CodePudding user response：

I solved the problem using this code

function urlText(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet().getActiveSheet();
  var lC = ss.getLastColumn();

  for(i=2; i<lC 1; i  ){
  var cellVal = ss.getRange(2,i).getRichTextValue().getLinkUrl();

  var count = cellVal.length;
  var find =cellVal.lastIndexOf("/");
  var remove =cellVal.slice(find 1,count);
  
  ss.getRange(9,i).setValue(remove);

   Logger.log(remove);

  }
}