外部数据选项

我们建议您的应用使用 Google Cloud SQL 存储数据。不过,您的应用还可以执行以下操作:

  • 使用 JDBC 将数据写入外部 MySQL 数据库。JDBC API 是将应用连接到数据库的业界标准。
  • 使用 HTTP 请求和 REST API。

将数据存储在外部数据库中

您可以创建一个通过 Apps 脚本 JDBC 服务将数据存储在非 Google MySQL 数据库中的应用。例如,您可以使用脚本将一行数据写入外部数据库:

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

详细了解如何将数据写入外部数据库以及如何从外部数据库读取数据。

试用 JDBC 示例应用

我们已经创建了一个示例应用,这样您就可以详细了解到,在构建一个从非 Google MySQL 数据库读取数据以及将数据写入该数据库的应用时所需要的脚本、界面元素和计算模型。请查看应用的 README 文件,了解如何自定义该应用以连接您的数据库。

调用 REST 服务

您可以使用 Representational State Transfer (REST) API 创建一个从第三方服务检索信息的应用。例如,以下脚本获取用户输入的地点,并从外部服务请求该地点的天气预报:

/**
     * 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;
    }
    

试用 REST 示例应用

我们已经创建了一个示例应用,这样您就可以详细了解到,在构建一个调用 REST API 的应用时所需要的脚本、界面元素和计算模型。