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 Cloud’s out-of-the-box authentication.
First things first, log in to Abstra Cloud to access your workspace.
We’ll use this example spreadsheet, which contains data from a rewards points service. We’ll start off by importing it to our workspace’s file system to access it inside our Smart Form. Download the file to follow along.
Then, we’ll create a new Smart Form and import the necessary libs - the hackerforms 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.
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.
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 Cloud - 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.
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:
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:
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 the “Share” button in our editor and send the generated URL to anyone who needs access.
Check out the full code below:
Try this form out for yourself here.
Log in to start creating your own projects, and get free credits to launch today!