B4J Library [Web][SithasoGoogleSheetsAPI] Replace Traditional Back Ends with Google Sheets

Hi Fam

Documentation

I have always been curious about this and wanted a version I could use with BANano. Finally its here, well, kinda loading...

The ability to use Google Sheets as your back-end. Let's watch..





STEP BY STEP SETUP

Follow these steps to set up your Google Sheet and get the credentials needed to use BANanoGoogleSheetAPI.js:

1. Create a Google Sheet**
- Go to Google Sheets and create a new spreadsheet.
- Name your sheet (e.g., `Users`).

2. Get Your Spreadsheet ID**
- Open your sheet in the browser. The URL will look like:

https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit#gid=0

- Copy the long string after `/d/` and before `/edit` — this is your **spreadsheetId**.

3. Create a Google Cloud Project**
- Go to the Google Cloud Console
- Click the project dropdown (top left) and select "New Project". Give it a name and create it.

4. Enable the Google Sheets API**
- In your project, go to "APIs & Services > Library".
- Search for "Google Sheets API" and click "Enable".

5. Create OAuth2 Credentials**
- Go to "APIs & Services > Credentials".
- Click "Create Credentials" > "OAuth client ID".
- If prompted, configure the consent screen (just fill required fields).
- Authorized Redirect URL should be https://developers.google.com/oauthplayground
- Choose "Web Application" as the application type.
- Download the credentials JSON file. It contains your **client_id** and **client_secret**.

6. Get a Refresh Token and Access Token**
- Go to OAuth 2.0 Playground to:
- Authorize with your client ID/secret.
- Click the gear for OAuth Configuration and enter your client id & client secret and close.

- Select the scope on the left for 'Select & Authorize API': `https://www.googleapis.com/auth/spreadsheets`, click Authorize API. This should ask you to confirm with your account.

- Exchange the code for a refresh token and access token.

- Save your **accessToken** and **refreshToken**.
- Use a tool like PostMan to check if you can get a token using the refresh token. If so you are good to go, these are the details you will use on the app also.

7. **Share Your Sheet for API Access**
- In your Google Sheet, click "Share" and add the email address shown in your OAuth credentials (often ends with `@developer.gserviceaccount.com` or your Google account email).
- Give it "Editor" access.
8. **Use These in BANanoGoogleSheetAPI.js**
- Use the `spreadsheetId`, `accessToken` (optional), `refreshToken`, `clientId`, `clientSecret` in your BANanoGoogleSheetAPI config as shown in the usage examples below.

Example Sheet:

NB: This b4xlib will not be free due to the great amount of work and time taken to create it, you can send your $25 certificate of appreciation to, https://paypal.me/anelembanga


Related Content: Share My Creations

[Web][SithasoGoogleSheetsAPI] - Replace Traditional Back Ends with Google Sheets

Hi Fam! NB: Please note the technology discussed here can be used in JavaScript & also in BANano Projects. After a couple of days of planning and building SithasoGoogleSheetsAPI, its finally here. The Hidden Power of Google Sheets as a Database Imagine building full-stack web applications...

www.b4x.com

 

[Mashiane]

We worked on updating the bugs in the demo, here is a working example...

 

[Mashiane]

It's been a long day... the unit tests are unit testing...

PASS ./SheetAPI.test.js
SheetAPI Core CRUD Operations
√ generateUniqueId() creates unique IDs (6 ms)
√ create() adds record successfully (4 ms)
√ getById() retrieves record correctly (3 ms)
√ getByField() retrieves by field (1 ms)
√ getIdByField() returns the correct ID (1 ms)
√ recordExists() returns true for existing record (1 ms)
√ updateById() supports partial updates (2 ms)
√ deleteById() removes record successfully (2 ms)
√ error handling works for non-existent records (1 ms)
SheetAPI Batch Operations
√ batchInsert() adds multiple records (1 ms)
√ batchUpdate() updates multiple records by id (2 ms)
√ batchDelete() removes multiple records by id (1 ms)
√ batchInsert() returns error for empty array
√ batchUpdate() returns error for empty array
√ batchDelete() returns error for empty array (1 ms)
√ batchUpdate() returns error for non-existent ids
√ batchDelete() returns error for non-existent ids
SheetAPI Constructor and Initialization
√ constructor throws error for missing spreadsheetId (60 ms)
√ constructor throws error for missing sheetName (1 ms)
√ constructor throws error for missing authentication (1 ms)
√ constructor accepts valid configuration
√ headers are processed correctly (1 ms)
SheetAPI Cache Functionality
√ cache configuration is auto-optimized based on userCount (1 ms)
√ cache is disabled when userCount is 0 (1 ms)
√ duration unit conversion works
SheetAPI Type Casting and Schema
√ _castValue handles string type (1 ms)
√ _castValue handles integer type
√ _castValue handles double type
√ _castValue handles boolean type (1 ms)
√ _castValue handles date type
SheetAPI Sheet Management
√ sheetExists() returns boolean for sheet existence (1 ms)
√ rowCount() returns number of data rows (1 ms)
SheetAPI Advanced getAll Features
√ getAll() applies default limit for memory efficiency (1 ms)
√ getAll() supports complex filtering with multiple conditions (1 ms)
√ getAll() supports multiple sort fields (1 ms)
√ getAll() supports offset and limit (1 ms)
√ getAll() field selection works (1 ms)
SheetAPI Error Handling
√ methods return error when API not initialized (1 ms)
√ getById() handles invalid ID format (1 ms)
√ getByField() handles invalid field name (1 ms)
√ updateById() handles non-existent record (1 ms)
√ deleteById() handles non-existent record
SheetAPI Token Manager
√ token manager is created when refresh token is provided
√ token manager is not created when only access token is provided (1 ms)
SheetAPI Utility Methods
√ generateUniqueId() generates different IDs (1 ms)
√ recordExists() returns false for non-existent records

Test Suites: 1 passed, 1 total
Tests: 46 passed, 46 total
Snapshots: 0 total
Time: 2.192 s
Ran all test suites.
PS C:\laragon\www\googlesheetapi\sheetapi-test>

 

[Mashiane]

This example is built using BANano & SithasoDaisy5




How to Reproduce

1. Get BANano
2. Get SithasoDaisy5 (remember to copy b4xlib to additional libraries folder)
3. Step by Step on setting up Google Sheets API.
4. In B4J, click File > New > SD5 GoogleSheetAPI

View attachment 168309

5. Get SithasoGoogleSheetAPI
6. Add the file to your BANano / javascript project.

 

[Mashiane]

Using in your JavaScript Projects

We assume you have set up your Google Sheet API credentials.

Please see the attached readme and guide...

#SharingTheGoodness