Class: SheetsV4::ConvertDatesAndTimes

Inherits:
Object
  • Object
show all
Defined in:
lib/sheets_v4/convert_dates_and_times.rb

Overview

Convert between Ruby Date and DateTime objects and Google Sheets values

Google Sheets uses decimal values to represent dates and times. These conversion routines allows converting from Ruby Date and DateTime objects and values that Google Sheets recognize.

DateTime objects passed to datetime_to_gs or date_to_gs are converted to the time zone of the spreadsheet given in the initializer. DateTime objects returned by gs_to_datetime are always in the spreadsheet's time zone.

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)

Examples:

tz = spreadsheet.properties.time_zone #=> e.g. 'America/Los_Angeles'
converter = SheetsV4::ConvertDatesAndTimes.new(tz)
date = Date.parse('1967-03-15')
converter.date_to_gs(date) #=> 24546
converter.gs_to_date(24546) #=> #<Date: 1967-03-15 ((2439565j,0s,0n),+0s,2299161j)>

date_time = DateTime.parse('2021-05-17 11:36:00 UTC')
converter.datetime_to_gs(date_time) #=> 44333.191666666666
converter.gs_to_datetime(44333.191666666666)
#=> #<DateTime: 2021-05-17T11:36:00+00:00 ((2459352j,41760s,0n),+0s,2299161j)>

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(spreadsheet_tz) ⇒ ConvertDatesAndTimes

Initialize the conversion routines for a spreadsheet

Examples:

time_zone = 'America/Los_Angeles'
converter = SheetsV4::ConvertDatesAndTimes.new(time_zone)

Parameters:

  • spreadsheet_tz (String) —

    the time zone set in the spreadsheet properties

Raises:

  • (RuntimeError) —

    if the time zone is not valid



60
61
62
63
 
# File 'lib/sheets_v4/convert_dates_and_times.rb', line 60

def initialize(spreadsheet_tz)
  @spreadsheet_tz = ActiveSupport::TimeZone.new(spreadsheet_tz)
  raise "Invalid time zone '#{spreadsheet_tz}'" unless @spreadsheet_tz
end

Instance Attribute Details

#spreadsheet_tz ⇒ ActiveSupport::TimeZone (readonly)

The time zone passed into the initializer

Examples:

time_zone = 'UTC'
converter = SheetsV4::ConvertDatesAndTimes.new(time_zone)
converter.spreadsheet_tz
#=> #<ActiveSupport::TimeZone:0x00007fe39000f908 @name="America/Los_Angeles", ...>

Returns:

  • (ActiveSupport::TimeZone) —

    the time zone



48
49
50
 
# File 'lib/sheets_v4/convert_dates_and_times.rb', line 48

def spreadsheet_tz
  @spreadsheet_tz
end

Instance Method Details

#date_to_gs(date) ⇒ Float, String

Convert a Ruby Date object to a Google Sheets date value

The Google Sheets date value is a float.

Examples:

with a Date object

time_zone = 'America/Los_Angeles'
converter = SheetsV4::ConvertDatesAndTimes.new(time_zone)
date = Date.parse('2021-05-17')
converter.date_to_gs(date) #=> 44333

with a DateTime object

time_zone = 'America/Los_Angeles'
converter = SheetsV4::ConvertDatesAndTimes.new(time_zone)
date_time = DateTime.parse('2021-05-17 11:36:00 UTC')
converter.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 Sheets cell Returns a Float if date is not nil; otherwise, returns an empty string



133
134
135
136
137
138
139
 
# File 'lib/sheets_v4/convert_dates_and_times.rb', line 133

def date_to_gs(date)
  return datetime_to_gs(date).to_i if date.is_a?(DateTime)

  return (date - gs_epoch_start_date).to_i if date.is_a?(Date)

  ''
end

#datetime_to_gs(datetime) ⇒ Float, String

Convert a Ruby DateTime object to a Google Sheets date time value

Examples:

time_zone = 'America/Los_Angeles'
converter = SheetsV4::ConvertDatesAndTimes.new(time_zone)
date_time = DateTime.parse('2021-05-17 11:36:00 UTC')
converter.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 Sheets cell Returns a Float if datetime is not nil; otherwise, returns an empty string.



78
79
80
81
82
83
 
# File 'lib/sheets_v4/convert_dates_and_times.rb', line 78

def datetime_to_gs(datetime)
  return '' unless datetime

  time = datetime.to_time.in_time_zone(spreadsheet_tz)
  unix_to_gs_epoch(replace_time_zone(time, 'UTC').to_i)
end

#gs_to_date(gs_date) ⇒ Date?

Convert a Google Sheets date value to a Ruby Date object

Examples:

with a Date value

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

with a Date and Time value

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

Parameters:

  • gs_date (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.



161
162
163
164
165
166
167
 
# File 'lib/sheets_v4/convert_dates_and_times.rb', line 161

def gs_to_date(gs_date)
  return nil if gs_date.nil? || gs_date == ''

  raise 'gs_date is a string' if gs_date.is_a?(String)

  (gs_epoch_start_date + gs_date.to_i)
end

#gs_to_datetime(gs_datetime) ⇒ 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 spreadsheet's time zone given in the initiaizer.

Examples:

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

Parameters:

  • gs_datetime (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.



102
103
104
105
106
107
108
109
110
 
# File 'lib/sheets_v4/convert_dates_and_times.rb', line 102

def gs_to_datetime(gs_datetime)
  return nil if gs_datetime.nil? || gs_datetime == ''

  raise 'gs_datetime is a string' if gs_datetime.is_a?(String)

  unix_epoch_datetime = gs_to_unix_epoch(gs_datetime.to_f)
  time = Time.at_without_coercion(unix_epoch_datetime, in: 'UTC')
  replace_time_zone(time, spreadsheet_tz).to_datetime
end