Google Sheets custom functions

Use Google Sheets custom functions to connect to your datawarehouse enable Self-Service analytics.

What are we trying to do?

Create a custom google function for google sheets (eg.) WEEKLY_ORDERS("2023-01-02").
The function calls an API on your backend, with the date parameter.
The result is displayed in the google sheets cell.

Why?

Data Enginners and analysts usually need to push data to google sheets on a regular basis.
Either new sheets have to be created, updated or reformatted
Monitoring if these sheets are being updated or not etc.
Easier if spreadsheet users can just get fresh data they need from the warehouse directly.

What you'll need

A google cloud account, This method can't be used on public free accounts. A GCP project needs to be connected.
Basic JS & Python familiarity, with python virtualenv installed
Ngrok to tunnel your local machine

FastAPI

Active a virtual environment and install fastapi

mkdir custom_functions
cd custom_functions
virutalenv venv
source venv/bin/activate
pip install fastapi

Create a file called `main.py` with the following content

from fastapi import FastAPI
import json

app = FastAPI()
@app.get("/")
async def root():
    return {"message": "Google sheets custom functions"}

Next run

uvicorn main:app --reload

You'll see it start

INFO:     Will watch for changes in these directories: ['your/path/is/here']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [7685] using WatchFiles
INFO:     Started server process [7689]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Now, in a new terminal tab or window or check the url in the browser

curl http://127.0.0.1:8000

It returns

{"message":"Google sheets custom functions"}

Now, we have the basic api endpoint running that returns a key value pair but, it only works our machine and we cannot call it from inside google sheets. So, instead of deploying it somewhere on production, we tunnel it from our localhost

To do that we use ngrok.

Ngrok

It allows us to tunnel our local traffic to public and with a generous free tier. Follow the steps to install and start here

Once it is ready we can start it on port 8000

ngrok http --domain=ngrok-free-domain.app 8000

This will start a tunnel on our localmachine that proxies all traffic on https://ngork-free-domain to the port 8000 of our machine. So, we can access the api from public.

Now, we setup the google sheets part

Google Sheets

Create a new google sheet, we'll call it CustomFunctions

Navigate to Extentions->Apps Script

Inside the appscript file, paste this code.

function MYFUNC()
{
options = {
// The Ngrok freeplan requires we send the ngrok-skip-browser-warning header
// Without that it won't work
     "headers" : {
        "ngrok-skip-browser-warning": "69420"}
   }
  URL_STRING = "https://ngrok-free-domain.app";

  var response = UrlFetchApp.fetch(URL_STRING, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  return data.message
}

Now, in the google sheet where this function was created

Now, let's create an api that returns some data from FastAPI

from fastapi import FastAPI
import json

app = FastAPI()

WEEKLY = {
    "2023-01-02": {
        "Orders": 100,
        "Cancellations": 10,
        "Returns": 5,
    },
    "2023-01-09": {
        "Orders": 150,
        "Cancellations": 11,
        "Returns": 6,
    },
    "2023-01-16": {
        "Orders": 170,
        "Cancellations": 14,
        "Returns": 3,
    },
    "2023-01-23": {
        "Orders": 180,
        "Cancellations": 17,
        "Returns": 6,
    },
    "2023-01-30": {
        "Orders": 149,
        "Cancellations": 9,
        "Returns": 2,
    },            
    
}


@app.get("/")
async def root():
    return {"message": "Google sheets custom functions"}

@app.get("/weekly/{week}")
async def read(week: str):    
    week.replace('"', '')
    data = WEEKLY.get(week, None)
    print(week)
    print(type(week))
    return {week: data}

On another terminal tab or window, check the api

curl http://127.0.0.1:8000/weekly/2023-01-02
{"2023-01-02":{"Orders":100,"Cancellations":10,"Returns":5}}

Now, we create a function in the google sheets apps script code editor.

/**
* Calling our python API for weekly orders
* @param {string} input The value to call
* @return The input is called
* @customfunction
*/

function WEEKLY_ORDERS(input)
{
options = {
// The Ngrok freeplan requires we send the ngrok-skip-browser-warning header
// Without that it won't work
     "headers" : {
        "ngrok-skip-browser-warning": "69420"}
   }
  input = String(input).replace(/"/g, '');
  URL_STRING = "https://ngrok-free-domain.app/weekly/"+input;

  var response = UrlFetchApp.fetch(URL_STRING, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  return data[input]['Orders']
}

Then, in the Google Sheets we can try the function

Now, we add 2 more functions to our google sheets apps script

/**
* Calling our python API for weekly cancellations
* @param {string} input The value to call
* @return The input is called
* @customfunction
*/

function WEEKLY_CANCELLATIONS(input)
{
options = {
// The Ngrok freeplan requires we send the ngrok-skip-browser-warning header
// Without that it won't work
     "headers" : {
        "ngrok-skip-browser-warning": "69420"}
   }
  input = String(input).replace(/"/g, '');
  URL_STRING = "https://ngrok-free-domain.app/weekly/"+input;

  var response = UrlFetchApp.fetch(URL_STRING, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  return data[input]['Cancellations']
}

/**
* Calling our python API for weekly returns
* @param {string} input The value to call
* @return The input is called
* @customfunction
*/

function WEEKLY_RETURNS(input)
{
options = {
// The Ngrok freeplan requires we send the ngrok-skip-browser-warning header
// Without that it won't work
     "headers" : {
        "ngrok-skip-browser-warning": "69420"}
   }
  input = String(input).replace(/"/g, '');
  URL_STRING = "https://ngrok-free-domain.app/weekly/"+input;

  var response = UrlFetchApp.fetch(URL_STRING, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  return data[input]['Returns']
}