Bulk Link Shortening using YOURLS in Google Sheets

  • Thread starter Thread starter Sushubh
  • Start date Start date
  • Replies Replies 12
  • Views Views 6,085
It is pretty simple indeed. Took me less than 5 minutes to create a crude Google Sheet that can make bulk short links for me using YOURLS.

The core code is this:

Code: 
=importData(concatenate("https://domain.com/yourls-api.php?signature=SIGNATURE&action=shorturl&format=simple&url=",A1,""))

Put this in A2 cell. And paste the link you want to shorten in A1 cell. A2 cell should update to show the short link.

To create short links in bulk, just copy the content of A2 to more rows in the A column and repeat the process.

It's not pretty but it works!

--

Update:
Turns out this is not good enough. There are a few more modifications I have made to this script now that I have used it for a few days. First one is to not get Google to process the function if the cells are empty. Secondly, YOURLS continue to have issues with non-encoded URLs. It simply removes the arguments after & in the URL and creates a short link. I am not sure if encoding the URL fixes it for all links but it seems to work on the URLs I tried. I could not find a direct way to encode URLs in Google Sheets without creating a custom script. Here is the script that I used:

Code: 
function encode(value) {
  return encodeURIComponent(value);
}

And then I added a second column to convert all links to encoded format. This A2 cell contain this data:

Code: 
=encode(A1)

And then I added this code to A3 cell:

Code: 
=IF(A1="","***",importData(concatenate("https://domain.com/yourls-api.php?signature=SIGNATURE&action=shorturl&format=simple&url=",A1,"")))

Hopefully this works!
 
Last edited:
Click to expand...
that's exactly what i was searching. can you tell me if i would like to also use a keyword to generate the short url, how that formula would look like?
 
cannot really find the solution for this from the documentation. plus it is kind of hard to use custom keywords in google sheets. you would have to prefill custom keyword column before the actual url column.

this page: YOURLS/YOURLS does not seem to provide information on passing a custom keyword through the url.
 
it is available if you use a programming language. in google sheets, we are using basically running a link that returns the short url. the pdf you linked does not use that way.
 
ok found the code.

https://YOURLS/yourls-api.php?signature=SIGNATURE&format=simple&action=shorturl&url=URL&keyword=KEYWORD
 

=importData(concatenate("http://yourdomain.com/yourls-api.php?signature=SECRET&format=simple&action=shorturl&url=YOURURL.com&keyword=keyword"))

that worked

cool i just found it as well 🙂
 
Last edited by a moderator:
Yeah. I guess you would need to add a new column and incorporate it in the formula.
 
tbh, i did not create this from scratch. had to follow a bunch of guides. would have posted the source links if had kept track. but yeah... it has come handy.

do know that google has massive throttling in place. you cannot just magically convert hundreds of urls. the first 50-100 urls are converted fast. after that it slows down a lot.
 
I do understand, but its part of a automated process. Not too many URL would be generated automatically at the same time so I guess it will be not a problem
 
you could do this in google script

Code: 
function getShortLink(url) {
    try {
        if (url == undefined) {
            throw 'url is empty or is not a valid url!'
        }
        options = {muteHttpExceptions: true};
        let content = UrlFetchApp.fetch('YOURWEBADDRESS/yourls-api.php?signature=SIGNATURE&action=shorturl&format=simple&url=' + encodeURI(url),options);
        return content.getContentText();
    } catch (e) {
        return 'An error occured: [ ' + e + ' ]';
    }
}

then for your formula it would be
Code: 
=getShortLink(A1)
Where A1 is the full web address.
 

Top