Tirthya Kamal Dasgupta's blog
Effortlessly collect enquiries: How you can use Vercel Serverless Functions and FastAPI to send enquiry submissions to Google Sheets, for portfolio!

Effortlessly collect enquiries: How you can use Vercel Serverless Functions and FastAPI to send enquiry submissions to Google Sheets, for portfolio!

Tirthya Kamal Dasgupta's photo
Tirthya Kamal Dasgupta
·

17 min read

Table of contents

Preface

Use case

To facilitate user queries on my portfolio site, which is currently being developed, I am offering two options for submitting inquiries: emailing me directly or filling out a simple form. To ensure the best user experience for the latter option, I have the following constraints:

  • Data Storage: Data storage is necessary upon submission of the enquiry form.

  • Scalability: The functionality should be designed to scale efficiently with an increasing number of users. This will ensure optimal performance even as traffic grows.

Data Storage

Solution

I considered using a managed database or submitting the form data directly to a CRM. However, I opted for a lightweight solution that does not impose restrictions on the volume of storage, particularly since I am exploring the free tier and my site has yet to receive visitors. I decided to use Google Sheets as my data storage solution, at least for a couple of years.

Scalability

Possible solutions

  • Google Apps Script: Google offers a JavaScript API that enables seamless interaction with Google Sheets, thereby facilitating a managed backend that runs entirely on Google Infrastructure. For a comprehensive understanding of how to connect to Google Sheets using Google Apps Script, please refer to this GitHub repository: https://github.com/jamiewilson/form-to-google-sheets. If you intend to develop the service using a different programming language in the future, you will not be able to leverage the benefits of Google Apps Script.

  • Leveraging Google Sheets APIs for a managed Serverless Backend: You have the freedom to develop a backend using your preferred programming language and leverage the Google Sheets API to interact with spreadsheets. Once the backend is ready and tested, you can deploy it as a serverless function on the cloud provider of your choice, relieving yourself of the burden of managing it. For further details on interacting with Google Sheets API, please visit https://developers.google.com/sheets/api/guides/concepts.

  • In addition to the solutions we have discussed, you may find it beneficial to explore other resources available on the internet.

Solution Implemented

I have developed my backend using FastAPI (Python). While there are numerous cloud providers available, I chose to explore the capabilities of Vercel Serverless Functions, a remarkable platform provided by Vercel. By leveraging this solution, I was able to deploy my web service effortlessly within minutes. This platform utilizes AWS Lambda for function deployment, and novices in cloud computing will appreciate the simplicity of the deployment process. Since my portfolio site is already hosted on Vercel, I found it convenient to utilize their services for this project.

[Note]

To the best of my knowledge, I have tried to incorporate the industrial best practices while working on this project.

Getting started

Prerequisites

Creating a Google Cloud project

Creating a service account and obtaining credentials

  • Create a service account for your project by referring to this link: https://cloud.google.com/iam/docs/service-accounts-create.

  • Grant Editor role to the service account.

  • Create a key for the service account by following the instructions mentioned in this link: https://cloud.google.com/iam/docs/keys-create-delete. Make sure to select the key type as JSON.

  • To secure the JSON file, store it in a safe location. While Google's libraries directly read the credentials from this file, we'll be using the popular gspread library instead. This library provides a neat wrap-around for Google's Python libraries and enables us to pass the values in a dictionary. This allows us to read the values as environment variables, create a dictionary by passing those values, and supply it directly to the library. In my opinion, this is a more secure way to perform authentication. For more information, take a look at their GitHub repository: https://github.com/burnash/gspread.

Creating a Google Sheet and sharing it with the service account

[Note]

Our web service deployment will involve three distinct environments, namely, Development, Staging, and Production. If you have completed the last two steps, which are Creating a Service Account and obtaining credentials and Creating a Google Sheet and sharing it with the Service Account for the Development environment, I request you to perform the same set of actions for the remaining two environments.

Running the service

├── .idea
├── examples/
│ └── submit_enquiry.py
├── server/
│ ├── __init__.py
│ ├── api.py 
│ ├── enquiry_submitter.py
│ ├── env_vars_loader.py
│ ├── model.py
│ └── routes.py
├── .dockerignore
├── .gitignore
├── Dockerfile
├── main.py
├── README.md
├── requirements.txt
└── vercel.json

