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 }



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, ...]



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



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



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



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



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



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:)



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