Published :
June 2, 2023

15 minutes

Building our CRM in Neptyne

The first version of many tools is a spreadsheet. Maybe it’s a CRM, a financial model, or a project tracker. Spreadsheets often hit the sweet spot of power, flexibility, and familiarity to get the job done. At Neptyne, we believe that more often than not, the best version of many tools is a spreadsheet. This might be hard to imagine: while the spreadsheets of today are great for crunching numbers and producing charts, they tend to fall short when you need a script to import some data, or add useful interactivity. Google has Apps Script, Excel has macros, and other offerings like Airtable tackle some of this. But each becomes critically limited when you need to write general-purpose code.

As I write this, Neptyne is in its early stages, and there’s a waitlist to start using it. One goal of utilizing a waitlist is to prioritize users from whom we can learn: if you schedule a 15-minute chat with us to talk about your use case, we’ll give you early access to the product.

We also want to reach out proactively to folks who’ve signed up to the waitlist. In doing so, we want to track who we’ve emailed and who sent them an email. We also want to see who has used our Calendly link to speak with us. This sure is starting to sound like a CRM… Can we build it all into a Neptyne spreadsheet?

To recap, we need to pull into our spreadsheet:

  • Waitlist data from our Postgres database
  • User emails from firebase (if they’ve created an account already)
  • Event data from Calendly

With Neptyne, each of these is a simple as writing a little bit of Python.

Connecting to Postgres

Our first data source is a Postgres DB running on GCP’s Cloud SQL. There are a number of ways to connect; we’ll use the Cloud SQL Python connector as demonstrated in this Colab notebook.

Our code looks boils down to this:


B_INSTANCE = "[your db instance]"
DB_USER = "[postgres db user name]"
DB_NAME = "[postgres db name]"


def get_db():
    service_account = json.loads(nt.get_secret("service account json"))
    credentials = Credentials.from_service_account_info(service_account)


    connector = Connector(credentials=credentials, enable_iam_auth=True)


    def getconn():
        return connector.connect(DB_INSTANCE, "pg8000", user=DB_USER, db=DB_NAME)

n order to authenticate with Google Cloud, we need service account credentials. You can create these through the GCP Console, and download a key file. The key is stored using Neptyne’s secrets feature, which lets you inject sensitive data into your code, such as API keys or certificates. When creating this service account user, we can use PostgreSQL’s fine-grained permissioning to make sure it can only read from the relevant tables.

With that in place, we can query our database:



def get_waitlist_users()
    with get_db().connect() as conn:
        res = conn.execute(
            text(
                """
                select
                    firebase_uid,
                    created
                from waitlist
                order by created desc;
                """
            )
        )


    return [*res]:

This query returns to us all users who’ve signed up for our waitlist, and we now have a function we can run in our spreadsheet:

No alt text provided for this image
A spreadsheet function returning User IDs and waitlist join dates


But our spreadsheet isn’t so useful with just a list of firebase UIDs. We’d prefer to see their name and email, and we need this to match our data with Calendly. Let’s pull from Firebase!

Connecting to Firebase

To pull our user data from Firebase, we’ll use the Firebase admin SDK for Python. We can use the same service account credentials, as long as we permission that service account user for our Firebase data as well. The Firebase connection looks like this:


def init_firebase()
    try:
        firebase_admin.initialize_app(
            credential=firebase_admin.credentials.Certificate(
                json.loads(nt.get_secret("service account json"))
            )
        )
    except ValueError:  # firebase.init_app throws ValueError if it's called twice
        pass




def get_firebase_users():
    init_firebase()
    return [
        (
            u.uid,
            u.display_name,
            u.email,
        )
        for u in list_users().iterate_all()
    ]:

We can use this to pull in Firebase data:

No alt text provided for this image
A spreadsheet function to pull users from Firebase

We now have the UIDs of the users in the waitlist, and the names/emails of those users from Firebase. Last on our list of data to pull is Calendly events.

Querying Calendly’s API

Calendly is wonderfully useful for scheduling events with people you don’t typically share a calendar with. As it turns out, they also have a very useful API! Unlike the Cloud SQL and Firebase connections above, we don’t have any special SDKs/packages to install: Calendly’s API is simple REST:


CALENDLY_ORG = "[uuid of our Calendly organization]"




def get_calendly_api(endpoint, **params):
    token = nt.get_secret("calendly token")
    return requests.get(
        f"https://api.calendly.com{endpoint}",
        headers={
            "Authorization": f"Bearer {token}",
            "Content-Type": "application/json",
        },
        params=params,
    ).json()




def get_calendly_events():
    events = get_calendly(
        "/scheduled_events",
        organization=CALENDLY_ORG,
        min_start_time=utc_now_iso(),
    )
    res = []
    for event in events["collection"]:
        uuid = event["uri"].split("/")[-1]
        attendees = get_calendly(f"/scheduled_events/{uuid}/invitees")["collection"]
        assert len(attendees) == 1
        attendee = attendees[0]
        start_date = datetime.datetime.fromisoformat(event["start_time"][:-1])
        res.append((attendee["email"], attendee["name"], start_date))

    return res

It looks a bit more complicated because we need to make two types of API calls: the first to get all future scheduled events, and the second to get the details about the event invitee (the person who booked time with us). Note the use of nt.get_secret() once again to access our Calendly API token. We can use this new function to update our sheet:

No alt text provided for this image
A spreadsheet function to pull events from Calendly

We now have all the data we need to track our waitlist and Calendly appointments in one spreadsheet. To put it all together in a single table, we could use a Python function or a simple VLOOKUP. I’ll leave that as an exercise to the reader 😉.

What’s next?

We’ve built our first mini-CRM in a Neptyne spreadsheet. As the needs of our workflows grow, adding a new data source or interactive elements is as simple as writing a bit of Python.

Want to check out Neptyne? Sign up for our waitlist here and you’ll show up in this very spreadsheet! And don’t forget to use the Calendly widget to book a chat — we’d love to hear from you.