Project directory structure

  • Create a file named .env at the root of this directory, after which it will look like this.
├── .idea
├── examples/
│ └── submit_enquiry.py
├── server/
│ ├── __init__.py
│ ├── api.py 
│ ├── enquiry_submitter.py
│ ├── env_vars_loader.py
│ ├── model.py
│ └── routes.py
├── .dockerignore
├── .env
├── .gitignore
├── Dockerfile
├── main.py
├── README.md
├── requirements.txt
└── vercel.json
  • Create all the required environment variables as specified below.

Environment variables specifications

ENVIRONMENT

Description: This variable is used to indicate the environment to which you will deploy the web service. Value for the Development environment: DEV Value for the Preview environment: STAGING Value for the Production environment: PROD

GS_PRIVATE_KEY

Description: The value of this variable is the private key named private_key that is found in the JSON file holding the credentials. Value for the Development environment: <your_private_key_for_dev_environment> Value for the Preview environment: <your_private_key_for_staging_environment> Value for the Production environment: <your_private_key_for_production_environment>

GS_CLIENT_EMAIL

Description: The value of this variable is the client email key named client_email that is found in the JSON file holding the credentials. Value for the Development environment: <your_client_email_for_dev_environment> Value for the Preview environment: <your_client_email_for_staging_environment> Value for the Production environment: <your_client_email_for_production_environment>

GS_TOKEN_URI

Description: The value of this variable is the token URI key named token_uri that is found in the JSON file holding the credentials. Value for all environments: <your_token_uri_for_all_environments>

SPREADSHEET_KEY

Description: The value of this variable is the ID of the spreadsheet that was noted earlier. Value for the Development environment: <your_spreadsheet_key_for_dev_environment> Value for the Preview environment: <your_spreadsheet_key_for_dev_environment> Value for the Production environment: <your_spreadsheet_key_for_dev_environment>

WORKSHEET_TITLE

Description: The value of this variable is the title of the worksheet that was noted earlier. Value for all environments: <your_worksheet_title_for_all_environments>

TIMESTAMP_FORMAT

Description: The value of this variable is any timestamp format that allows the system to format the date before populating the sheet. For example, %dth %B, %Y at %I:%M:%S %p. Value for all environments: <your_preferred_timestamp_format_for_all_environments>

  • The .env file should look like this:
ENVIRONMENT=DEV
GS_PRIVATE_KEY=-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDO8GvJzfrssnZ3 muw1cLlQh/nG0MFdRyO/6kCXhTw/vGX0aiFshxsmZ3wqMNS8NQMrQosXDJ4ogQy4 YJYgUZOi6UZzb6mZTD6TMCJ6IWNTSY6H9aJkPzF7DBg+pkn1u+JrTV34M30wPG7P o+rgslfWh9kzq3EKyY+8zvQFFI7MDRcAvCwZtW+GvAcFudzKyac23uFk+8bHJtsB 1xnyrj+K70qwJ0fhmFKzf+DWnl0Wh9a3jD8HSY92HJ/0Rc+0wZtM8pNofF0vHwhR bz24l1B/oYk+91KvAVnbyPfJ3ztq+enFLPNQILn2OsJX4h4YZyYsYKk+UweS6gFn fUZokjqrAgMBAAECggEAF4Xm4Q2s/SKDT6O4H6OscS6URdUrnK0T6iqGvRzN9zN1 MfYgeKjoh82cTkP/iR0B2QV7yU6Ub8d6pAsU3qPGuVzZU6PZuVgOkfD5uc5v5ES5 Gdjxaz1gNUX9Y5aOvNxzEK2JgCwpyFHyJhIv/SjGGWQlOD4i4X9LbZprllBZAB8G p3w3q99i89cAVzt+P8dLZaRi9Q2vZ/WnN/8DpJLwq3i99c0yJWfAC8ovFJb9LPQ2 vZlWz+8SfnS0kD+fJFvL+M0B2z4t4YXt4zvGZ1HtI7rSy10rDfJ5h5l5JhxHrnTp c9Xg19DDm+mGvjLaJQ7AIBRnC28Q7kmnJ/6dDWB6UQKBgQD7VzTKhC+8v7yAZDe0 DFV2w0RfFgVzZll/Nig8u6zq3X9pUc5LR72/W5C5o5+kfckexrOn78QaZo/SOZEr /OxrXpl1fhmQI/jbjycjnAPg81wDFEDjS/+08I1zKNW8G4f4gacme4t21N03CysW xNnfq3NOONST70vZ/UEWb8I2QwKBgQDcfB\n-----END PRIVATE KEY-----\n
GS_CLIENT_EMAIL=xyz-service-account@xyz-945098.iam.gserviceaccount.com  
GS_TOKEN_URI=https://oauth2.googleapis.com/token
SPREADSHEET_KEY=T3qJF4f7VwvC8W1xdgLbYRnkE6X0KZI9uOMApmDhSzjN5ti2yUPrsaoBcH 
WORKSHEET_TITLE=Enquiries
TIMESTAMP_FORMAT=%Y-%m-%d %H:%M:%S (%Z)
  • Please note that some of the values for the variables provided above are for demonstration purposes only and should be replaced with actual values before proceeding further.

