Create a Database with GoogleSheet and Rust: Mock ‘Post’ Request to GoogleSheet.

In the last post, we discussed creating a service account and setting it up for our database. One important factor for choosing a database, is the ease to post (send) and get (retrieve) data from your database. The GoogleSheet API, allows users to send and retrieve data easily.

For this section, we will explore how to add new data into our Spreadsheet, using Rust.

Why Rust?

Requests ‘to’ and ‘from’ a database, is usually done in an application’s backend. Developers would mostly chose a language that they’re most familiar with. Therefore, one reason we’re using Rust is because it’s a suitable backend language for me.

Rust also provides enhanced safety, speed, and concurrency, or the ability to run multiple computations in parallel.

GoogleSheet — Rust API Implementation

To use the GoogleSheet API, we need an implementation of the GoogleSheet API in Rust. This Rust library, provides a means of interacting with the GoogleSheet API.

First, we’ll create our Rust project. Run the command below to create a Rust project.

// for this project, our <projectname> is gdatabase
cargo new <projectname>

Navigate to the project that you’ve just created and notice the structure.

gdatabase
  - src
    - main.rs
  - .gitignore
  - Cargo.toml

Now, navigate to the Cargo.toml file, let’s add our GoogleSheet library in your dependencies section. Your dependencies section should look like this now:

[dependencies]
google-sheets4 = "*"

We’re using * since we want the latest version of google-sheets4.

Next, in your src/main.rs file, we’ll write the logic for our application. First, let’s import our Sheet library.

use google_sheets4 as sheets4;

use sheets4::{api::ValueRange, Error, Sheets, oauth2, oauth2::ServiceAccountAuthenticator};

We’ve referenced our ValueRange, Error, and Sheets structs/Enums that we’ll use later. We also referenced the ServiceAccountAuthenticator to authenticate our service account.

In our main function, let’s read our credential that was downloaded from the first series.

let creds = oauth2::read_service_account_key("serviceaccount.json")
        .await
        .expect("Can't read credential, an error occurred");

This credential should be added to the root of our project with the name serviceaccount.json. Now, we’ll authenticate our service account.

let sa = ServiceAccountAuthenticator::builder(creds)
        .build()
        .await
        .expect("There was an error, trying to build connection with authenticator");

Now, we can create a connection to the Sheet hub using hyper.

let hub = Sheets::new(hyper::Client::builder().build(hyper_rustls::HttpsConnectorBuilder::new().with_native_roots().https_or_http().enable_http1().enable_http2().build()), sa);

We’ll declare the ValueRange which contains the values we want to add to our database. Since this is a test, we’ll be posting hello, world.

 let req =ValueRange {
        major_dimension: None,
        range: None,
        values: Some(vec![vec![
            "hello".to_string(),
            "world".to_string()
        ]]),
    };

Now, let’s post our value with the values_append function. This function takes three parameters. First, the ValueRange. Next, it takes the ID of the spreadsheet we’re using as a database, i.e SpreadsheetID. Finally, the Range we’ll want to cover in A1 notation.

   let result = hub.spreadsheets().values_append(req, "1Wzp7fWqcgQNQsv7MxAj5wrPm7JrFstFP0XXXXXXXXXX", "A1:D10")
                .value_input_option("USER_ENTERED")
                 .doit().await;

Finally, we can match our result to available errors from our Error Enum declared earlier.

match result {
        Err(e) => match e {
            // The Error enum provides details about what exactly happened.
            // You can also just use its `Debug`, `Display` or `Error` traits
             Error::HttpError(_)
            |Error::Io(_)
            |Error::MissingAPIKey
            |Error::MissingToken(_)
            |Error::Cancelled
            |Error::UploadSizeLimitExceeded(_, _)
            |Error::Failure(_)
            |Error::BadRequest(_)
            |Error::FieldClash(_)
            |Error::JsonDecodeError(_, _) => println!("{}", e),
        },
        Ok(res) => println!("Success: {:?}", res),
    }

We should also add tokio to our main function since we have some asynchronous tasks in our function.

Our main.rs should look like this now:

use google_sheets4 as sheets4;

use sheets4::{api::ValueRange, Error, Sheets, oauth2, oauth2::ServiceAccountAuthenticator};
use hyper;
use hyper_rustls;

#[tokio::main]
async fn main() {
    let creds = oauth2::read_service_account_key("serviceaccount.json")
        .await
        .expect("Can't read credential, an error occurred");

    let sa = ServiceAccountAuthenticator::builder(creds)
        .build()
        .await
        .expect("There was an error, trying to build connection with authenticator");

    let hub = Sheets::new(hyper::Client::builder().build(hyper_rustls::HttpsConnectorBuilder::new().with_native_roots().https_or_http().enable_http1().enable_http2().build()), sa);

    let req =ValueRange {
        major_dimension: None,
        range: None,
        values: Some(vec![vec![
            "hello".to_string(),
            "world".to_string()
        ]]),
    };

    // You can configure optional parameters by calling the respective setters at will, and
    // execute the final call using `doit()`.
    // Values shown here are possibly random and not representative !
    let result = hub.spreadsheets().values_append(req, "1Wzp7fWqcgQNQsv7MxAj5wrPm7JrFstFP0RBSoAje8QI", "A1:D10")
                .value_input_option("USER_ENTERED")
                 .doit().await;
    
    match result {
        Err(e) => match e {
            // The Error enum provides details about what exactly happened.
            // You can also just use its `Debug`, `Display` or `Error` traits
             Error::HttpError(_)
            |Error::Io(_)
            |Error::MissingAPIKey
            |Error::MissingToken(_)
            |Error::Cancelled
            |Error::UploadSizeLimitExceeded(_, _)
            |Error::Failure(_)
            |Error::BadRequest(_)
            |Error::FieldClash(_)
            |Error::JsonDecodeError(_, _) => println!("{}", e),
        },
        Ok(res) => println!("Success: {:?}", res),
    }
}

The dependencies section of our Cargo.toml should look like this:

[dependencies]
google-sheets4 = "*"
tokio =  { version = "1.26.0", features = ["full"] }
hyper = "0.14.24"
hyper-rustls = "0.23.2"

When you run the application with the command cargo run, you should get the 200 status code.

When you check your Google Sheet, notice that Hello, World has been added.

In our next post, we will design our database structure using Rust Struct. This way we determine the field type we’ll be receiving. We’ll also construct a means to collect data from our users, instead of hardcoding data in our application.

Conclusion

In this article, we have explored sending data into our Google Sheet, from our Rust backend. One highlight of a database is its structure. In our next post, we’ll work on our database’ structure and what type of information each cell should hold.

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”.

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.

Bookkeeping with PayTrack

We’re starting a discord community for users who love to perform bookkeeping in a spreadsheet, you can join.