Class: GoogleSheets

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

Overview

googleapis.dev/ruby/google-api-client/latest/Google/Apis/SheetsV4/Request.html

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(ssid, **opts) ⇒ GoogleSheets

Returns a new instance of GoogleSheets.



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/googlesheets.rb', line 16

def initialize(ssid, **opts)
  @ssid = ssid =~ /^https?:/ ? ssid.split('/')[5] : ssid

  @json = opts[:credentials] || 'credentials.json'
  @yaml = opts[:token      ] || 'token.yaml'

  if opts[:debug] == true
    $stdout.sync = true
    Google::Apis.logger = Logger.new(STDERR)
    Google::Apis.logger.level = Logger::DEBUG
  end

  @api = Google::Apis::SheetsV4::SheetsService.new
  @api.client_options.application_name    = opts[:app] || "Ruby"
  @api.client_options.application_version = opts[:ver] || "1.0.0"
  @api.authorization = authorize
end

Instance Attribute Details

#api ⇒ Object

Returns the value of attribute api.



14
15
16
 
# File 'lib/googlesheets.rb', line 14

def api
  @api
end

Instance Method Details

#authorize ⇒ Object 



34
35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/googlesheets.rb', line 34

def authorize
  idno = Google::Auth::ClientId.from_file(@json)
  repo = Google::Auth::Stores::FileTokenStore.new(file: @yaml)
  auth = Google::Auth::UserAuthorizer.new(idno, Google::Apis::SheetsV4::AUTH_SPREADSHEETS, repo)
  oobs = 'urn:ietf:wg:oauth:2.0:oob'
  user = 'default'
  info = auth.get_credentials(user) || begin
    href = auth.get_authorization_url(base_url: oobs)
    puts "Open the following URL and paste the code here:\n" + href
    info = auth.get_and_store_credentials_from_code(user_id: user, code: gets, base_url: oobs)
  end
end

#biject(x) ⇒ Object

a=1, z=26, aa=27, az=52, ba=53, aaa=703



64
65
66
67
68
69
70
71
72
73
 
# File 'lib/googlesheets.rb', line 64

def biject(x) # a=1, z=26, aa=27, az=52, ba=53, aaa=703
  case x
  when String
    x.each_char.inject(0) {|n,c| (n * 26) + (c.ord & 31) }
  when Integer
    s = []
    s << (((x -= 1) % 26) + 65).chr && x /= 26 while x > 0
    s.reverse.join
  end
end

#filter_criteria(hash) ⇒ Object 



75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/googlesheets.rb', line 75

def filter_criteria(hash)
  hash.inject({}) do |h, (k,v)|
    l = Array(v)
    h[biject(k.to_s) - 1] = {
      condition: {
        type: "TEXT_EQ",
        values: l.map {|e| { user_entered_value: e} },
      }
    }
    h
  end
end

#hex2rgb(color = nil) ⇒ Object 



55
56
57
58
59
60
61
62
 
# File 'lib/googlesheets.rb', line 55

def hex2rgb(color=nil)
  color =~ /\A#?(?:(\h\h)(\h\h)(\h\h)|(\h)(\h)(\h))\z/ or return
  r, g, b = $1 ? [$1, $2, $3] : [$4*2, $5*2, $6*2]
  r = "%.2f" % (r.hex / 255.0)
  g = "%.2f" % (g.hex / 255.0)
  b = "%.2f" % (b.hex / 255.0)
  { red: r, green: g, blue: b }
end

#range(area) ⇒ Object 



88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/googlesheets.rb', line 88

def range(area)
  sh, rc = area.split('!', 2); rc, sh = sh, nil if sh.nil?
  as, ae = rc.split(':', 2); ae ||= as
  cs, rs = as.split(/(?=\d)/, 2); cs = biject(cs) - 1; rs = rs.to_i - 1
  ce, re = ae.split(/(?=\d)/, 2); ce = biject(ce) - 1; re = re.to_i - 1
  {
    sheet_id:           sh ? sheet_id(sh) : nil,
    start_column_index: cs,
    start_row_index:    rs,
    end_column_index:   ce + 1,
    end_row_index:      re + 1,
  }.compact
end

#resolve_sheet(area) ⇒ Object 



137
138
139
 
# File 'lib/googlesheets.rb', line 137

