JDBC

Apps Script can connect to external databases through the JDBC service , a wrapper around the standard Java Database Connectivity technology . The JDBC service supports Google Cloud SQL for MySQL , MySQL, Microsoft SQL Server, and Oracle databases.

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

Google Cloud SQL databases

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

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

Creating Google Cloud SQL connections

There are two ways of establishing a connection with a Google Cloud SQL database using Apps Script's JDBC service :

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

This method creates a connection to a Google Cloud SQL MySQL instance using the Jdbc.getCloudSqlConnection(url) method. The database URL has the form of jdbc:google:mysql://subname , where subname is the MySQL Instance connection namelisted on the Cloud SQL instance Overviewpage in the Google Cloud console .

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

Using Jdbc.getConnection(url)

In order to use this method, you must authorize certain Classless 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 this data source .

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

Once you've authorized these IP ranges, you can create connections to your Google Cloud SQL instance using one of the Jdbc.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 Script JDBC service , in your database settings you must authorize IP ranges from this data source .

Once these allowlists are in place, you can create a connection to the database using one of the Jdbc.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 the Jdbc.getCloudSqlConnection(url) method. For other databases you must use the Jdbc.getConnection(url) method to create database connections.

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

Create a database, user, and table

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

service/jdbc.gs
 /** 
 * Create a new database within a Cloud SQL instance. 
 */ 
 function 
  
 createDatabase 
 () 
  
 { 
  
 try 
  
 { 
  
 const 
  
 conn 
  
 = 
  
 Jdbc 
 . 
 getCloudSqlConnection 
 ( 
 instanceUrl 
 , 
  
 root 
 , 
  
 rootPwd 
 ); 
  
 conn 
 . 
 createStatement 
 (). 
 execute 
 ( 
 ' 
 CREATE 
  
 DATABASE 
  
 ' 
  
 + 
  
 db 
 ); 
  
 } 
  
 catch 
  
 ( 
 err 
 ) 
  
 { 
  
 // TODO(developer) - Handle exception from the API 
  
 console 
 . 
 log 
 ( 
 ' 
 Failed 
  
 with 
  
 an 
  
 error 
  
 % 
 s 
 ' 
 , 
  
 err 
 . 
 message 
 ); 
  
 } 
 } 
 /** 
 * Create a new user for your database with full privileges. 
 */ 
 function 
  
 createUser 
 () 
  
 { 
  
 try 
  
 { 
  
 const 
  
 conn 
  
 = 
  
 Jdbc 
 . 
 getCloudSqlConnection 
 ( 
 dbUrl 
 , 
  
 root 
 , 
  
 rootPwd 
 ); 
  
 const 
  
 stmt 
  
 = 
  
 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 API 
  
 console 
 . 
 log 
 ( 
 ' 
 Failed 
  
 with 
  
 an 
  
 error 
  
 % 
 s 
 ' 
 , 
  
 err 
 . 
 message 
 ); 
  
 } 
 } 
 /** 
 * Create a new table in the database. 
 */ 
 function 
  
 createTable 
 () 
  
 { 
  
 try 
  
 { 
  
 const 
  
 conn 
  
 = 
  
 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 API 
  
 console 
 . 
 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 as well as a batch of 500 records. Batching is vital for bulk operations.

Note also the use of parameterized statements, in which the variables are denoted by ? . To prevent SQL injection attacks , you should use parameterized statements to escape all user-supplied data.

service/jdbc.gs
 /** 
 * Write one row of data to a table. 
 */ 
 function 
  
 writeOneRecord 
 () 
  
 { 
  
 try 
  
 { 
  
 const 
  
 conn 
  
 = 
  
 Jdbc 
 . 
 getCloudSqlConnection 
 ( 
 dbUrl 
 , 
  
 user 
 , 
  
 userPwd 
 ); 
  
 const 
  
 stmt 
  
 = 
  
 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 API 
  
 console 
 . 
 log 
 ( 
 ' 
 Failed 
  
 with 
  
 an 
  
 error 
  
 % 
 s 
 ' 
 , 
  
 err 
 . 
 message 
 ); 
  
 } 
 } 
 /** 
 * Write 500 rows of data to a table in a single batch. 
 */ 
 function 
  
 writeManyRecords 
 () 
  
 { 
  
 try 
  
 { 
  
 const 
  
 conn 
  
 = 
  
 Jdbc 
 . 
 getCloudSqlConnection 
 ( 
 dbUrl 
 , 
  
 user 
 , 
  
 userPwd 
 ); 
  
 conn 
 . 
 setAutoCommit 
 ( 
 false 
 ); 
  
 const 
  
 start 
  
 = 
  
 new 
  
 Date 
 (); 
  
 const 
  
 stmt 
  
 = 
  
 conn 
 . 
 prepareStatement 
 ( 
 ' 
 INSERT 
  
 INTO 
  
 entries 
  
 ' 
  
 + 
  
 ' 
 ( 
 guestName 
 , 
  
 content 
 ) 
  
 values 
  
 (?, 
  
 ?) 
 ' 
 ); 
  
 for 
  
 ( 
 let 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
 < 
 500 
 ; 
  
 i 
 ++) 
  
 { 
  
 stmt 
 . 
 setString 
 ( 
 1 
 , 
  
 ' 
 Name 
  
 ' 
  
 + 
  
 i 
 ); 
  
 stmt 
 . 
 setString 
 ( 
 2 
 , 
  
 ' 
 Hello 
 , 
  
 world 
  
 ' 
  
 + 
  
 i 
 ); 
  
 stmt 
 . 
 addBatch 
 (); 
  
 } 
  
 const 
  
 batch 
  
 = 
  
 stmt 
 . 
 executeBatch 
 (); 
  
 conn 
 . 
 commit 
 (); 
  
 conn 
 . 
 close 
 (); 
  
 const 
  
 end 
  
 = 
  
 new 
  
 Date 
 (); 
  
 console 
 . 
 log 
 ( 
 ' 
 Time 
  
 elapsed 
 : 
  
 % 
 sms 
  
 for 
  
 % 
 s 
  
 rows 
 . 
 ' 
 , 
  
 end 
  
 - 
  
 start 
 , 
  
 batch 
 . 
 length 
 ); 
  
 } 
  
 catch 
  
 ( 
 err 
 ) 
  
 { 
  
 // TODO(developer) - Handle exception from the API 
  
 console 
 . 
 log 
 ( 
 ' 
 Failed 
  
 with 
  
 an 
  
 error 
  
 % 
 s 
 ' 
 , 
  
 err 
 . 
 message 
 ); 
  
 } 
 }

Read from the database

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

service/jdbc.gs
 /** 
 * Read up to 1000 rows of data from the table and log them. 
 */ 
 function 
  
 readFromTable 
 () 
  
 { 
  
 try 
  
 { 
  
 const 
  
 conn 
  
 = 
  
 Jdbc 
 . 
 getCloudSqlConnection 
 ( 
 dbUrl 
 , 
  
 user 
 , 
  
 userPwd 
 ); 
  
 const 
  
 start 
  
 = 
  
 new 
  
 Date 
 (); 
  
 const 
  
 stmt 
  
 = 
  
 conn 
 . 
 createStatement 
 (); 
  
 stmt 
 . 
 setMaxRows 
 ( 
 1000 
 ); 
  
 const 
  
 results 
  
 = 
  
 stmt 
 . 
 executeQuery 
 ( 
 ' 
 SELECT 
  
 * 
  
 FROM 
  
 entries 
 ' 
 ); 
  
 const 
  
 numCols 
  
 = 
  
 results 
 . 
 getMetaData 
 (). 
 getColumnCount 
 (); 
  
 while 
  
 ( 
 results 
 . 
 next 
 ()) 
  
 { 
  
 let 
  
 rowString 
  
 = 
  
 '' 
 ; 
  
 for 
  
 ( 
 let 
  
 col 
  
 = 
  
 0 
 ; 
  
 col 
 < 
 numCols 
 ; 
  
 col 
 ++) 
  
 { 
  
 rowString 
  
 += 
  
 results 
 . 
 getString 
 ( 
 col 
  
 + 
  
 1 
 ) 
  
 + 
  
 ' 
 \ 
 t 
 ' 
 ; 
  
 } 
  
 console 
 . 
 log 
 ( 
 rowString 
 ); 
  
 } 
  
 results 
 . 
 close 
 (); 
  
 stmt 
 . 
 close 
 (); 
  
 const 
  
 end 
  
 = 
  
 new 
  
 Date 
 (); 
  
 console 
 . 
 log 
 ( 
 ' 
 Time 
  
 elapsed 
 : 
  
 % 
 sms 
 ' 
 , 
  
 end 
  
 - 
  
 start 
 ); 
  
 } 
  
 catch 
  
 ( 
 err 
 ) 
  
 { 
  
 // TODO(developer) - Handle exception from the API 
  
 console 
 . 
 log 
 ( 
 ' 
 Failed 
  
 with 
  
 an 
  
 error 
  
 % 
 s 
 ' 
 , 
  
 err 
 . 
 message 
 ); 
  
 } 
 }

Closing connections

JDBC connections close automatically when a script finishes executing. (Keep in mind that a single google.script.run call counts as a complete execution, even if the HTML service page that made the call remains open.)

Nonetheless, if you know you're done with a connection, statement, or result set before the end of the script, it's a good idea to close them manually by calling JdbcConnection.close() , JdbcStatement.close() , or JdbcResultSet.close() .

Showing an alert or prompt dialog also terminates any open JDBC connections. However, other showing UI elements—like custom menus or dialogs and sidebars with custom content—does not.

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