Class: GoogleSheets

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

Constant Summary collapse

VERSION = 
"0.8.3"

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



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

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



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

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 



94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/googlesheets.rb', line 94

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 



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

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 



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

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 



132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/googlesheets.rb', line 132

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 



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

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 



157
158
159
160
 
# File 'lib/googlesheets.rb', line 157

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

#sheet_color(curr, color = nil) ⇒ Object

NOTE: ignores alpha



162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/googlesheets.rb', line 162

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 



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

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 



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/googlesheets.rb', line 186

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 



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

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 



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

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 



148
149
150
151
152
153
154
155
 
# File 'lib/googlesheets.rb', line 148

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 



239
240
241
242
 
# File 'lib/googlesheets.rb', line 239

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

#sheet_rename(curr, name = nil) ⇒ Object 



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/googlesheets.rb', line 223

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 



244
245
246
247
248
249
250
 
# File 'lib/googlesheets.rb', line 244

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 



252
253
254
 
# File 'lib/googlesheets.rb', line 252

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

#sheets ⇒ Object 



123
124
125
 
# File 'lib/googlesheets.rb', line 123

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

#sheets! ⇒ Object 



127
128
129
130
 
# File 'lib/googlesheets.rb', line 127

def sheets!
  @sheets = nil
  sheets
end