def resolve_sheet(area)
  area.sub(/^(#\d+)(?=!)/) {|num| sheet_name(num)}
end

#rgb2hex(color = nil) ⇒ Object 



47
48
49
50
51
52
53
 
# File 'lib/googlesheets.rb', line 47

def rgb2hex(color=nil)
  color or return
  r = ((color.red   || 0) * 255).to_i
  g = ((color.green || 0) * 255).to_i
  b = ((color.blue  || 0) * 255).to_i
  "#%02x%02x%02x" % [r, g, b]
end

#sheet_clear(area) ⇒ Object 



200
201
202
203
204
 
# File 'lib/googlesheets.rb', line 200

def sheet_clear(area)
  area = resolve_sheet(area)
  area.sub!(/^(#\d+)(?=!)/) {|num| sheet_name(num)}
  api.clear_values(@ssid, area)
end

#sheet_color(pick, color = nil) ⇒ Object

NOTE: ignores alpha



157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/googlesheets.rb', line 157

def sheet_color(pick, color=nil) # NOTE: ignores alpha
  reqs = []
  reqs.push(update_sheet_properties: {
    properties: {
      sheet_id: sheet_id(pick),
      tab_color: hex2rgb(color),
    },
    fields: 'tab_color',
  })
  resp = api.batch_update_spreadsheet(@ssid, { requests: reqs }, {})
  true
end

#sheet_filter(area, want = nil) ⇒ Object 



170
171
172
173
174
175
176
177
178
179
 
# File 'lib/googlesheets.rb', line 170

def sheet_filter(area, want=nil)
  area = resolve_sheet(area)
  range = range(area)
  criteria = filter_criteria(want) if want
  reqs = []
  reqs.push(clear_basic_filter: { sheet_id: range[:sheet_id] })
  reqs.push(set_basic_filter: { filter: { range: range, criteria: criteria}.compact })
  resp = api.batch_update_spreadsheet(@ssid, { requests: reqs }, {})
  true
end

#sheet_format(area, pattern) ⇒ Object 



181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/googlesheets.rb', line 181

def sheet_format(area, pattern)
  area = resolve_sheet(area)
  reqs = []
  reqs.push(repeat_cell: {
    range: range(area),
    cell: {
      user_entered_format: {
        number_format: {
          type: "NUMBER",
          pattern: pattern,
        },
      },
    },
    fields: 'user_entered_format.number_format',
  })
  resp = api.batch_update_spreadsheet(@ssid, { requests: reqs }, {})
  true
end

#sheet_id(obj) ⇒ Object 



121
122
123
124
125
126
127
 
# File 'lib/googlesheets.rb', line 121

def sheet_id(obj)
  case obj
    when /^#(\d+)$/ then sheets[$1.to_i - 1].sheet_id
    when Integer    then obj
    else sheets.first_result {|item| item.sheet_id if item.title == obj}
  end
end

#sheet_list ⇒ Object 



111
112
113
114
115
116
117
118
119
 
# File 'lib/googlesheets.rb', line 111

def sheet_list
  sheets.map do |item|
    {
      id:    item.sheet_id,
      name:  item.title,
      color: rgb2hex(item.tab_color),
    }.compact
  end
end

#sheet_name(obj) ⇒ Object 



129
130
131
132
133
134
135
 
# File 'lib/googlesheets.rb', line 129

def sheet_name(obj)
  case obj
    when /^#(\d+)$/ then sheets[$1.to_i - 1].title
    when Integer    then sheets.first_result {|item| item.title if item.sheet_id == obj}
    else obj
  end
end

#sheet_read(area) ⇒ Object 



206
207
208
209
210
 
# File 'lib/googlesheets.rb', line 206

def sheet_read(area)
  area = resolve_sheet(area)
  area.sub!(/^(#\d+)(?=!)/) {|num| sheet_name(num)}
  api.get_spreadsheet_values(@ssid, area).values
end

#sheet_rename(pick, name = nil) ⇒ Object 



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
# File 'lib/googlesheets.rb', line 141

def sheet_rename(pick, name=nil)
  shid = sheet_id(pick)
  name ||= yield(sheet_name(shid)) if block_given?

  reqs = []
  reqs.push(update_sheet_properties: {
    properties: {
      sheet_id: shid,
      title: name,
    },
    fields: 'title',
  })
  resp = api.batch_update_spreadsheet(@ssid, { requests: reqs }, {})
  true
end

#sheet_save(area, rows, log = false) ⇒ Object 



212
213
214
215
216
217
218
 
# File 'lib/googlesheets.rb', line 212

def sheet_save(area, rows, log=false)
  area = resolve_sheet(area)
  gasv = Google::Apis::SheetsV4::ValueRange.new(range: area, values: rows)
  done = api.update_spreadsheet_value(@ssid, area, gasv, value_input_option: "USER_ENTERED")
  puts "#{done.updated_cells} cells updated." if log
  done.updated_cells
end

#sheet_save!(area, rows) ⇒ Object 



220
221
222
 
# File 'lib/googlesheets.rb', line 220

def sheet_save!(area, rows)
  sheet_save(area, rows, true)
end

#sheets ⇒ Object 



102
103
104
 
# File 'lib/googlesheets.rb', line 102

def sheets
  @sheets ||= api.get_spreadsheet(@ssid).sheets.map {|item| item.properties }
end

#sheets! ⇒ Object 



106
107
108
109
 
# File 'lib/googlesheets.rb', line 106

def sheets!
  @sheets = nil
  sheets
end