AI-generated Key Takeaways

outlined_flag

• Apps Script uses the JDBC service to connect to external databases, including Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle.

• Updating external databases with JDBC requires opening a connection and sending SQL statements.

• For Google Cloud SQL databases, the recommended connection method isJdbc.getCloudSqlConnection(url).

• To connect to other databases or Google Cloud SQL usingJdbc.getConnection(url), you must authorize specific IP ranges in your database settings.

• JDBC connections close automatically when a script finishes, but can be closed manually withclose() methods for connections, statements, or result sets.

Apps Script can connect to external databases through theJDBC service, a wrapper around the standardJava Database Connectivity technology.The JDBC service supportsGoogle Cloud SQL for MySQL, MySQL, Microsoft SQLServer, and Oracle databases.

To update an external database with JDBC, your script must open a connectionto the database and then make changes by sending SQL statements.

Google Cloud SQL databases

Google Cloud SQL lets you create relational databases that livein Google's cloud. Note thatCloud SQL might incur charges based on your usage.

You can create a Google Cloud SQL instance by following the steps listed in theCloud SQL quickstart.

Creating Google Cloud SQL connections

There are two ways of establishing a connection with a Google Cloud SQLdatabase using Apps Script'sJDBC service:

• (Recommended) Connecting usingJdbc.getCloudSqlConnection(url)
• Connecting usingJdbc.getConnection(url)

These methods are explained below. Both are valid, but the second methodrequires you to authorize a set of IP ranges for access to your database.

UsingJdbc.getCloudSqlConnection(url) (recommended)

This method creates a connection to a Google Cloud SQL MySQL instance using theJdbc.getCloudSqlConnection(url)method. The database URL has the form ofjdbc:google:mysql://subname, wheresubname is the MySQLInstance connection namelisted on the Cloud SQL instanceOverview page in theGoogle Cloud console.

To connect to Cloud SQL SQL Server, seeJdbc.getConnection(url).

UsingJdbc.getConnection(url)

In order to use this method, you must authorize certainClassless Inter-Domain Routing (CIDR) IP address ranges so that Apps Script's servers can connect to your database.Before running your script, complete the following steps:

1. In your Google Cloud SQL instance,authorize the IP ranges,one at at time from thisdata source.

2. Copy the URL that was assigned to your database; it should have theformjdbc:mysql:subname.

Once you've authorized these IP ranges, you can create connections to yourGoogle Cloud SQL instance using one of theJdbc.getConnection(url)methods and the URL you copied above.

Other databases

If you already have your own MySQL, Microsoft SQL Server, or Oracle database,you can connect to it through Apps Script's JDBC service.

Creating other database connections

In order to create a database connection using the Apps ScriptJDBC service, in your database settingsyou must authorize IP ranges fromthis data source.

Once these allowlists are in place, you can create a connection to the databaseusing one of theJdbc.getConnection(url)methods and your database's URL.

Sample code

The sample code below assumes you are connecting to a Google Cloud SQL database,and creates database connections using theJdbc.getCloudSqlConnection(url)method. For other databases you must use theJdbc.getConnection(url)method to create database connections.

For more information on the JDBC methods, see theJava documentation for JDBC.

Create a database, user, and table

Most developers use theMySQL command-line tool tocreate databases, users, and tables. However, it's possible to do the samething in Apps Script, as shown below. It's a good idea to create at least oneother user so that your script doesn't always have to connect to the database asroot.

/** * Create a new database within a Cloud SQL instance. */functioncreateDatabase(){try{constconn=Jdbc.getCloudSqlConnection(instanceUrl,root,rootPwd);conn.createStatement().execute('CREATE DATABASE '+db);}catch(err){// TODO(developer) - Handle exception from the APIconsole.log('Failed with an error %s',err.message);}}/** * Create a new user for your database with full privileges. */functioncreateUser(){try{constconn=Jdbc.getCloudSqlConnection(dbUrl,root,rootPwd);conststmt=conn.prepareStatement('CREATE USER ? IDENTIFIED BY ?');stmt.setString(1,user);stmt.setString(2,userPwd);stmt.execute();conn.createStatement().execute('GRANT ALL ON `%`.* TO '+user);}catch(err){// TODO(developer) - Handle exception from the APIconsole.log('Failed with an error %s',err.message);}}/** * Create a new table in the database. */functioncreateTable(){try{constconn=Jdbc.getCloudSqlConnection(dbUrl,user,userPwd);conn.createStatement().execute('CREATE TABLE entries '+'(guestName VARCHAR(255), content VARCHAR(255), '+'entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));');}catch(err){// TODO(developer) - Handle exception from the APIconsole.log('Failed with an error %s',err.message);}}

Write to the database

The examples below demonstrate how to write a single record to the database aswell as a batch of 500 records. Batching is vital for bulk operations.

Note also the use of parameterized statements, in which the variables aredenoted by?. To preventSQL injection attacks, you should useparameterized statements to escape all user-supplied data.

/** * Write one row of data to a table. */functionwriteOneRecord(){try{constconn=Jdbc.getCloudSqlConnection(dbUrl,user,userPwd);conststmt=conn.prepareStatement('INSERT INTO entries '+'(guestName, content) values (?, ?)');stmt.setString(1,'First Guest');stmt.setString(2,'Hello, world');stmt.execute();}catch(err){// TODO(developer) - Handle exception from the APIconsole.log('Failed with an error %s',err.message);}}/** * Write 500 rows of data to a table in a single batch. */functionwriteManyRecords(){try{constconn=Jdbc.getCloudSqlConnection(dbUrl,user,userPwd);conn.setAutoCommit(false);conststart=newDate();conststmt=conn.prepareStatement('INSERT INTO entries '+'(guestName, content) values (?, ?)');for(leti=0;i <500;i++){stmt.setString(1,'Name '+i);stmt.setString(2,'Hello, world '+i);stmt.addBatch();}constbatch=stmt.executeBatch();conn.commit();conn.close();constend=newDate();console.log('Time elapsed: %sms for %s rows.',end-start,batch.length);}catch(err){// TODO(developer) - Handle exception from the APIconsole.log('Failed with an error %s',err.message);}}

Read from the database

This example demonstrates how to read a large number of records from thedatabase, looping over the result set as necessary.

/** * Read up to 1000 rows of data from the table and log them. */functionreadFromTable(){try{constconn=Jdbc.getCloudSqlConnection(dbUrl,user,userPwd);conststart=newDate();conststmt=conn.createStatement();stmt.setMaxRows(1000);constresults=stmt.executeQuery('SELECT * FROM entries');constnumCols=results.getMetaData().getColumnCount();while(results.next()){letrowString='';for(letcol=0;col <numCols;col++){rowString+=results.getString(col+1)+'\t';}console.log(rowString);}results.close();stmt.close();constend=newDate();console.log('Time elapsed: %sms',end-start);}catch(err){// TODO(developer) - Handle exception from the APIconsole.log('Failed with an error %s',err.message);}}

Closing connections

JDBC connections close automatically when a script finishes executing. (Keep inmind that a singlegoogle.script.runcall counts as a complete execution, even if the HTML service page that made thecall remains open.)

Nonetheless, if you know you're done with a connection, statement, or result setbefore the end of the script, it's a good idea to close them manually by callingJdbcConnection.close(),JdbcStatement.close(),orJdbcResultSet.close().

Showing analert or prompt dialogalso terminates any open JDBC connections. However, other showing UIelements—like custom menus or dialogs and sidebars with customcontent—does not.

​Google, Google Workspace, and related marks and logos are trademarks ofGoogle LLC. All other company and product names are trademarks of the companieswith which they are associated.​