Quickstart Guide This guide will help you build a working example with ServiceSQL in just 5 minutes. By the end, you’ll have a simple blog system with users and posts.
Prerequisites
A Google Apps Script project
A Google Spreadsheet to use as your database
ServiceSQL library added to your project (see Installation )
Step 1: Get Your Spreadsheet ID First, create or open a Google Spreadsheet and copy its ID from the URL:
https://docs.google.com/spreadsheets/d/1abc...xyz/edit ↑ Copy this part
The Spreadsheet ID is the long string between /d/ and /edit in your spreadsheet’s URL.
Step 2: Initialize ServiceSQL In your Google Apps Script project, create a new file called Code.gs and add:
function setupDatabase () { // Initialize ServiceSQL with your spreadsheet ID const db = APPSQL . init ({ spreadsheetId: "YOUR_SPREADSHEET_ID" // Replace with your actual ID }); Logger . log ( "Database initialized!" ); return db ; }
Replace "YOUR_SPREADSHEET_ID" with the actual ID you copied in Step 1.
Step 3: Create Your First Table Now let’s create a Users table:
Define table structure
Add this code to create a Users table with columns: function createUsersTable () { const db = APPSQL . init ({ spreadsheetId: "YOUR_SPREADSHEET_ID" }); // Create Users table const users = db . table ( "Users" ); users . create ([ "id" , "name" , "email" , "age" , "created_at" ]); Logger . log ( "Users table created!" ); }
Run the function
Select createUsersTable from the function dropdown
Click the Run button
Authorize the script if prompted
Check your spreadsheet - you’ll see a new “Users” sheet!
Step 4: Insert Data Let’s add some users to our database:
function addUsers () { const db = APPSQL . init ({ spreadsheetId: "YOUR_SPREADSHEET_ID" }); const users = db . table ( "Users" ); // Insert first user users . insert ({ id: 1 , name: "John Doe" , email: "[email protected] " , age: 30 , created_at: new Date (). toISOString () }); // Insert second user users . insert ({ id: 2 , name: "Jane Smith" , email: "[email protected] " , age: 25 , created_at: new Date (). toISOString () }); Logger . log ( "Users added!" ); }
Run this function and check your spreadsheet - you’ll see the data appear!
Step 5: Query Your Data Now let’s retrieve and filter our data:
function queryUsers () { const db = APPSQL . init ({ spreadsheetId: "YOUR_SPREADSHEET_ID" }); const users = db . table ( "Users" ); // Get all users const allUsers = users . get (); Logger . log ( "All users:" , allUsers ); // Find user by ID const user = users . findById ( 1 ); Logger . log ( "User 1:" , user . name ); // Filter users const youngUsers = db . table ( "Users" ) . where ( "age" , "<" , 30 ) . get (); Logger . log ( "Users under 30:" , youngUsers ); // Count users const count = users . count (); Logger . log ( "Total users:" , count ); }
Step 6: Use Models (Optional but Recommended) For more complex applications, use Models instead of direct table queries:
class User extends Model { static _table = "Users" ; static _primaryKey = "id" ; static _timestamps = true ; static _fillable = [ "name" , "email" , "age" ]; static _casts = { age: "int" }; } function useModels () { const db = APPSQL . init ({ spreadsheetId: "YOUR_SPREADSHEET_ID" }); // Initialize the model User . use ( db ); // Create a user const user = User . create ({ name: "Bob Johnson" , email: "[email protected] " , age: 35 }); Logger . log ( "Created user:" , user . name ); // Find and update const foundUser = User . find ( 1 ); foundUser . age = 31 ; foundUser . save (); Logger . log ( "Updated user:" , foundUser . name ); // Query with conditions const activeUsers = User . where ( "age" , ">=" , 25 ) . get (); Logger . log ( "Users 25+:" , activeUsers . count ()); }
Complete Example: Blog System Here’s a complete example with Users and Posts:
Setup Tables
Define Models
Add Data
Query with Relations
function setupBlog () { const db = APPSQL . init ({ spreadsheetId: "YOUR_SPREADSHEET_ID" }); // Create Users table db . table ( "Users" ). create ([ "id" , "name" , "email" , "created_at" ]); // Create Posts table db . table ( "Posts" ). create ([ "id" , "user_id" , "title" , "body" , "published" , "created_at" ]); Logger . log ( "Blog tables created!" ); }
Next Steps Congratulations! You’ve built your first ServiceSQL application. Here’s what to explore next:
Models Learn about model features like casts, accessors, and lifecycle hooks
Query Builder Master complex queries, joins, and aggregations
Relationships Define HasMany, BelongsTo, and ManyToMany relationships
Collections Transform and manipulate query results
Common Patterns Update Records // With models const user = User . find ( 1 ); user . email = "[email protected] " ; user . save (); // With table API db . table ( "Users" ). updateById ( 1 , { email: "[email protected] " });
Delete Records // With models const user = User . find ( 1 ); user . delete (); // With table API db . table ( "Users" ). deleteById ( 1 );
const result = User . query (). paginate ( 20 , 1 ); // 20 per page, page 1 Logger . log ( result . data ); // Array of users Logger . log ( result . total ); // Total count Logger . log ( result . current_page ); // Current page Logger . log ( result . last_page ); // Last page number
Pro Tip : Use Models for applications with complex business logic, and use the table API for simple CRUD operations or data migrations.
Troubleshooting Table not found Make sure you’ve called create() on the table first, or check that the sheet name matches exactly.
Data not appearing Verify your spreadsheet ID is correct and the script has permission to access the spreadsheet.
Authorization errors Run any function manually first to trigger the authorization flow. Accept all permissions.
Ready to Learn More? You now have a working ServiceSQL application! Continue to the Installation guide for production setup details, or dive into Models to learn about advanced features.