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/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

Overview

Unofficial helpers for the Google Sheets V4 API

Defined Under Namespace

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

Constant Summary collapse

VERSION =

The version of this gem

'0.10.3'

Date and DateTime Conversions 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


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

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

Class Method Details

.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 }



75
76
77
 
# File 'lib/sheets_v4.rb', line 75

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



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

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



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

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.



186
187
188
 
# File 'lib/sheets_v4.rb', line 186

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:



124
125
126
 
# File 'lib/sheets_v4.rb', line 124

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.



170
171
172
 
# File 'lib/sheets_v4.rb', line 170

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.



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

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



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

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