Unofficial helpers and extensions for the Google Drive V3 API
Gems in the Google API helper, extensions, and examples series:
- Contents
- Installation
- Examples
- Important links for programming Google Drive
- Getting started
- Usage
- Development
- Contributing
- License
Install the gem and add to the application's Gemfile by executing:
bundle add drive_v3If bundler is not being used to manage dependencies, install the gem by executing:
gem install drive_v3Many 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_color1This Gem's YARD documentation is hosted on [rubydoc.info]https://rubydoc.info/gems/drive_v3/.
- 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 = credentialThe 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_serviceIf 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:)
endor 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:
- constructing requests using
Google::Apis::SheetsV4::*objects or - 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:
- Raise a
Google::Apis::ClientErrorwith some additional information - Raise a
Google::Apis::ClientErrorwith no additional information (this the most common result) - 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 givensheet_id(title): returns the ID for the sheet matching the title giveneach_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 valueSheetsV4.gs_to_date(cell_value)returns a Date objectSheetsV4.datetime_to_gs(datetime)returns a numeric cell valueSheetsV4.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_zoneIf 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 UTCValid 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 UTCColor 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.
All commit messages must follow the Conventional Commits standard. This helps us maintain a clear and structured commit history, automate versioning, and generate changelogs effectively.
To ensure compliance, this project includes:
-
A git commit-msg hook that validates your commit messages before they are accepted.
To activate the hook, you must have node installed and run
npm install. -
A GitHub Actions workflow that will enforce the Conventional Commit standard as part of the continuous integration pipeline.
Any commit message that does not conform to the Conventional Commits standard will cause the workflow to fail and not allow the PR to be merged.
All pull requests must be merged using rebase merges. This ensures that commit messages from the feature branch are preserved in the release branch, keeping the history clean and meaningful.
The gem is available as open source under the terms of the MIT License.