External data options

We recommend your apps use Google Cloud SQL to store their data. However, your app can also:

  • write data to an external MySQL database using JDBC. The JDBC API is an industry standard for connecting applications to databases.
  • use HTTP requests and REST APIs.

Store data in an external database

You can create an app that uses the Apps Script JDBC service to store data in a non-Google MySQL database. For example, you could use a script to write a row of data to an external database:

// Replace the variables in this block with real values.
var address = 'database_IP_address';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

// Write one row of data to a table.
function writeOneRecord() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

   var stmt = conn.prepareStatement('INSERT INTO entries '
      + '(guestName, content) values (?, ?)');
  stmt.setString(1, 'First Guest');
  stmt.setString(2, 'Hello, world');
  stmt.execute();
}

Learn more about how to write to and read from an external database.

Try the JDBC sample app

We've created a sample app so you can learn more about the scripts, UI elements, and calculated models needed to build an app that reads and writes data to a non-Google MySQL database. Check the app's README to find out how to customize the app to connect your database.

Call REST services

You can create an app that retrieves information from third-party services using REpresentational State Transfer (REST) APIs. For example, the following script takes a currency input by the user and requests the exchange rate from an outside service:

/**
 * Calls REST service to fetch exchange rates for a provided currency.
 * @param {string} currency ISO 4217 currency code to fetch rates for.
 */
function getExchangeRates_(currency) {
  var url = 'https://api.fixer.io/latest?base=' + currency;
  try {
    var response = UrlFetchApp.fetch(url);
    return JSON.parse(response);
  } catch (err) {
    console.error('Error fetching rates: ' + err);
  }
}

/**
 * Calculates the exchange rates for a given currency for the ExchangeRates
 * calculated model.
 * @param {Query} query Query object owned by the datasource.
 * @return {Array<ExchangeRates>} Set of records for the ExchangeRates
 * datasource.
 */
function calculateExchangeRatesModel_(query) {
  var records = [];

  var currency = query.parameters.Currency;
  var response = getExchangeRates_(currency);

  for (var rate in response.rates) {
    var record = app.models.ExchangeRates.newRecord();
    record.CurrencySymbol = rate;
    record.Value = response.rates[rate];

    records.push(record);
  }

  return records;
}

Try the REST sample app

We've created a sample app so you can learn more about the scripts, UI elements, and calculated models needed to build an app that calls REST APIs.