Partial access to spreadsheets using Python

Spreadsheets are everywhere. From small businesses to large organizations, the world literally runs on Excel.

But what happens when you need to give users partial access? Just viewing their own info, hidden in single row of a single document, for example.

That’s where Python comes in handy with an elegant solution.

In this Python-only tutorial we’ll show how to build a Smart Form that enables authenticated access to user-specific spreadsheet info. For this, we’ll parse the data using pandas and use Abstra's out-of-the-box authentication.

‍

First things first, pip install abstra on your terminal to run the local editor.

‍

Getting started

We’ll use this example spreadsheet, which contains data from a rewards points service. We’ll start off by saving it to the same folder as our Abstra project locally. Download the file to follow along.

‍

Then, we’ll create a new Smart Form and import the necessary libs - the abstra.forms lib to generate UI and the pandas lib to handle the data.

We begin building by calling pandas’ read_excel function to read our spreadsheet as a DataFrame.

from abstra.forms import *
import pandas as pd

df = pd.read_excel("mock_data.xlsx")

‍

Email authentication

To guarantee each user accesses their own info safely, we’ll use email authentication. The user will input their email and receive a verification code - quick and easy, no password required. Then, we’ll match this address with our spreadsheet’s emails to check whether there’s a corresponding entry.

user = get_user()
email = user.email

result = df.loc[df['Email'] == email]

‍

‍

Handling and displaying the data

if there’s an email match, we can get the party started 🎈

There are multiple ways to display information to a user in Abstra - let’s check out a few.

We can use a pandas function to collect every info we need from the spreadsheet, and display it in markdown format.

def get_cell(info):
    return df.loc[df['Email'] == email, info].item()

if email in result.values:
    name = get_cell("Name")
    points = get_cell("Points")
    redeem_date = get_cell("Redeem date")

		display_markdown(f"""
## Hey {name}!
So far you've accumulated **{points}** points.
They can be reedemed from =={redeem_date}== onwards.""")

‍

Another option is to display all of the user’s details (i.e. everything on his row) in a table view. For that, we can use the display_pandas widget. And last but not least, this table can be transformed into an .xlsx file of its own to be downloaded by the user via display_file.

Let’s create a Page to show all these options together:

def get_cell(info):
    return df.loc[df['Email'] == email, info].item()

if email in result.values:
    name = get_cell("Name")
    points = get_cell("Points")
    redeem_date = get_cell("Redeem date")

    new_df = result.transpose()
    output = new_df.to_excel("output.xlsx")  

    Page().display_markdown(f"""
## Hey {name}!
So far you've accumulated **{points}** points. They can be reedemed from =={redeem_date}== onwards.""") \
        .display("Here is your info in spreadsheet format.") \
        .display_pandas(new_df) \
        .display_file("output.xlsx", download_text="Download it here") \
        .run()

‍

And here’s the nifty output:

‍

Finally, just in case the inputted email can’t be found, we’ll let the user know with simple if/else logic:

else:
    display('Sorry, can\'t find this user!')

‍

Users authenticated, data extracted from the spreadsheet and displayed in many formats - our work here is done 😎

‍

Sharing the tool

To share the Form, we’ll hit Deploy and send the generated URL to anyone who needs access.

Check out the full code below:

from abstra.forms import *
import pandas as pd

def get_cell(info):
    return df.loc[df['Email'] == email, info].item()

df = pd.read_excel("mock_data.xlsx")

user = get_user()
email = user.email

result = df.loc[df['Email'] == email]


if email in result.values:
    name = get_cell("Name")
    points = get_cell("Points")
    redeem_date = get_cell("Redeem date")

    new_df = result.transpose()
    output = new_df.to_excel("output.xlsx")  

    Page().display_markdown(f"""
## Hey {name}!
So far you've accumulated **{points}** points.
They can be reedemed from =={redeem_date}== onwards.""") \
        .display("Here is your info in spreadsheet format.") \
        .display_pandas(new_df) \
        .display_file("output.xlsx", download_text="Download it here") \
        .run()

else:
    display('Sorry, can\'t find this user!')

‍

Try this form out for yourself here.

Access Abstra to start creating your own projects today!