Class: GoogleSheets

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

Constant Summary collapse

VERSION = 
"0.9.0"

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(ssid, **opts) ⇒ GoogleSheets

Returns a new instance of GoogleSheets.



26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/googlesheets.rb', line 26

def initialize(ssid, **opts)
  @ssid = ssid =~ /^https?:/ ? ssid.split("/")[5] : ssid # spreadsheet id
  @wsid = ssid =~ /(?<=#gid=)(\d+)(?:!([a-z\d:]+))?$/i ? $1.to_i : "#1" # worksheet id
  @rect = $2 ? $2.sub(/(?<=\D):/, "1:") : "A:ZZ"

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

  if !File.exist?(@json)
    base = File.expand_path(File.join(ENV["HOME"], ".google"))
    abort "unable to file Google API credentials" unless File.exist?(base)
    @json = File.join(base, @json)
    @yaml = File.join(base, @yaml)
  end

  if File.exist?(@yaml)
    begin
      yaml = YAML.load_file(@yaml)
      till = JSON[yaml["default"]]["expiration_time_millis"] / 1000
      till > Time.now.to_i or raise
    rescue
      File.delete(@yaml)
    end
  end

  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.



24
25
26
 
# File 'lib/googlesheets.rb', line 24

def api
  @api
end

Instance Method Details

#authorize ⇒ Object 



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

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



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

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 



104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/googlesheets.rb', line 104

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 



87
88
89
90
91
92
93
94
 
# File 'lib/googlesheets.rb', line 87

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 



117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/googlesheets.rb', line 117

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_area(area) ⇒ Object 



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

def resolve_area(area)
  if area.blank?
    wsid = sheet_name
  elsif area =~ /!/
    wsid = sheet_name($`)
    rect = $'
  elsif area =~ /:/ or area =~ /^[a-z]{1,3}\d+$/i
    wsid = sheet_name
    rect = area
  else
    wsid = sheet_name(area)
  end
  rect = rect ? rect.sub(/(?<=\D):/, "1:") : @rect
  "#{wsid}!#{rect}"
end

#rgb2hex(color = nil) ⇒ Object 



96
97
98
99
100
101
102
 
# File 'lib/googlesheets.rb', line 96

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 



167
168
169
170
 
# File 'lib/googlesheets.rb', line 167

def sheet_clear(area)
  area = resolve_area(area)
  api.clear_values(@ssid, area)
end

#sheet_color(curr, color = nil) ⇒ Object

NOTE: ignores alpha



172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/googlesheets.rb', line 172

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

#sheet_filter(area, filt = nil) ⇒ Object 



185
186
187
188
189
190
191
192
193
194
 
# File 'lib/googlesheets.rb', line 185

def sheet_filter(area, filt=nil)
  area = resolve_area(area)
  range = range(area)
  criteria = filter_criteria(filt) if filt
  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 



196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/googlesheets.rb', line 196

def sheet_format(area, pattern)
  area = resolve_area(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 



215
216
217
218
219
220
221
 
# File 'lib/googlesheets.rb', line 215

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

#sheet_list ⇒ Object 



223
224
225
226
227
228
229
230
231
 
# File 'lib/googlesheets.rb', line 223

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 = nil) ⇒ Object 



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

def sheet_name(obj=nil)
  case obj ||= @wsid
    when /^#(\d+)(?:!?|$)/ then sheets[$1.to_i - 1].title
    when "", 0, nil        then sheets.first.title
    when Integer           then sheets.find {|item| item.sheet_id == obj}&.title
    else obj
  end
end

#sheet_read(area = nil) ⇒ Object 



249
250
251
252
 
# File 'lib/googlesheets.rb', line 249

def sheet_read(area=nil)
  area = resolve_area(area)
  api.get_spreadsheet_values(@ssid, area).values
end

#sheet_rename(curr, name = nil) ⇒ Object 



233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/googlesheets.rb', line 233

def sheet_rename(curr, name=nil)
  shid = sheet_id(curr)
  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 



254
255
256
257
258
259
260
 
# File 'lib/googlesheets.rb', line 254

def sheet_save(area, rows, log=false)
  area = resolve_area(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 



262
263
264
 
# File 'lib/googlesheets.rb', line 262

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

#sheets ⇒ Object 



133
134
135
 
# File 'lib/googlesheets.rb', line 133

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

#sheets! ⇒ Object 



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

def sheets!
  @sheets = nil
  sheets
end