External data options

We recommend that your apps use Google Cloud SQL to store 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();
}

Note: This script is for demonstration purposes only. Never store passwords in production apps.

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 location that is input by the user and requests the weather forecast for that location from an outside service:

/**
 * Calls Geonames.org to fetch coordinates for a provided US city or location.
 * @param {string} location A city or location in the US.
 * @return {object} an object with lng and lat fields.
 */
function getLocationCoordinates_(location) {
  var url = 'http://api.geonames.org/searchJSON?q=' + escape(location) +
      '&maxRows=1&username=' + GEONAMES_ACCOUNT;

  console.log('Geonames url is: ' + url);
  console.log('Fetching lat & lng from geonames.org for location \'' +
                location + '\'');

  var response = JSON.parse(UrlFetchApp.fetch(url));
  return response.geonames[0];
}


/**
 * Calls Weather Service with lat lng to get office location url.
 * @param {string} lat Latitude.
 * @param {string} lng Longitude.
 * @return {object} an object with the forecast office info.
 */
function getOfficeInfo_(lat, lng) {
  var url = 'http://api.weather.gov/points/' + lat + ',' + lng;
  console.log('Forecast office is: ' + url);

  var fetchParameters = {
    headers: {
      'Accept': 'application/json'
    }
  };

  var response = UrlFetchApp.fetch(url, fetchParameters);
  return JSON.parse(response);
}


/**
 * Get weather forecast from provided office.
 * @param {string} forecastUrl URL with the office forecast.
 * @return {object} an object with the forecast.
 */
function getWeatherForecast_(forecastUrl) {
  console.log('Forecast url is: ' + forecastUrl);

  var fetchParameters = {
    headers: {
      'Accept': 'application/json'
    }
  };

  var response = UrlFetchApp.fetch(forecastUrl, fetchParameters);
  return JSON.parse(response);
}


/**
 * Calls REST services to fetch weather for a provided US city or location.
 * @param {string} location A city or location in the US.
 * @return {object} an object with forecast and city/state.
 */
function getWeather_(location) {
  var coordinates = getLocationCoordinates_(location);

  if (coordinates !== undefined ) {
    var office = getOfficeInfo_(coordinates.lat, coordinates.lng);
    var forecastUrl = office.properties.forecast;
    var city = office.properties.relativeLocation.properties.city;
    var state = office.properties.relativeLocation.properties.state;
    var citystate = city + ', ' + state;
    var forecast = getWeatherForecast_(forecastUrl);
    return {
      forecast: forecast,
      citystate: citystate
    };
  } else {
    return null;
  }
}


/**
 * Calculates the weather for a location for the Weather calculated model.
 * @param {Query} query Query object owned by the datasource.
 * @return {Array<Weather>} set of records for the Weather datasource.
 */
function calculateWeatherModel_(query) {
  var location = query.parameters.Location;
  var response;
  try {
    response = getWeather_(location);
  } catch (error) {
    throw new Error('Unable to locate provided city: \"' + location + '\".');
  }

  if (response === null) {
    throw new Error('Unable to locate provided city: \"' + location + '\".');
  }

  var forecastPeriods = response.forecast.properties.periods;
  var citystate = response.citystate;

  var records = forecastPeriods.map(function (period) {
    var record = app.models.Weather.newRecord();

    record.Citystate = citystate;
    record.ShortForecast = period.shortForecast;
    record.ForecastName = period.name;
    record.DetailedForecast = period.detailedForecast;
    record.ImageUrl = period.icon;
    record.Temperature = period.temperature;
    record.TemperatureUnit = period.temperatureUnit;
    record.WindSpeed = period.windSpeed;
    record.WindDirection = period.windDirection;

    return 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 that are needed to build an app that calls REST APIs.