Create a Database with GoogleSheet and Rust: Setting up Service Account

6 min readMar 9, 2023

Welcome to our guide on creating a database with GoogleSheet. First, we’ll create a service account in Google Cloud Project. Service accounts provide a secure way to access Google API without sharing your personal credentials.

In this tutorial, we will walk you through the process of creating a service account for our database in Google Cloud Project. This service account will be used to authenticate and authorise access to the database. We will cover the necessary steps to create a service account, grant it the appropriate permissions, and use it to access our database securely.

By the end of this tutorial, you will have a fully functional service account set up, giving you peace of mind knowing that your data is being accessed securely and with the appropriate level of authorization. So, let’s get started!

Case Study

Businesses often require efficient data management tools to streamline their operations. While some businesses may invest in complex database systems, small businesses may prefer a simpler solution that is easy to set up and use. One such solution is Google Sheets, a cloud-based spreadsheet software that can serve as a database for small businesses.

Challenges

JAD Finance is a small accounting company that specialises in providing accounting services for freelancers, small businesses and startups. The company has a team of 10 employees and serves clients in the local area. JAD Finance receives numerous inquiries and bookings, and it needs a reliable database to manage customer information, bookings, and other data.

Initially, JAD Finance used a traditional standard relational database software installed on a local computer to store its data. However, this system had several limitations, such as:

Limited access: Only one user could access the spreadsheet at a time, and if the user made changes, others had to wait to make their own changes.
No data backup: In case of computer failure or data loss, the company could lose all its data.

Migrating the data to a native cloud database can solve most of the problems above. However, this solution can be very costly and time consuming for small businesses.

Solution

JAD Finance decided to switch to Google Sheets as its database system. The company’s decision was based on several factors, such as:

Accessibility: Google Sheets is cloud-based, meaning that multiple users can access it simultaneously from anywhere with an internet connection. This feature makes it easier for employees to collaborate on tasks and update data in real-time.
Data backup: Google Sheets automatically saves data in real-time, and all changes are immediately synced to the cloud. This feature ensures that the company’s data is always backed up and accessible even in case of computer failure or data loss.
Functionality: Google Sheets has a wide range of functions, formulas, and features that can be used to manipulate and analyse data. This functionality makes it easier for JAD Finance to manage its data efficiently.
Scalability: Google Sheets is highly scalable, meaning that it can accommodate large amounts of data as the company grows.

Project Setup

In this section, we will setup our project for our database integration.

Prerequisites

In order to follow with this tutorial, you need the following prerequisites:

A google account. You can create one by following this guide if you don’t have one.
Google project for our our database. If you don’t already have a Google project, you can create one from this link.
Install Rust in your local environment. You can install Rust using rustup.

Creating a Service Account

When you authenticate your service account, it can interact with Google spreadsheets. First, you need to add the account as a collaborator to the sheet(s) you want to access.

First, Click on ENABLE APIS AND SERVICES from your Google cloud project.

Next, find and enable the Google Sheet API

By enabling the Google Sheet API we can get access to call the Google Sheet API from our project. However, we need a means of authenticating our application (Rust) which will be calling the Google Sheet API.

Now, click on the CREATE CREDENTIALS button to create credentials (service account) to manage authentication

Answer the questions asked to setup a credential. Next, hit the NEXT button.

Name your service account and add a little description about your service account. Now, click on the CREATE AND CONTINUE button

Grant the service account access to the project. I’ll be selecting the editor role.

Since we don’t have any need to grant access to users or groups, we’ll hit the DONE button now.

Next, we’ll create an API KEY for our service account.

Navigate to the KEYS tab, then click on the ADD KEY button in the new page, to select Create new key.

Chose the JSON key format and the created key will be downloaded automatically into your work environment.

Create The Database Sheet

Open the service_account.json file and find the client_email property. Now, we’ll create a new sheet for our database and grant access to the Email address from our service account.

Conclusion

In this article, we have explored Google Spreadsheet and how small businesses can use it as a database. First, we have setup a service account for the GoogleSheet API. We have also created our Spreadsheet and our service account now has access as an EDITOR for our Spreadsheet.

I have created a Github repository that holds the code for our database. This repository is still a work-in-progress. You can fork, star and contribute.

Would you like to read our publications on GoogleSheet? Checkout our store on “How to guide”.

Part 2 (Create a database with GoogleSheet and Rust: Create a mock post request)

While Google Sheets allows you to manage large data like accounting, incorporating tools that can enhance accounting activities is a great way to increase productivity. PayTrack is a great fit for you. Try PayTrack and contact our technical team at peppubooks@gmail.com if you need help.