Walkthrough of the code

The project follows a typical FastAPI modular project structure. While I won't be going through the code at this time due to time constraints, I would like to direct your attention to the article Routers in FastAPI written by Rushikesh Naik which provides an excellent overview of the directory structure and a sample application written in FastAPI, which our project adheres to.

After familiarizing yourself with this information, I encourage you to examine our project code, paying particular attention to the comments provided within. While a detailed walkthrough of the code will not be provided at this time, I may revisit this in the future if the need arises.

Running locally

For Windows

python -m venv <Name of the virtual environment>

For Linux/Mac OS

python3 -m venv <Name of the virtual environment>
  • Install the necessary dependencies:
pip install -r requirements.txt
  • Start the server:
python main.py
  • The server is expected to start without encountering any problems, and a corresponding message will be displayed in the terminal, like this:
/Users/tirthyakamaldasgupta/portfolio-enquiry-submitter-service/bin/python /Users/tirthyakamaldasgupta/Projects/Production/portfolio/portfolio-enquiry-submitter-service/main.py 
INFO:     Will watch for changes in these directories: ['/Users/tirthyakamaldasgupta/Projects/Production/portfolio/portfolio-enquiry-submitter-service']
INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [58360] using WatchFiles
INFO:     Started server process [58362]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Running on Docker

To run the service on Docker, it is necessary to have Docker Desktop installed on your system if you are using Windows or macOS. However, if you are using Linux, it is essential to install the Docker Engine. Refer to the following links for guidance on installing Docker on your system:

There are two ways to obtain a Docker image of this web service in your system.

Use an existing image

Pull the image from Dockerhub:

docker pull tirthyakamaldasgupta/portfolio-enquiry-submitter-service:v0.0.1

Build the image

To build the image from the Dockerfile located at the root of the project directory, run the following command from the root of the project directory:

docker build --no-cache --tag <your_preferred_image_name> .

For example:

docker build --no-cache --tag tirthyakamaldasgupta/portfolio-enquiry-submitter-service:0.0.1 .

Verifying the existence of the image

To display all images, execute the following command:

docker images

You should receive output similar to the following, indicating that the image has been created:

REPOSITORY                                                 TAG                                                                          IMAGE ID       CREATED             SIZE

tirthyakamaldasgupta/portfolio-enquiry-submitter-service   0.0.1                                                                           8fc225bf215f   52 minutes ago      1.03GB

Creating a container and running

To create a container from this image, run the following command from the root of the project directory:

docker run --detach --env-file <your_environment_file_name> --name <your_preferred_container_name> --publish <host_port>:<container_port> <your image_name>

For example:

docker run --detach --env-file .env --name portfolio-enquiry-submitter-service --publish 8000:8000 portfolio-enquiry-submitter-service
[Note]

The host port can be chosen as per your preference, however, the server inside the container will listen on port 8000. It is possible that I may update the Dockerfile in the future, after which the container port can also be set accordingly.

