Module: SheetsV4

Defined in:
lib/sheets_v4.rb,
lib/sheets_v4/color.rb,
lib/sheets_v4/version.rb,
lib/sheets_v4/create_credential.rb,
lib/sheets_v4/google_extensions.rb,
lib/sheets_v4/validate_api_objects.rb,
lib/sheets_v4/api_object_validation.rb,
lib/sheets_v4/convert_dates_and_times.rb,
lib/sheets_v4/google_extensions/sheet.rb,
lib/sheets_v4/google_extensions/spreadsheet.rb,
lib/sheets_v4/google_extensions/sheets_service.rb,
lib/sheets_v4/api_object_validation/load_schemas.rb,
lib/sheets_v4/api_object_validation/resolve_schema_ref.rb,
lib/sheets_v4/validate_api_objects/validate_api_object.rb,
lib/sheets_v4/api_object_validation/validate_api_object.rb,
lib/sheets_v4/api_object_validation/traverse_object_tree.rb

Overview

Copyright (c) 2022 Yahoo frozen_string_literal: true

Defined Under Namespace

Modules: ApiObjectValidation, GoogleExtensions Classes: Color, ConvertDatesAndTimes, CreateCredential

Constant Summary collapse

VERSION =

The version of this gem

'0.9.0'

Date and DateTime Conversions collapse

Validation collapse

Colors collapse

Date and DateTime Conversions collapse

Class Method Summary collapse

Class Attribute Details

.default_spreadsheet_tz

This method returns an undefined value.

Set the default time zone for date and time conversions

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)

If you want to set the timezone to the time zone of the system's local time, you could use the timezone_local gem.

Examples:

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'

Set the time zone to the system's local time

require 'timezone_local'
SheetsV4.default_spreadsheet_tz = TimeZone::Local.get.name


146
147
148
 
# File 'lib/sheets_v4.rb', line 146

def default_spreadsheet_tz
  @default_spreadsheet_tz || raise('default_spreadsheet_tz not set')
end

Class Method Details

.api_object_schema_names(logger: Logger.new(nil)) ⇒ Array<String>

List the names of the schemas available to use in the Google Sheets API

Examples:

List the name of the schemas available

SheetsV4.api_object_schema_names #=> ["add_banding_request", "add_banding_response", ...]

Returns:

  • (Array<String>) —

    the names of the schemas available



91
92
93
 
# File 'lib/sheets_v4.rb', line 91

def api_object_schema_names(logger: Logger.new(nil))
  SheetsV4::ApiObjectValidation::LoadSchemas.new(logger:).call.keys.sort
end

.color(name) ⇒ Hash

Given the name of the color, return a Google Sheets API color object

Available color names are listed using SheetsV4.color_names.

The object returned is frozen. If you want a color you can change (for instance, to adjust the alpha attribute), you must clone the object returned.

Examples:

SheetsV4::Color.color(:red) #=> { "red": 1.0, "green": 0.0, "blue": 0.0 }

