Class: RVGP::Plot::GoogleDrive::ExportSheets

Inherits:
Object
  • Object
show all
Defined in:
lib/rvgp/plot/google-drive/output_google_sheets.rb

Overview

This class works as a driver, to the plot subsystem. And, exports plots too a google sheets document.

Here’s the guide I used, to generate the credentials:

https://medium.com/building-rigup/wrangling-google-services-in-ruby-5493e906c84f

NOTE: The guide doesn’t exactly say it, but, make sure that your oauth client ID

is of type 'Web Application'. That will ensure that you're able to provide
a redirect URI, and use the playground...

You’ll want to write these credentials, in the following files:

config/google-secrets.yml “‘ client_id: “” project_id: “ruby-rake-accounting” client_secret: “” token_path: “google-token.yml” application_name: “rvgp” “`

config/google-token.yml “‘

default: ‘href=""https://www.googleapis.com/auth/spreadsheets"">client_id”:“”,“access_token”:“”,“refresh_token”:“”,“scope”:,“expiration_time_millis”:1682524731000’ “‘

The empty values in these files, should be populated with the values you secured following the medium link above. The only exception here, might be that refresh_token. Which, I think gets written by the googleauth library, automatically.

Defined Under Namespace

Classes: MissingSecretsFile, MissingSecretsParams

Constant Summary collapse

SV4 =

This is just a shorthand we’re using, to simply the sheets_v4 implementation

Google::Apis::SheetsV4
LOTUS_EPOCH =

Dates are expected to be provided wrt to their relative offset from this date

Date.new 1899, 12, 30
COLOR_SCHEMES =

The coloring schemes, used by our sheets. This constant… needs some work

[
  # Pink:  TODO : maybe remove this, or put into a palettes file/option
  [233, 29, 99, 255, 255, 255, 253, 220, 232]
].freeze
OOB_URI =

Our OAuth base_url

'urn:ietf:wg:oauth:2.0:oob'

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(options) ⇒ ExportSheets

Create a Spreadsheet, in a Google Drive.

Parameters:

  • options (Hash) —

    The parameters governing this Spreadsheet

Options Hash (options):

  • :secrets_file (String) —

    The path to a yaml file, containing the secrets used to login to this drive.

  • :title (String) —

    The title of this spreadsheet

  • :log_http_requests (TrueClass, FalseClass) —

    a flag to indicate whether we want to debug the http session involved in the creation of this spreadsheet

Raises:



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/rvgp/plot/google-drive/output_google_sheets.rb', line 90

def initialize(options)
  raise MissingSecretsFile unless options.key? :secrets_file

  config = Psych.load File.read(options[:secrets_file]), symbolize_names: true

  raise MissingSecretsParams unless %w[
    client_id project_id client_secret token_path application_name
  ].all? { |p| config.key? p.to_sym }

  @spreadsheet_title = options[:title]
  @service = SV4::SheetsService.new
  @service.client_options.log_http_requests = true if options[:log_http_requests]
  @client_id = Google::Auth::ClientId.from_hash(
    'installed' => {
      'client_id' => config[:client_id],
      'project_id' => config[:project_id],
      'client_secret' => config[:client_secret],
      'auth_uri' => 'https://accounts.google.com/o/oauth2/auth',
      'token_uri' => 'https://oauth2.googleapis.com/token',
      'auth_provider_x509_cert_url' => 'https://www.googleapis.com/oauth2/v1/certs',
      'redirect_uris' => ['urn:ietf:wg:oauth:2.0:oob', 'http://localhost']
    }
  )

  @token_store = Google::Auth::Stores::FileTokenStore.new(
    file: [File.dirname(options[:secrets_file]), config[:token_path]].join('/')
  )
  @application_name = config[:application_name]

  @service.client_options.application_name = @application_name
  @service.authorization = authorize

  @current_sheet_id = nil
  @now = Time.now
end

Instance Attribute Details

#current_sheet_id ⇒ String (readonly)

The id of the most recently created sheet, as provided by Google

Returns:

  • (String) —

    the current value of current_sheet_id



44
45
46
 
# File 'lib/rvgp/plot/google-drive/output_google_sheets.rb', line 44

def current_sheet_id
  @current_sheet_id
end

#spreadsheet_url ⇒ String (readonly)

The url to this Sheet, which can be used to access the sheet, by the user.

Returns:

  • (String) —

    the current value of spreadsheet_url



44
45
46
 
# File 'lib/rvgp/plot/google-drive/output_google_sheets.rb', line 44

def spreadsheet_url
  @spreadsheet_url
end

Instance Method Details

#sheet(sheet) ⇒ void

This method returns an undefined value.

Add the provided sheet, to the Spreadsheet document

Parameters:

Raises:

  • (StandardError) 


129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/rvgp/plot/google-drive/output_google_sheets.rb', line 129

def sheet(sheet)
  raise StandardError, 'Too many columns...' if sheet.columns.length > 26
  raise StandardError, 'No header...' if sheet.columns.empty?

  # Create a sheet, or update the sheet 0 title:
  if current_sheet_id.nil?
    update_sheet_title! 0, sheet.title
  else
    add_sheet! sheet.title
  end

  # Now that we have a title and sheet id, we can insert the data:
  update_spreadsheet_value! sheet.title, [sheet.columns] + sheet.rows

  # Format the sheet:
  batch_update_spreadsheet! [
    # Set the Date column:
    repeat_cell(
      create_range(1, 0, sheet.rows.count + 1),
      'userEnteredFormat.numberFormat',
      { userEnteredFormat: { numberFormat: { type: 'DATE', pattern: 'mm/dd/yy' } } }
    ),
    # Set the Money columns:
    repeat_cell(
      create_range(1, 1, sheet.rows.count + 1, sheet.columns.count),
      'userEnteredFormat.numberFormat',
      { userEnteredFormat: { numberFormat: { type: 'CURRENCY', pattern: '"$"#,##0.00' } } }
    ),
    # Format the header row text:
    repeat_cell(
      create_range(0, 0, 1, sheet.columns.count),
      'userEnteredFormat(textFormat,horizontalAlignment)',
      { userEnteredFormat: { textFormat: { bold: true }, horizontalAlignment: 'CENTER' } }
    ),
    # Color-band the rows:
    band_rows(sheet.rows.count + 1, sheet.columns.count),
    # Resize the series columns:
    update_column_width(1, sheet.columns.count, 70)
  ], skip_serialization: true

  # Add a chart!
  add_chart! sheet
end