After executing the command, you will receive a unique ID for the container in response, similar to the following:

030cd38a526ed791f24fda2f09894cac70858c5da8d41d31941e4d963067df73

Use the following command to view all running containers:

docker ps

You will see that a container has been successfully created and appears in the list of containers:

CONTAINER ID   IMAGE                                 COMMAND                  CREATED         STATUS         PORTS                             NAMES

030cd38a526e   portfolio-enquiry-submitter-service   "venv/bin/python mai…"   2 minutes ago   Up 2 minutes   0.0.0.0:8000->8000/tcp            portfolio-enquiry-submitter-service

Viewing logs within a container

To view logs within a container, execute the following command:

docker logs <your_container_id>

You will see that the server is up and running:

INFO:     Will watch for changes in these directories: ['/app']
INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [1] using WatchFiles
INFO:     Started server process [8]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Testing out the endpoint

cURL, being a versatile command-line tool, provides the capability to transmit requests to a server directly from the terminal, eliminating the need for the installation of any heavy API testing tools. We will be using it to test our endpoint, for demonstrative purposes. However, it is worth noting that you can also utilize your preferred API testing tools, such as Postman, SoapUI, and Insomnia, among others, to test the endpoint as well.

Install cURL if it is not already installed on your system. Refer to these links on how to install it in your system:

Paste this command into a new terminal:

curl -i --location --request POST 'http://127.0.0.1:8000/enquiry' \
--header 'Content-Type: application/json' \
--data-raw '{
    "timestamp": 1680249656000,
    "first_name": "Clyde",
    "last_name": "Cronshaw",
    "email": "ccronshaw3@theguardian.com",
    "company": "Topicblab",
    "message": "Nulla tellus. In sagittis dui vel nisl. Duis ac nibh."
}'

It is worth noting that the current timestamp is expressed in the Unix time format, denoted in milliseconds, and it is mandatory for the client (in this instance, the portfolio site) to consume this API to send the same. To convert your current date and time to milliseconds, you can utilize this helpful tool: https://codechi.com/dev-tools/date-to-millisecond-calculators/.

The response you will receive will look like this:

HTTP/1.1 307 Temporary Redirect
date: Mon, 27 Mar 2023 14:51:51 GMT
server: uvicorn
content-length: 0
location: http://127.0.0.1:8000/enquiry/

HTTP/1.1 200 OK
date: Mon, 27 Mar 2023 14:51:51 GMT
server: uvicorn
content-length: 40
content-type: application/json

{"message":"Enquiry added successfully"}%

Please ensure to verify whether a new row containing the anticipated data has been appended to your spreadsheet.

A row has been inserted into the spreadsheet I am utilizing for development, as evident.

A new row with data inserted into Google Sheets spreadsheet

Deploying to Vercel

  • Navigate to your Vercel dashboard by visiting the Vercel website and signing in to your account.

Vercel dashboard

Adding a new project

  • Click on the Add New button.

  • Click on the Project button.

  • If you have signed in to Vercel using GitHub, GitLab, or BitBucket, which is also hosting your forked repository, you will find the option Import Git Repository that displays the desired repository. If you are unable to locate your desired repository, you can search for it by its name. If the repository exists, it will appear in the search results. To import it, click on the Import button. However, if you have used other methods to sign in, you can still import the desired repository by clicking Import Third-Party Git Repository and entering the repository URL. Then click on the Continue button to proceed with the import process.

Importing a Git repository

  • Having already signed in to Vercel using my GitHub account, I will proceed by selecting the portfolio-enquiry-submitter-service repository and clicking on the corresponding Import button.

Deploying the project

Choosing a project name

  • You have the option to either stick with the default project name or select a new name according to your preference. I will stick with the default project name.

  • Don't worry about adding the environment variables for now. We will add them later.

  • Click on the Deploy button.

  • You will notice that the deployment procedure has been initiated.

Deployment initiated

  • The deployment procedure will be running, which may take some time.

Running deployment

  • To view the execution steps, expand the Building section.

Building project

  • Once the deployment is successful, you will be redirected to a page that confirms the same.

Deploying successfully

  • Click on the Continue to Dashboard button to navigate to the project dashboard.

