Pull data from supabase to google sheets

In this article, you will see how to pull data from your supabase project using Supabase API to a Google Sheet. No matter how many fields and rows you want to pull, this should always work. If not, feel free to contact me so that we can look at your issue.

The code

You can get it here.

Prerequisites

This project assumes that you are already familiar to supabase and Google appscript.

You will need :

the URL of your supabase project
its API KEY (service role or anon one, depending your RLS)

And of course : your Google account.

How often can I refresh data ?

Every minute
Every hour
Every day
At a specific date
On a special event (event-driven triggers)

More informations about Google script triggers here.

CAUTION : be careful of your daily quotas on your Google account. For a free account, you can have 90 minutes of runtime per day (July 2022). Check out the details of Google triggers quotas here.

Motivation

Google Scripts can’t handle postsgresql connections yet, so here is a workaround to pull data everyday at the same hour. We don’t really want a realtime update. Just a snapshot is enough.

Once the data are available on your Google Sheet, you can explore many possibilities, like using Google Data Studio that pulls data from Google Sheets every 15 minutes.

Errors handling

If you find any issue related to the code, please contact me so that I can help you.

Limits and recommandation

Your daily quota from your Google account as stated before
Your supabase max rows limit, which can be changed here :

I highly recommand you to :

set this parameter according to the average amount of data you are going to pull (here for me is ~3.5k rows so I have 500 as an offset)
NOT set this parameter too high (as low as possible actually) or/and to create a strong RLS to make sure that :
- your supabase API is not over loading (I already tried with 10k rows and it’s definitely NOT a good idea)
- your users don’t over pull your data

Have fun with your data !