DriveV3

Unofficial helpers and extensions for the Google Drive V3 API

Gems in the Google API helper, extensions, and examples series:

• discovery_v1
• drive_v3
• sheets_v4

• Contents
• Installation
• Examples
• Important links for programming Google Drive
  • DriveV3 documenation
  • General API documentation
  • Ruby implementation of the Drive API
  • Other links
• Getting started
  • Create a Google Cloud project
  • Enable the APIs you want to use
  • Download a Google API credential file
• Usage
  • Obtaining an authenticated DriveService
  • Building a request
    • Method 1: constructing requests using Google::Apis::SheetsV4::* objects
    • Method 2: constructing requests using hashes
    • Which method should be used?
  • Validating requests
  • Google Extensions
    • SheetsService Extensions
    • Spreadsheet Extensions
    • Sheet Extensions
  • Working with dates and times
  • Working with colors
• Development
• Contributing
• License

Install the gem and add to the application's Gemfile by executing:

bundle add drive_v3

If bundler is not being used to manage dependencies, install the gem by executing:

gem install drive_v3

Many examples can be found in the examples directory of this project. The examples have a README of their own which describes each example.

Prior to running an example, clone this project and run bundle install in the project's root working directory.

Run an example using bundle exec. For example, to run the set_background_color1 example:

bundle exec examples/set_background_color1

This Gem's YARD documentation is hosted on [rubydoc.info]https://rubydoc.info/gems/drive_v3/.

• Google Drive API Overview
• Google Drive API Reference
• Discovery Document for the Drive API

• DriveService Class
• All Other Drive Classes

• None yet!

In order to use this gem, you will need to obtain a Google API service account credential following the instructions below.

Create a Google Cloud project using these directions.

Enable the Drive API for this project using these directions.

Create a service account and download a credential file using these directions.

You can store the download credential files anywhere on your system. The recommended location is ~/.google-api-credential.json since this is default credential file that DriveV3.drive_service uses.

Detailed API documenation is hosted on rubygems.org.

Typically, to construct an authenticated DriveService object where the credential is read from a file, the following code is required:

require 'googleauth'
drive_service = Google::Apis::DriveV3::DriveService.new
credential = File.read(File.expand_path('~/google-api-credential.json')) do |credential_source|
  scopes = Google::Apis::DriveV4::AUTH_DRIVE
  options = { json_key_io: credential_source, scope: scopes }
  Google::Auth::DefaultCredentials.make_creds(options).tap(&:fetch_access_token)
end
drive_service.authorization = credential

The SheetsV4.sheets_service method simplifies this a bit.

By default, the credential is read from ~/.google-api-credential.json. In that case, an authenticated SheetsService object can be obtained with one method call:

sheets_service = SheetsV4.sheets_service

If the credential is stored somewhere else, pass the credential_source to SheetsV4.sheets_service manually. credential_source can be a String:

sheets_service = SheetsV4.sheets_service(credential_source: File.read('credential.json'))

an IO object:

sheets_service = File.open('credential.json') do |credential_source|
  SheetsV4.sheets_service(credential_source:)
end

or an already constructed Google::Auth::* object.

To use the Sheets API, you need to construct JSON formatted requests.

These requests can be constructed using two different methods:

1. constructing requests using Google::Apis::SheetsV4::* objects or
2. constructing requests using hashes

The following two sections show how each method can be used to construct a request to update a row of values in a sheet.

For these two examples, values in the values array will be written to the sheet whose ID is 0. The values will be written one per row starting at cell A1.

values = %w[one two three four] # 'one' goes in A1, 'two' goes in A2, etc.

The method SheetsService#batch_update_spreadsheet will be used to write the values. This method takes a batch_update_spreadsheet_request object with a update_cells request that defines the update to perform.

When using this method, keep the Ruby source file containing the SheetsService class (google/apis/drive_v3/service.rb) and the Ruby source file containing the defitions of the request & data classes (lib/google/apis/drive_v3/classes.rb) open for easy searching. These files will give you all the information you need to construct valid requests.

Here is the example constructing requests using Google::Apis::SheetsV4::* objects

def values = %w[one two three four]

def row_data(value)
  Google::Apis::SheetsV4::RowData.new(
    values: [
      Google::Apis::SheetsV4::CellData.new(
        user_entered_value:
          Google::Apis::SheetsV4::ExtendedValue.new(string_value: value.to_s)
      )
    ]
  )
end

def rows
  values.map { |value| row_data(value) }
end

def write_values_request
  fields = 'user_entered_value'
  start = Google::Apis::SheetsV4::GridCoordinate.new(
    sheet_id: 0, row_index: 0, column_index: 0
  )
  Google::Apis::SheetsV4::Request.new(
    update_cells: Google::Apis::SheetsV4::UpdateCellsRequest.new(rows:, fields:, start:)
  )
end

requests = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new(requests: [write_values_request])

spreadsheet_id = '18FAcgotK7nDfLTOTQuGCIjKwxkJMAguhn1OVzpFFgWY'
SheetsV4.sheets_service.batch_update_spreadsheet(spreadsheet_id, requests)

When constructing requests using this method, keep the Google Sheets Rest API Reference documentation open. In particular, the Batch Update Requests page is particularly useful for building spreadsheet batch update requests.

One caveat to keep in mind is that the Rest API documents object properties using Camel case BUT the Ruby API requires snake case.

For instance, the Rest API documents the properties for a grid coordinate to be "sheetId", "rowIndex", and "columnIndex". However, in the Ruby API, you should construct this object using snake case:

grid_coordinate = { sheet_id: 0, row_index: 0, column_index: 0 }

Here is the example constructing requests using hashes:

def values = %w[one two three four]

