Google-sheets – Reverse Shortened URL to Get the Original URL Before the Shortening in Google Sheets

This script will do the trick:

/**
 * Replaces some spaces with newlines in str, specifically, ' a.' becomes '\na.' for any letter 'a'.
 * See http://webapps.stackexchange.com/questions/76035/how-to-add-line-breaks-in-a-cell-before-specific-keywords-in-google-sheets
 */
function InsertLineBreaksInString(str) {
  return typeof(str) === 'string' ? str.replace(/ (\w\.)/g, '\n$1') : str;
}

/** Replaces some spaces with newlines in all the cells in the given range. */
function InsertLineBreaksInCells(range) {
  var vals = range.getValues();

  for (var i in vals) {
    var row = vals[i];
    for (var j in row) {
      row[j] = InsertLineBreaksInString(row[j]);
    }
  }

  range.setValues(vals);
}

/** Replaces some spaces with newlines in all the cells in the current Sheet's active range. */
function InsertLineBreaks() {
  var range = SpreadsheetApp.getActiveRange();
  InsertLineBreaksInCells(range);
}

/** Adds a menu to the Google Sheet. */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Insert Line Breaks', 'InsertLineBreaks')
      .addToUi();
}

To set it up:

1. In Google Sheets, invoke Tools > Script Editor...
2. Paste in this script.
3. Save the script.
4. Go back to the Sheet.
5. Reload the browser page.
6. Confirm the requests for access permissions.

To use it:

1. Select the cells you want to process.
2. Invoke the menu command Custom Menu > Insert Line Breaks.

Explanation and How to learn this stuff

The first part, function InsertLineBreaksInString(str), processes a string by replacing some spaces with newline characters. If the argument is a string, it uses a "regular expression" to look for the pattern SPACE LETTER PERIOD and replace it with NEWLINE LETTER PERIOD. Regular expressions are handy for lots of string-processing work.

This is a matter of JavaScript programming and regular expressions. There are many tutorials on these topics including Khan Academy classes, Codecademy classes, and more. You can experiment right in your browser's JavaScript Console and in online interactive tutorials like Learn JavaScript.

The second part, function InsertLineBreaksInCells(range), uses the first function to process all the cell values in a spreadsheet range. See Google's Quickstart: Macros, Menus, and Custom Functions and Spreadsheet Service reference. Within Google Sheets, you can invoke the menu command Tools > Script Editor... to get to the script editor, and from there to the Help info.

The third part, function InsertLineBreaks(), get the currently selected range in the current sheet, and calls the second function. See the same documentation for this.

The last part, function onOpen(), adds a menu to the Google Sheet so you can invoke your script. This is documented in the Base Service part of the Apps Script reference, with examples, and there's also an example in the Quickstart mentioned above.

This little code makes it possible to create shortened URL's, with the use of Google Apps Script and the URL Shortener API.

Prerequisites

Under resources in the script editor (Tools > Script editor) select the Advanced Google Services and activate the UrlShortener:
Then go to the Google Developers Console and select the URL for the API:
Enable the API and paste the code in to the script editor. Press the bug button to authenticate the script

Code

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Shorten")
    .addItem("Go !!","rangeShort")
    .addToUi()  
}

function rangeShort() {
  var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
  var output = [];
  for(var i = 0, iLen = data.length; i < iLen; i++) {
    var url = UrlShortener.Url.insert({longUrl: data[i][0]});
    output.push([url.id]);
  }
  range.offset(0,1).setValues(output);
}

Explained

The onOpen() function will create a menu item for you to select, having the name Shorten. Every time you open the file this menu item will be added (if you're in the script editor, select the function from the drop-down menu and press the play button. This will execute that particular function and the item will be added to the menu).

Once the menu is created, it will show the menu item Go !!. Select the cell or range that you want to shorten and the script will create the shortened URL's and add them to the sheet.

Screenshot

before

after

Note

The code makes the assumption that, once you've selected either the cell or range to be shortened, that the result will be added to the right of the selected cell/range (OFFSET(0,1)).
If you simply want to overwrite the existing URL, then change the code into this: (OFFSET(0,0)).

Example

I've created an example file for you: Create Shortened URL's

References

1. url-shortener
2. offset(Integer,Integer)
3. onOpen()