Module: Lexdrill::SheetsClient

Defined in:
lib/lexdrill/sheets_client.rb

Overview

Thin wrapper around the Google Sheets API v4 REST endpoints needed by drill push/drill pull: create the target tab if it doesn't already exist, clear its contents, write new rows into it, then auto-fit column A's width to the content so long phrases aren't visually truncated; or, for pull, read column A back out of an existing tab. Also covers workbook/tab management for drill wb/drill sh: the workbook's title, the list of tab titles, and creating/deleting a tab.

Defined Under Namespace

Classes: ApiError

Constant Summary collapse

BASE_URL = 
"https://sheets.googleapis.com/v4/spreadsheets"

Class Method Summary collapse

Class Method Details

.add_sheet(spreadsheet_id, sheet_name, access_token) ⇒ Object

Creates a new, empty tab, for drill sh add. Returns its sheetId.



55
56
57
58
59
60
 
# File 'lib/lexdrill/sheets_client.rb', line 55

def self.add_sheet(spreadsheet_id, sheet_name, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}:batchUpdate"
  body = { "requests" => [{ "addSheet" => { "properties" => { "title" => sheet_name } } }] }
  result = handle_response(Lexdrill::HTTPClient.json_post(url, body: body, headers: auth_header(access_token)))
  result.dig("replies", 0, "addSheet", "properties", "sheetId")
end

.clear(spreadsheet_id, sheet_name, access_token) ⇒ Object 



77
78
79
80
 
# File 'lib/lexdrill/sheets_client.rb', line 77

def self.clear(spreadsheet_id, sheet_name, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}/values/#{encoded_range(sheet_name)}:clear"
  handle_response(Lexdrill::HTTPClient.json_post(url, body: {}, headers: auth_header(access_token)))
end

.delete_sheet(spreadsheet_id, sheet_id, access_token) ⇒ Object

Deletes a tab by its numeric sheetId, for drill sh remove.



63
64
65
66
67
 
# File 'lib/lexdrill/sheets_client.rb', line 63

def self.delete_sheet(spreadsheet_id, sheet_id, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}:batchUpdate"
  body = { "requests" => [{ "deleteSheet" => { "sheetId" => sheet_id } }] }
  handle_response(Lexdrill::HTTPClient.json_post(url, body: body, headers: auth_header(access_token)))
end

.find_sheet_id(spreadsheet_id, sheet_name, access_token) ⇒ Object

The sheetId for a tab by title, or nil if no tab has that title — for drill sh use/drill sh remove, which need the numeric id (not just the name) to build a direct link or issue a delete request.



50
51
52
 
# File 'lib/lexdrill/sheets_client.rb', line 50

def self.find_sheet_id(spreadsheet_id, sheet_name, access_token)
  find_sheet(spreadsheet_id, sheet_name, access_token)&.[]("sheetId")
end

.overwrite_sheet(spreadsheet_id, sheet_name, rows, access_token) ⇒ Object 



25
26
27
28
29
30
 
# File 'lib/lexdrill/sheets_client.rb', line 25

def self.overwrite_sheet(spreadsheet_id, sheet_name, rows, access_token)
  sheet_id = ensure_sheet_exists(spreadsheet_id, sheet_name, access_token)
  clear(spreadsheet_id, sheet_name, access_token)
  update(spreadsheet_id, sheet_name, rows, access_token)
  autofit_first_column(spreadsheet_id, sheet_id, access_token)
end

.read_column(spreadsheet_id, sheet_name, access_token) ⇒ Object

Reads column A back out of an existing tab (ignoring any other columns, e.g. show counts from an older export), skipping blank cells.



90
91
92
93
94
 
# File 'lib/lexdrill/sheets_client.rb', line 90

def self.read_column(spreadsheet_id, sheet_name, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}/values/#{encoded_range(sheet_name)}"
  data = handle_response(Lexdrill::HTTPClient.json_get(url, headers: auth_header(access_token)))
  data.fetch("values", []).filter_map { |row| row[0]&.strip }.reject(&:empty?)
end

.sheet_titles(spreadsheet_id, access_token) ⇒ Object

The titles of every tab in the workbook, for drill sh index.



97
98
99
100
101
 
# File 'lib/lexdrill/sheets_client.rb', line 97

def self.sheet_titles(spreadsheet_id, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}?fields=sheets.properties.title"
  data = handle_response(Lexdrill::HTTPClient.json_get(url, headers: auth_header(access_token)))
  data.fetch("sheets", []).map { |sheet| sheet.dig("properties", "title") }
end

.spreadsheet_title(spreadsheet_id, access_token) ⇒ Object

The workbook's own title, for naming it automatically in drill wb add.



104
105
106
107
108
 
# File 'lib/lexdrill/sheets_client.rb', line 104

def self.spreadsheet_title(spreadsheet_id, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}?fields=properties.title"
  data = handle_response(Lexdrill::HTTPClient.json_get(url, headers: auth_header(access_token)))
  data.dig("properties", "title")
end

.update(spreadsheet_id, sheet_name, rows, access_token) ⇒ Object 



82
83
84
85
86
 
# File 'lib/lexdrill/sheets_client.rb', line 82

def self.update(spreadsheet_id, sheet_name, rows, access_token)
  url = "#{BASE_URL}/#{spreadsheet_id}/values/#{encoded_range(sheet_name)}?valueInputOption=RAW"
  body = { "range" => quoted_range(sheet_name), "majorDimension" => "ROWS", "values" => rows }
  handle_response(Lexdrill::HTTPClient.json_put(url, body: body, headers: auth_header(access_token)))
end