ORMA: Orm per Google Apps Script

If you use Google Apps Script, you are surely as tired as I am of getting data from Google Sheets in the form of multidimensional arrays, running endless loops to identify the row where the element is inserted to modify it without risking compromising the values of other rows.

For this reason, together with Sintropia I created ORMA an ORM for Google Apps Script aimed at being simple and straightforward.

Before to start

There are some key points to know before deciding if Orma is right for you. Let's take a look at them right away:

• Orma uses Google Sheets as if they were relational databases, so it's essential that row 1 is used as a header.
• To function correctly, Orma requires that an id column be present in the header of all connected databases.
• To make developers' work easier, Orma does not create its own classes but instead leverages the Spreadsheet & Sheet enhancing them by adding the methods necessary for the library to function.

So, if you use Google Sheets as a database and Google Apps Script to create, modify, and delete data, Orma is the tool for you.

‍

How to start

The complete documentation is available at this link; below we will only cover some of the fundamental steps for using the library in Google Apps Script.

‍

Installation

Access the Google Apps Script file where you want to install Orma and enter the following Script Id as a new library.

  1CA-kvfiZjY2Ex2C84hL46Vn2F8GijPbfSy9J3vZQUzzbal5ExhQyG0Z7

‍

How to get data with Orma

Below are just some of the methods available for retrieving data from your Google Sheets. To view all the other methods, please refer to the documentation available here.

//Collegarsi al File Spreadsheet
const ssId = "your_spreadsheet_id";
const db = ORMA.openDbById(ssId);

//Ottenere gli Sheets disponibili
const tables = db._getTables();
const {users} = tables;

//Ottenere tutti i record del foglio
const allUsers = users._all();

//Ottenere un record cercandolo per id
const userById = users._find(1);

//Ottenere un record cercandolo secondo la corrispondenza del valore di una colonna
const firstUserBy = users._firstBy("name","Matteo");

//Ottenere un record secondo la corrispondenza di una query 
const firstUserByQuery = users._firstByQuery(`{}.email !== "matteo@sintropia.io"`)

‍

In what format do you get the data?

Instead of getting the data in a two-dimensional array as Google Apps Script usually returns, Orma makes our life much easier by allowing us to write cleaner code and returns the values as an object.

For example, this would be the output of the variable userById initialized in the box above:

‍

{
  id: 1, 
  name: Matteo, 
  password: 5994471abb01112afcc18159f6cc74b4f511b99806da59b3caf5a9c173cacfc5, 
  email: matteo@sintropia.io, 
  _delete: function () { [native code] }, 
  _save: function () { [native code] }
 }

As you may have noticed, in addition to the data present in the database, the object contains functions necessary for modifying and deleting the data. In the following paragraphs, we will see how to use them.

‍

How to edit data with Orma

Orma uses a modern syntax that is now the standard for major ORM tools worldwide, but with the limitations imposed by Google Apps Script. Below we will see an example of how simple and intuitive it is to modify data in the database.

//Get the record to edit
const userToEdit = users._find(1);

//Edit the record
userToEdit.name = "Luca";

//Push the new values to the database
userToEdit._save();

To make bulk modifications, refer to the official documentation at this link.

‍

How to delete data with Orma

Deleting data in Orma is very simple, as you will see in the example below. You just need to retrieve the record or records to be deleted and then invoke the necessary function.

//Get the record to delete
const user = users._find(1);

//Delete the record
user._delete(); 

To delete items in bulk refer to the official documentation at this link.

‍

How to create records with Orma

Of course, by manipulating data in the form of objects, Orma also expects that data insertion is done in the form of objects. Here's an example of how to insert new records into the database.

const newUser = users._create(
    {
        id: users._id(), //This function generate an incremental id
        name: "Matteo",
        email: "matteo@sintropia.io",
        password: ENDO.hash("superPassword1!") //this function will encrypt the password
    }
)

To create items in bulk refer to the official documentation at this link.

Why use Orma?

If you're not yet convinced to try Orma in your projects, I'll give you the reasons that drove us to develop Orma and why I use it daily with my clients.

• The position of the columns will become irrelevant; you'll be able to rearrange it as you please.
• Abandon two-dimensional arrays in Google Apps Script.
• You will write 50% fewer lines of code.
• The code will be more readable.
• Prevent any type of errors during CRUD operations.
• Follows the most modern development standards.
• It will be updated with new implementations.
• It's free and open source.

‍

In conclusion

I would be really happy to receive any feedback on the use of the library. For any ideas, advice, or feedback, feel free to write to me at matteo@sintropia.io

‍