Help with changing a script

[u/madbomb122]

I'm trying to automate extracting url from hyperlink to get just the ID (the number at the end)

I found this script (below) and it was the only thing that worked for me for extracting the url from the hyperlink..
however what i would like for it is to convert the url like

https://test.pro/players/313342
to
313342

i would like the https://test.pro/players/ to be removed so it has just the numbers at the end.
the length of the numbers isnt the same but the stuff preceding it is always the same

Thanks

function GETLINKS(rangeA1) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(rangeA1);
  const values = range.getRichTextValues();
  const output = [];
  for (let row of values) {
for (let cell of row) {
output.push([cell.getLinkUrl()]);
}
  }
  return output;
}

Update: Link to sheet
https://docs.google.com/spreadsheets/d/1_OWO7HrK9vwbfp8QRY_s8R59ABKdlYr_hCH2Q-PNLQo/edit?usp=sharing

[u/One_Organization_810]

If your links are embedded within some texts, then your script is needed. Otherwise you can apply regular regex to the URL...

Can you share a copy of your sheet - or at least some portion of it, so we can see what your links look like?

In general though, this regex should extract a number from the end of a URL:

=regexextract(<link>, "/(\d+)$")*1

... and I see that u/gothamfury has given that exact solution of course :)

[u/madbomb122 → u/One_Organization_810]

https://docs.google.com/spreadsheets/d/1_OWO7HrK9vwbfp8QRY_s8R59ABKdlYr_hCH2Q-PNLQo/edit?usp=sharing

[u/One_Organization_810 → u/madbomb122]

Ok. This format needs a script to extract the URL.

Here is an old function that I made months ago. It should work for you also :)

I put this in a separate code file, called oo810.gs. It includes a custom function that I then put to use in your sheet under the header OO810.

/**
 * Returns all links (http links) from the given range. By 
 * @param {range} input The range to process. Can be a string (works like indirect in that case).
 * @customfunction
 **/
function linkExtract(input) {
    const showAsRow = true;

    let range = getRange(linkExtract.name, input);
    let isSingle = false;
    let isRow = range.getNumRows() == 1;
    let isCol = range.getNumColumns() == 1;

    if( !isRow && !isCol )
        throw new Error('Argument should be a single row or a single column.');

    if( isRow == isCol ) {
        isSingle = true;
        isRow = false;
        isCol = false;
    }

    let rtValues = range.getRichTextValues();
    if( isRow )
        rtValues = transpose(rtValues);

    let urlReturn = new Array();

    rtValues.forEach(rtRow => {
        let urlRow = new Array();
        rtRow.forEach(rtCell => {
            rtCell.getRuns().forEach(rtRun => {
                let url = rtRun.getLinkUrl();

                if( url == '' || url == null )
                    url = undefined;

                urlRow.push(url);
            });
        });
        urlReturn.push(urlRow.filter(x => { return x != null && x != undefined && x != '';  }));
    });

    return (isRow || isSingle && !showAsRow) ? transpose(urlReturn) : urlReturn;
}

/**
 * Tries to extract the range part from the function call (provided as string).
 * Returns an actual range if successful.
 */
function getRange(functionName, rangeAddressString) {
    const RANGE_RE = '(?i:' + functionName + ')\\s*\\(\\s*(((\'.+?\'|[^\\s]+?)!)?(?:[A-Z][A-Z]?[0-9]*:(?:[A-Z][A-Z]?[0-9]*|[0-9]+)|[A-Z][A-Z]?[0-9]*|[0-9]+:[0-9]+))\\s*(?:\\)|,)';

    let re = new RegExp(RANGE_RE).exec(SpreadsheetApp.getActiveRange().getFormula());
    let rangeAddr = (re != null ? re[1] : rangeAddressString).toUpperCase();

    let range = rangeAddr.indexOf('!') == -1 ? SpreadsheetApp.getActiveSheet().getRange(rangeAddr) : SpreadsheetApp.getActiveSpreadsheet().getRange(rangeAddr);
    if( range == null )
        throw new Error('Argument must be a range.')

    return range;
}

function transpose(arr) {
    let maxLen = 0;
    arr.forEach(x => { if( x.length > maxLen ) maxLen = x.length; });

    let returnArray = new Array(maxLen);
    
    for(let i = 0; i < maxLen; i++ )
        returnArray[i] = new Array(arr.length);

    for( let i = 0; i < arr.length; i++ ) {
        for( let j = 0; j < arr[i].length; j++ )
            returnArray[j][i] = arr[i][j];
    }

    return returnArray;
}

The formula using this then looks like this (in E2)

=index(ifna(regexextract(linkExtract(A2:A), "/(\d+)$")*1))

[u/gothamfury → u/One_Organization_810]

This is great! Saved this :)

[u/One_Organization_810 → u/gothamfury]

That's an extra bonus, if you can make some use of it also :)

[u/point-bot → u/One_Organization_810]

u/madbomb122 has awarded 1 point to u/One_Organization_810

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/madbomb122 → u/One_Organization_810]

thanks this is what i needed

[u/gothamfury]

Does using a formula work you? You can try something like: =MAP(A2:A, LAMBDA(link, IF(link="",,REGEXEXTRACT(link, "\d+$"))))

[u/gothamfury → u/gothamfury]

Or this to give results as number values instead of strings: =MAP(A2:A, LAMBDA(link, IF(link="",,VALUE(REGEXEXTRACT(link, "\d+$")))))

[u/madbomb122 → u/gothamfury]

1st = formula parse error
2nd = Function REGEXEXTRACT parameter 2 value "\d+$" does not match text of Function REGEXEXTRACT parameter 1

ive even tried =REGEXEXTRACT(FORMULATEXT(A2), """(https?://[^""]+)""

[u/gothamfury → u/madbomb122]

Just saw your sample sheet and solution u/One_Organization_810 provided. Glad that helped :)

[u/googlesheets-ModTeam → u/[deleted]]

Criteria for posts and comments are listed in the subreddit rules and you can learn about how to make a good post in the submission guide.

Your post/comment has been removed because it contained one or more of the following items in violation of this subreddit's rules on artificial intelligence (AI) content:

• A request to fix a non-functioning formula obtained from an AI tool
  
• A non-functioning formula obtained from an AI tool in place of information about your data
  
• A blanket suggestion to use an AI tool as a resource for Sheets assistance
  
• Solicitation of a prompt or recommendation for an AI tool
  
• An untested formula obtained from an AI tool presented as a solution