Parameters:

  • name (#to_sym) —

    the name of the color requested

Returns:

  • (Hash) —

    The color requested e.g. { "red": 1.0, "green": 0.0, "blue": 0.0 }



111
112
113
 
# File 'lib/sheets_v4.rb', line 111

def color(name)
  SheetsV4::Color::COLORS[name.to_sym] || raise("Color #{name} not found")
end

.color_names ⇒ Array<Symbol>

List the names of the colors available to use in the Google Sheets API

Examples:

SheetsV4::Color.color_names #=> [:black, :white, :red, :green, :blue, :yellow, :magenta, :cyan, ...]

Returns:

  • (Array<Symbol>) —

    the names of the colors available



122
 
# File 'lib/sheets_v4.rb', line 122

def color_names = SheetsV4::Color::COLORS.keys

.date_to_gs(date) ⇒ Float, String

Convert a Ruby Date object to a Google Sheet date value

Uses the time zone given by SheetsV4.default_spreadsheet_tz.

Examples:

with a Date object

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
date = Date.parse('2021-05-17')
SheetsV4.date_to_gs(date) #=> 44333

with a DateTime object

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
date_time = DateTime.parse('2021-05-17 11:36:00 UTC')
SheetsV4.date_to_gs(date_time) #=> 44333

Parameters:

  • date (DateTime, Date, nil) —

    the date to convert

Returns:

  • (Float, String) —

    the value to sstore in a Google Sheet cell Returns a Float if date is not nil; otherwise, returns an empty string



183
184
185
 
# File 'lib/sheets_v4.rb', line 183

def date_to_gs(date)
  default_date_and_time_converter.date_to_gs(date)
end

.datetime_to_gs(datetime) ⇒ Float, String

Convert a Ruby DateTime object to a Google Sheets value

Examples:

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
date_time = DateTime.parse('2021-05-17 11:36:00 UTC')
SheetsV4.datetime_to_gs(date_time) #=> 44333.191666666666

Parameters:

  • datetime (DateTime, nil) —

    the date and time to convert

Returns:

  • (Float, String) —

    the value to store in a Google Sheet cell Returns a Float if datetime is not nil; otherwise, returns an empty string.



222
223
224
 
# File 'lib/sheets_v4.rb', line 222

def datetime_to_gs(datetime)
  default_date_and_time_converter.datetime_to_gs(datetime)
end

.default_date_and_time_converter ⇒ SheetsV4::ConvertDatesAndTimes

This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.

The default converter for dates and times

Returns:



160
161
162
 
# File 'lib/sheets_v4.rb', line 160

def default_date_and_time_converter
  @default_date_and_time_converter ||= SheetsV4::ConvertDatesAndTimes.new(default_spreadsheet_tz)
end

.gs_to_date(gs_value) ⇒ Date?

Convert a Google Sheets date value to a Ruby Date object

Examples:

with a date value

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
gs_value = 44333
SheetsV4.gs_to_date(gs_value) #=> #<Date: 2021-05-17 ...>

with a date and time value

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
gs_value = 44333.191666666666
SheetsV4.gs_to_date(gs_value) #=> #<Date: 2021-05-17 ...>

Parameters:

  • gs_value (Float, "", nil) —

    the value from the Google Sheets cell

Returns:

  • (Date, nil) —

    the value represented by gs_date

    Returns a Date object if a Float was given; otherwise, returns nil if an empty string or nil was given.



206
207
208
 
# File 'lib/sheets_v4.rb', line 206

def gs_to_date(gs_value)
  default_date_and_time_converter.gs_to_date(gs_value)
end

.gs_to_datetime(gs_value) ⇒ DateTime?

Convert a Google Sheets date time value to a DateTime object

Time is rounded to the nearest second. The DateTime object returned is in the time zone given by SheetsV4.default_spreadsheet_tz.

Examples:

SheetsV4.default_spreadsheet_tz = 'America/Los_Angeles'
gs_value = 44333.191666666666
SheetsV4.gs_to_datetime(gs_value) #=> #<DateTime: 2021-05-17T04:35:59-07:00 ...>

Parameters:

  • gs_value (Float, "", nil) —

    the value from the Google Sheets cell

Returns:

  • (DateTime, nil) —

    the value represented by gs_datetime Returns a DateTime object if a Float was given; otherwise, returns nil if an empty string or nil was given.



242
243
244
 
# File 'lib/sheets_v4.rb', line 242

def gs_to_datetime(gs_value)
  default_date_and_time_converter.gs_to_datetime(gs_value)
end

.sheets_service(credential_source: nil, scopes: nil, credential_creator: SheetsV4::CreateCredential) ⇒ Object

Create a new Google::Apis::SheetsV4::SheetsService object

Simplifies creating and configuring a the credential.

Examples:

using the credential in ~/.google-api-credential

SheetsV4.sheets_service

using a credential passed in as a string

credential_source = File.read(File.expand_path('~/.google-api-credential.json'))
SheetsV4.sheets_service(credential_source:)

using a credential passed in as an IO

credential_source = File.open(File.expand_path('~/.google-api-credential.json'))
SheetsV4.sheets_service(credential_source:)

Parameters:

  • credential_source (nil, String, IO, Google::Auth::*) (defaults to: nil) —

    may be either an already constructed credential, the credential read into a String or an open file with the credential ready to be read. Passing nil will result in the credential being read from ~/.google-api-credential.json.

  • scopes (Object, Array) (defaults to: nil) —

    one or more scopes to access.

  • credential_creator (#credential) (defaults to: SheetsV4::CreateCredential) —

    Used to inject the credential creator for testing.

Returns:

  • a new SheetsService instance



51
52
53
54
55
56
57
58
 
# File 'lib/sheets_v4.rb', line 51

def sheets_service(credential_source: nil, scopes: nil, credential_creator: SheetsV4::CreateCredential)
  credential_source ||= File.read(File.expand_path('~/.google-api-credential.json'))
  scopes ||= [Google::Apis::SheetsV4::AUTH_SPREADSHEETS]

  Google::Apis::SheetsV4::SheetsService.new.tap do |service|
    service.authorization = credential_creator.call(credential_source, scopes)
  end
end

.validate_api_object(schema_name:, object:, logger: Logger.new(nil))

This method returns an undefined value.

Validate the object using the named JSON schema

The JSON schemas are loaded from the Google Disocvery API. The schemas names are returned by SheetsV4.api_object_schema_names.

Examples:

schema_name = 'batch_update_spreadsheet_request'
object = { 'requests' => [] }
SheetsV4.validate_api_object(schema_name:, object:)

Parameters:

  • schema_name (String) —

    the name of the schema to validate against

  • object (Object) —

    the object to validate

  • logger (Logger) (defaults to: Logger.new(nil)) —

    the logger to use for logging error, info, and debug message

Raises:

  • (RuntimeError) —

    if the object does not conform to the schema



80
81
82
 
# File 'lib/sheets_v4.rb', line 80

def validate_api_object(schema_name:, object:, logger: Logger.new(nil))
  SheetsV4::ApiObjectValidation::ValidateApiObject.new(logger:).call(schema_name:, object:)
end