Project dashboard

  • You will see that domains have been allocated for your production deployment. However, please note that visiting the first domain may result in a server crash if you do the same now.

Crashing of server

Adding environment variables

  • To access project settings, click on the Settings button.

Accessing project settings

Project settings

  • Previously, we retrieved the environment variables from the .env file while working locally. However, when deploying to Vercel, it securely stores the environment variables for us. To add the necessary variables, click on the Environment Variables section.

No environment variables

  • There are two available methods to add environment variables:

    • Manually adding them one by one.

    • Dragging and dropping an existing .env file into the designated area.

  • We will be proceeding with the former option.

  • Follow these steps to add a new environment variable:

    • Enter the variable name and value in the Key and Value input fields.

    • Select deployment environment(s).

    • Click on the Save button.

  • For variables that have a common value across all deployment environments:

    • Add the variable once.

    • Check all three deployment environments.

  • For variables whose value varies across multiple deployment environments:

    • Add the key which will have the same name for each environment.

    • Check the applicable environment for each key.

    • Enter the corresponding value for each key.

  • Click on the Save button.

Adding a sample environment variable

Sample environment variable added

  • To ensure all environment variables are properly configured across all three deployment environments, please refer to the Environment variables specifications section and add all the variables according to their specifications.

  • Regardless of the order of display on the screen, your final set of environment variables should resemble the following:

The final set of environment variables

  • Return to the project dashboard.

Project dashboard

  • Please click on the link located under the DEPLOYMENT header. This will redirect you to the deployment page.

Deployment page

  • Since we have added new environment variables, please note that the project will need to be redeployed again. To do so, click on the button displaying three dots located on the left-hand side of the Visit link. Then, select Redeploy from the options presented. This will ensure that the updated environment variables are included in the project deployment.

Redeploy project

  • Upon completing the redeployment process, you will receive a confirmation indicating that the deployment has been successful.

Project redeployment confirmation

  • To initiate the redeployment process, click on the Redeploy button. You will then be redirected to a page where you can monitor the progress of the deployment.

Redeployment in progress

  • Wait for the deployment process to complete. Once finished, you will be able to see that the status of the project has changed to Ready, indicating that the new deployment is now available.

Redeployed successfully

  • Take note of the first URL.

  • Use cURL to hit the endpoint once again, but this time, use the production URL.

curl -i --location --request POST 'https://portfolio-enquiry-submitter-service.vercel.app/enquiry' \
--header 'Content-Type: application/json' \
--data-raw '{
    "timestamp": 1680249656000,
    "first_name": "Clyde",
    "last_name": "Cronshaw",
    "email": "ccronshaw3@theguardian.com",
    "company": "Topicblab",
    "message": "Nulla tellus. In sagittis dui vel nisl. Duis ac nibh."
}'
  • If the process is successful, you will be presented with a response similar to the following:
HTTP/2 307 
cache-control: public, max-age=0, must-revalidate
date: Fri, 31 Mar 2023 05:24:59 GMT
location: https://portfolio-enquiry-submitter-service.vercel.app/enquiry/
server: Vercel
strict-transport-security: max-age=63072000; includeSubDomains; preload
x-vercel-cache: MISS
x-vercel-id: bom1::iad1::vbgfb-1680240299206-2c3e66d39364
content-length: 0

HTTP/2 200
cache-control: public, max-age=0, must-revalidate
content-type: application/json
date: Fri, 31 Mar 2023 05:25:00 GMT
server: Vercel
strict-transport-security: max-age=63072000; includeSubDomains; preload
x-vercel-cache: MISS
x-vercel-id: bom1::iad1::vbgfb-1680240299478-ef121065b697
content-length: 40

{"message":"Enquiry added successfully"}%

You have successfully deployed your serverless function to Vercel quickly and simply! This is just the beginning, as there are countless possibilities for you to explore beyond this point. I encourage you to refer to the relevant documentation and experiment with different options.

Thank you for reading this article. Please do not hesitate to contact me at dasguptatirthyakamal@gmail.com if you have any questions or if you notice any errors that need to be corrected. Your feedback and suggestions for future improvements to this article are also welcome.

VercelserverlessFastAPIPythongoogle sheets