def rows
  values.map do |value|
    { values: [{ user_entered_value: { string_value: value } }] }
  end
end

def write_values_request
  fields = 'user_entered_value'
  start = { sheet_id: 0, row_index: 0, column_index: 0 }
  { update_cells: { rows:, fields:, start: } }
end

requests = { requests: [write_values_request] }

spreadsheet_id = '18FAcgotK7nDfLTOTQuGCIjKwxkJMAguhn1OVzpFFgWY'
response = SheetsV4.sheets_service.batch_update_spreadsheet(spreadsheet_id, requests)

Either method will do the same job. I prefer "Method 2: constructing requests using hashes" because my code is more concise and easy to read.

While either method can produce a malformed request, "Method 2" is more likely to result in malformed requests. Unfortunately, when given a malformed request, the Google Sheets API will do one of following depending on the nature of the problem:

1. Raise a Google::Apis::ClientError with some additional information
2. Raise a Google::Apis::ClientError with no additional information (this the most common result)
3. Not return an error with some of the batch requests not having the expected outcome

Luckily, this library provides a way to validate that requests are valid and identifies precisely where the request objects do not conform to the API description. That is the subject of the next section Validating requests.

The SheetsV4.validate_api_object method can be used to validate request objects prior to using them in the Google Sheets API.

This method takes a schema_name and an object to validate. Schema names can be listed using SheetsV4.api_object_schema_names.

This method will either return true if object conforms to the schema OR it will raise a RuntimeError noting where the object structure did not conform to the schema.

In the previous examples (see Building a request), the following line can be inserted after the requests = ... line to validate the request:

SheetsV4.validate_api_object(schema: 'batch_update_spreadsheet_request', object: requests)

The SheetsV4::GoogleExtensions module provides extensions to the Google::Apis::SheetsV4 classes to simplify use of the SheetsV4 API.

These extensions are not loaded by default and are not required to use other parts of this Gem. To enable these extension, you must:

require 'drive_v3/google_extensions'

Functionality is added to get_spreadsheet to set the sheets_service attribute on the returned spreadsheet and set the sheets_service and spreadsheet attributes on the sheets contained in the spreadsheet.

This can simplify complex spreadsheet updates because you won't have to pass a sheets_service, spreadsheet, and sheet objects separately.

The sheets_service attribute is added and is set by SheetsService#get_spreadsheet.

Convenience methods for getting sheets within the spreadsheet are added:

• sheet(id_or_title): returns the sheet matching the id or title given
• sheet_id(title): returns the ID for the sheet matching the title given
• each_sheet(ids_or_titles): enumerates the sheets within a spreadsheet matching the given IDs or titles.

The sheets_service and spreadsheet attributes are added. Both are set when the sheet's spreadsheet is loaded by SheetsService#get_spreadsheet.

Google Sheets, similar to other spreadsheet programs, stores dates and date-time values as numbers. This system makes it easier to perform calculations with dates and times.

This gem provides two sets of equavalent conversion methods. The first set is defined as class methods on the SheetsV4 class.

• SheetsV4.date_to_gs(date) returns a numeric cell value
• SheetsV4.gs_to_date(cell_value) returns a Date object
• SheetsV4.datetime_to_gs(datetime) returns a numeric cell value
• SheetsV4.gs_to_datetime(cell_value) returns a DateTime object

In order to convert to and from spreadsheet values, the spreadsheet timezone must be known. A spreadsheet's timezone is found in the Google Sheets spreadsheet object's properties:

SheetsV4.default_spreadsheet_tz = spreadsheet.properties.time_zone

If a time zone is not set using SheetsV4.default_spreadsheet_tz, a RuntimeError will be raised when any of the above methods are used.

Here is an example of how the timezone can change the values fetched from the spreadsheet:

cell_value = 44333.191666666666

SheetsV4.default_spreadsheet_tz = 'America/New_York'
datetime = SheetsV4.gs_to_datetime(cell_value) #=> Mon, 17 May 2021 04:36:00 -0400
datetime.utc #=> 2021-05-17 08:36:00 UTC

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
datetime = SheetsV4.gs_to_datetime(cell_value) #=> Mon, 17 May 2021 04:36:00 -0700
datetime.utc #=> 2021-05-17 11:36:00 UTC

Valid time zone names are those listed in one of these two sources:

• ActiveSupport::TimeZone.all.map { |tz| tz.tzinfo.name }
• ActiveSupport::TimeZone.all.map(&:name)

The SheetsV4 methods works well if the spreadsheet timezone is constant through the run of the program. If this is not the case -- for instance when working with multiple spreadsheets whose timezones may be different -- then use SheetsV4::ConvertDatesAndTimes.

Each instance of SheetsV4::ConvertDatesAndTimes has it's own spreadsheet timezone used in the conversions. Instance methods for this class are the same as the date conversion methods on the SheetsV4 class.

Example:

cell_value = 44333.191666666666
converter = SheetsV4::ConvertDatesAndTimes.new('America/Los_Angeles')
datetime = SheetsV4.gs_to_datetime(cell_value) #=> Mon, 17 May 2021 04:36:00 -0700
datetime.utc #=> 2021-05-17 11:36:00 UTC

Color objects (with appropriate :red, :green, :blue values) can be retrieved by name using SheetsV4.color(:black) or SheetsV4::Color.black (these are equivalent).

Color names can be listed using SheetsV4.color_names.

The color object returned is a Hash as follows:

SheetsV4::Color.black #=> {:red=>0.0, :green=>0.0, :blue=>0.0}

After checking out the repo, run bin/setup to install dependencies and then, run rake to run the tests, static analysis, etc. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.

Bug reports and pull requests are welcome on the main-branch/drive_v3 GitHub project.

The gem is available as open source under the terms of the MIT License.