Creating Tables

🚀 Initialization

Initialize the database by creating a new instance of the DB class. You can either create a new spreadsheet or connect to an existing one using its ID.

/**
 * Initialize the database.
 */

// Declare db in your global scope in order to use it in any part of the script
const db = DB.init('MyDatabase');
// OR connect to an existing spreadsheet using its ID
const existingDb = DB.init('ExistingDatabase', 'YOUR_SPREADSHEET_ID');

function seeSchemaCreation() {
  // Log the creation result
  console.log(db.getCreationResult());
}

Note that the seeSchemaCreation() is just a helper function to see the result of the schema creation. You can remove it after you see the result in the console. If you dont want to see the result, you can remove the function, but you must have at least one or more functions in your code.gs file, otherwise the script will not run.

When you hit Run on the seeSchemaCreation function for the first time, you will see an Oauth consent screen, asking for permissions to access your Google Sheets. This is because the library needs to create a new spreadsheet/handle any spreadsheets you store in the database schema.

After you grant the permissions, this screen may appear

As the library is not verified by Google, you will need to click on Advanced and then Go to <Your Project Name> (unsafe) to proceed.

As the code is open-source, you can review it before proceeding and reassure yourself that it is safe to use.

After that the script will run, and you will see a new spreadsheet created in your Google Drive with the name MyDatabase (or now you can manage ExistingDatabase if you provided an ID).

🛠️ Creating Tables

Define and create tables within your spreadsheet. Each table represents a sheet with specified fields and types.

/**
 * Create tables in the database.
 */
let tables = []
const db = DB.init('MyDatabase');
const employeeTableConfig;
const departmentTableConfig;

function createTables() {

  // Define configuration for the EMPLOYEES table
  employeeTableConfig = {
    tableName: "EMPLOYEES",
    historyTableName: "DELETED_EMPLOYEES",
    fields: {
      name: "string",
      age: "number",
      position: "string",
      employed: "boolean",
      hire_date: "date",
    }
  };

  // Create the EMPLOYEES table
  const createResult = db.createTable(employeeTableConfig);
  console.log("Employee table created:", createResult);

  // Define configuration for the DEPARTMENTS table
  departmentTableConfig = {
    tableName: "DEPARTMENTS",
    historyTableName: "DELETED_DEPARTMENTS",
    fields: {
      department_name: "string",
      manager_id: "number",
      location: "string",
    }
  };

  // Create the DEPARTMENTS table
  const deptCreateResult = db.createTable(departmentTableConfig);
  console.log("Departments table created:", deptCreateResult);

  tables.push(employeeTableConfig);
  tables.push(departmentTableConfig);
}

After creation, remember that you must place them into the DB Context, otherwise they wont be available to operate with. I Recomend doing this as separate declarations, if there are few tables. If there's a hefty amount of tables, you can loop through them and place them into the context.

tables.forEach((table) => {
  console.log(db.putTableIntoDbContext(table));
});

Key Points

1. You leave the code for putting tables in the schema context outside of any function, in the global scope, as .gs files are stateless, and will be called in a clean slate state each and every time any google.script.run is called in your main.js.
2. You have the tables defined in the global scope, so you can access them from anywhere in your script.
3. You have the DB instance defined in the global scope, so you can access it from anywhere in your script (either by copying the script or adding the library).
4. You actually did placed the tables into the context before using them in your CRUD operations.

You can continue adding more basic usage instructions here (e.g., how to handle data, read records, etc.). For advanced usage, see docs/advanced-examples.

🎨 Applying Color Schemes

Enhance the visual appeal of your sheets by applying predefined color themes.

const db = DB.init('MyDatabase');

/**
 * Apply a color scheme to the EMPLOYEES table.
 */
function applyColorScheme() {

  // Apply the 'blue' color scheme
  db.applyColorScheme('EMPLOYEES', 'blue');
  
  console.log('Color scheme applied to EMPLOYEES table.');
}