JDBC

As versões 1.0 e 1.1 do protocolo de segurança TLS estão desativadas. Para estabelecer conexões, use o TLS 1.2 ou uma versão mais recente.

O Google Apps Script consegue se conectar a bancos de dados externos pelo serviço JDBC, um wrapper que usa a tecnologia Java Database Connectivity padrão. O serviço JDBC é compatível com o Google Cloud SQL para MySQL, MySQL, Microsoft SQL Server, Oracle e bancos de dados PostgreSQL.

Se a planilha estiver ficando muito grande ou você estiver enfrentando problemas de tempo limite com cálculos complexos, mover os dados para um banco de dados externo pode melhorar significativamente o desempenho e a confiabilidade.

Para atualizar um banco de dados externo com JDBC, seu script precisa abrir uma conexão com o banco de dados e fazer mudanças enviando instruções SQL.

Bancos de dados do Google Cloud SQL

Com o Google Cloud SQL, é possível criar bancos de dados relacionais que ficam na nuvem do Google. O Cloud SQL pode gerar cobranças com base no seu uso.

Crie uma instância do Google Cloud SQL seguindo as etapas listadas no guia de início rápido do Cloud SQL.

Criar conexões do Google Cloud SQL

Há duas maneiras de estabelecer uma conexão com um banco de dados do Google Cloud SQL usando o serviço JDBC do Apps Script:

Os dois são válidos, mas o segundo método exige que você autorize um conjunto de intervalos de IP para acessar seu banco de dados.

Esse método cria uma conexão com uma instância do MySQL do Google Cloud SQL usando o método Jdbc.getCloudSqlConnection. O URL do banco de dados tem a forma jdbc:google:mysql://subname, em que subname é o Nome da conexão da instância do MySQL listado na página Visão geral da instância do Cloud SQL no console do Google Cloud.

Para se conectar ao Cloud SQL SQL Server, consulte Jdbc.getConnection.

Use Jdbc.getConnection

Para usar esse método, é necessário autorizar determinados intervalos de endereços IP de roteamento entre domínios sem classe (CIDR) para que os servidores do Apps Script possam se conectar ao seu banco de dados. Antes de executar o script, siga estas etapas:

  1. Na sua instância do Google Cloud SQL, autorize os intervalos de IP, um de cada vez, desta fonte de dados.

  2. Copie o URL atribuído ao seu banco de dados. Ele deve ter o formato jdbc:mysql:subname.

Depois de autorizar esses intervalos de IP, crie conexões com sua instância do Google Cloud SQL usando um dos métodos Jdbc.getConnection e o URL copiado anteriormente.

Outros bancos de dados

Se você já tem seu próprio banco de dados MySQL, Microsoft SQL Server, Oracle ou PostgreSQL, conecte-se a ele usando o serviço JDBC do Apps Script.

Criar outras conexões de banco de dados

Para criar uma conexão de banco de dados usando o serviço JDBC do Apps Script, nas configurações do banco de dados, autorize os intervalos de IP da fonte de dados.

O serviço JDBC só pode se conectar às portas 1025 ou posteriores. Verifique se o banco de dados não está sendo veiculado em uma porta inferior.

Depois que essas listas de permissões forem criadas, crie uma conexão com o banco de dados usando um dos métodos Jdbc.getConnection e o URL do banco de dados.

Código de amostra

O exemplo de código a seguir pressupõe que você está se conectando a um banco de dados do Google Cloud SQL e cria conexões de banco de dados usando o método Jdbc.getCloudSqlConnection. Para outros bancos de dados, use o método Jdbc.getConnection para criar conexões.

Para mais informações sobre os métodos JDBC, consulte a documentação do Java para JDBC.

Criar um banco de dados, um usuário e uma tabela

A maioria dos desenvolvedores usa a ferramenta de linha de comando do MySQL para criar bancos de dados, usuários e tabelas. No entanto, é possível fazer a mesma coisa no Apps Script, conforme mostrado no exemplo a seguir. Crie pelo menos outro usuário para que o script não precise sempre se conectar ao banco de dados como 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);
  }
}

Gravar no banco de dados

Os exemplos a seguir mostram como gravar um único registro no banco de dados e um lote de 500 registros. O agrupamento em lotes é essencial para operações em massa.

São usadas instruções parametrizadas, em que as variáveis são indicadas por ?. Para evitar ataques de injeção de SQL, use instruções parametrizadas para escapar de todos os dados fornecidos pelo usuário.

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);
  }
}

/**
 * Write 500 rows of data to a table in a single batch.
 * Recommended for faster writes
 */
function writeManyRecordsUsingExecuteBatch() {
  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 (?, ?)",
    );
    const params = [];
    for (let i = 0; i < 500; i++) {
      params.push([`Name ${i}`, `Hello, world ${i}`]);
    }

    const batch = stmt.executeBatch(params);
    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);
  }
}

Ler do banco de dados

Este exemplo demonstra como ler um grande número de registros do banco de dados, fazendo um loop no conjunto de resultados conforme necessário.

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);
  }
}

/**
 * Read up to 1000 rows of data from the table and log them.
 * Recommended for faster reads
 */
function readFromTableUsingGetRows() {
  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();
    const getRowArgs = [];
    for (let col = 0; col < numCols; col++) {
      getRowArgs.push(`getString(${col + 1})`);
    }
    const rows = results.getRows(getRowArgs.join(","));
    for (let i = 0; i < rows.length; i++) {
      console.log(rows[i].join("\t"));
    }

    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);
  }
}

Encerrar conexões

As conexões JDBC são fechadas automaticamente quando um script termina de ser executado. As chamadas únicas de google.script.run contam como uma execução completa, mesmo que a página do serviço HTML que fez a chamada permaneça aberta.

No entanto, se você souber que terminou de usar uma conexão, instrução ou conjunto de resultados antes do fim do script, feche-os manualmente chamando JdbcConnection.close, JdbcStatement.close ou JdbcResultSet.close.

Mostrar uma caixa de diálogo de alerta ou solicitação também encerra todas as conexões JDBC abertas. No entanto, outros elementos da interface —como menus ou caixas de diálogo personalizados e barras laterais com conteúdo personalizado—não são.

Google, Google Workspace e marcas e logotipos relacionados são marcas registradas da Google LLC. Todos os outros nomes de empresas e produtos são marcas registradas das empresas a que estão associados.