Sheets powered Go Links on App Engine

Each company I've worked for has some surprising tech that really formed the backbone of the business. In Microsoft, it was Excel. Everything ran off Excel.

At Google, it's Go Links. Want to submit expenses? Just type go/expenses. Want to create an NDA? Just go/nda. It's really simple, and really powerful. You get redirected to the real URL yet you never have to remember it.

I also rely on it in my personal life, outside work. It's like a short URL generator, but I have full control over what the short URL is, on my own domain.

Instead of remembering the long-winded YouTube link to a specific video that I share frequently, I just point people to go.my-own-domain.com/yt. Or my Meet meeting links. Or a short link to the survey I'd like friends to complete.

It's got the added benefit that I can change where it points over time, so if I want to point people to a new video, the same link can suddenly point to a new video.

And then Google announced that it's deprecating the feature for Google #Workspace customers from 31 March 2021, so I needed an alternative.

This morning, I built a nice solution with just a few lines of code.

I created a subdomain with my domain ISP, and mapped it to App Engine.

When a browser requests go.mydomain,com/LI, App Engine receives the request and look up the short link (e.g. LI) from a Google Sheet, find the corresponding URL and send an HTTP 302 redirect to the target address (e.g. Linked.com).

This provides a simple solution to the users of my short links while Google Sheets provides a really simple user interface for me to manage the short codes and associated expanded URLs. It works well enough for my use case and is wonderfully simple and fast. Yet it might be worth using Cloud Datastore, Cloud SQL and/or Memory Store if you needed highly performant solution.

With a simple few python lines (of this non-programmer!), I got this all working:

from flask import Flask, request, redirect
import logging, requests
import gspread
from oauth2client.service_account import ServiceAccountCredentials


app = Flask(__name__)


@app.route('/', defaults={'path': ''})
@app.route('/<path:path>')
def catch_all(path):


    # The ID and range of the spreadsheet.
    MASTER_SPREADSHEET_ID = '<insert your own Google Sheet ID here>'    


    # define credentials needed to read the trix
    scope = ['https://www.googleapis.com/auth/spreadsheets.readonly']
    creds = ServiceAccountCredentials.from_json_keyfile_name('secret-key.json', scope)   
    client = gspread.authorize(creds)


    # Open List Sheet in Wedding planning
    sheet = client.open_by_key(MASTER_SPREADSHEET_ID).get_worksheet(0)
    
    try:
        cell = sheet.find(path)       
        the_url = sheet.cell(cell.row, cell.col+1).value
        return redirect(the_url)
    except:  #not found
        logging.warning("404 Not found " + path)    
        return 'Error 404: Could not find ' + path, 404

There were a couple of other steps I had to take too:

• Authenticate the Spreadsheet to the App Engine project (and add the sheet ID in the code above)
• You'd also need the bare minimum boilerplate requirements.txt and app.yaml files deployed to App Engine

And that's it. Three cheers for the power and simplicity of Google Cloud.