Class: GoogleDocs::Sheet

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

Constant Summary collapse

LETTER_LIST = 
('A'..'ZZZ').to_a.freeze

Instance Method Summary collapse

Constructor Details

#initialize(spreadsheet_id:, sheet_id:, google_access_token: nil, service: nil) ⇒ Sheet

Note:

if you have service you don’t need google_access_token

Set class instance variables param google_access_token [String] google access token

Parameters:

  • spreadsheet_id (String) —

    id of spreadsheet

  • sheet_id (String) —

    id of sheet

  • google_access_token (String) (defaults to: nil)
  • service (Google::Apis::SheetsV4::SheetsService) (defaults to: nil)

Raises:

  • (ArgumentError) 


14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/google_docs/sheet.rb', line 14

def initialize(spreadsheet_id:, sheet_id:, google_access_token: nil, service: nil)
  if service.nil?
    @service = SERVICE::SheetsService.new
    @service.authorization = google_access_token
  else
    @service = service
  end

  @spreadsheet = @service.get_spreadsheet(spreadsheet_id)
  @sheet       = @spreadsheet.sheets.find { |sheet| sheet.properties.sheet_id == sheet_id.to_i }
  @requests    = []

  raise ArgumentError, 'Invalid sheet id' if @sheet.nil?
end

Instance Method Details

#apply_changes! ⇒ Object 



155
156
157
158
159
160
 
# File 'lib/google_docs/sheet.rb', line 155

def apply_changes!
  @service.batch_update_spreadsheet(
    @spreadsheet.spreadsheet_id, SERVICE::BatchUpdateSpreadsheetRequest.new(requests: @requests)
  )
  @requests = []
end

#download_pdf {|file| ... } ⇒ Object

create pdf file of sheet’s content

Yields:

  • (file) —

    Gives file to the block



40
41
42
43
44
45
46
 
# File 'lib/google_docs/sheet.rb', line 40

def download_pdf
  file = Tempfile.new(['sheet', '.pdf'])
  file.binmode
  file.write(pdf_request)
  yield(file)
  file.close
end

#get_rows_values ⇒ Object

return all information from each row in sheet



30
31
32
33
34
35
36
 
# File 'lib/google_docs/sheet.rb', line 30

def get_rows_values
  last_column_letter = LETTER_LIST[@sheet.properties.grid_properties.column_count - 1]
  last_row_number    = @sheet.properties.grid_properties.row_count
  @service.get_spreadsheet_values(
    @spreadsheet.spreadsheet_id, "#{@sheet.properties.title}!A1:#{last_column_letter}#{last_row_number}"
  ).values
end

#merge_cells(props) ⇒ Object

Examples:

of props value

{
  type: :merge_all, :merge_columns, :merge_rows
  range: {
    start_row_index:    integer
    end_row_index:      integer
    start_column_index: integer
    end_column_index:   integer
  }
}

Parameters:

  • props (Hash) 


130
131
132
133
134
135
136
137
 
# File 'lib/google_docs/sheet.rb', line 130

def merge_cells(props)
  append_request(
    merge_cells: SERVICE::MergeCellsRequest.new(
      merge_type: props[:type],
      range:      grid_range(props[:range])
    )
  )
end

#pdf_request ⇒ Object 



48
49
50
 
# File 'lib/google_docs/sheet.rb', line 48

def pdf_request
  @service.http(:get, "https://docs.google.com/spreadsheets/d/#{@spreadsheet.spreadsheet_id}/export?exportFormat=pdf&gid=#{@sheet.properties.sheet_id}")
end

#setup_columns_width(data) ⇒ Object

Note:

‘integer value’ is positive pixel size for Google::Apis::SheetsV4::DimensionProperties

{

{
  'A' => 'integer value',
  'D' => 'integer value',
  ...
}

}

Parameters:

  • data (Hash) —

    keys are the columns names and values indicate desirable columns width in sheet



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/google_docs/sheet.rb', line 61

def setup_columns_width(data)
  data.each do |key, value|
    append_request(
      update_dimension_properties: SERVICE::UpdateDimensionPropertiesRequest.new(
        range: SERVICE::DimensionRange.new(
          sheet_id:    @sheet.properties.sheet_id,
          dimension:   'COLUMNS',
          start_index: LETTER_LIST.index(key),
          end_index:   LETTER_LIST.index(key) + 1
        ),
        properties: SERVICE::DimensionProperties.new(pixel_size: value),
        fields:     'pixelSize'
      )
    )
  end
  true
end

#unmerge_cells(range) ⇒ Object

Examples:

of props value

{
  start_row_index:    integer
  end_row_index:      integer
  start_column_index: integer
  end_column_index:   integer
}

Parameters:

  • range (Hash) 


147
148
149
150
151
152
153
 
# File 'lib/google_docs/sheet.rb', line 147

def unmerge_cells(range)
  append_request(
    unmerge_cells: SERVICE::UnmergeCellsRequest.new(
      range: grid_range(range)
    )
  )
end

#update_cells(data) ⇒ Object

Examples:

of data value

[
  [ 'text', 'text', ...],                # first row
  [],                                    # second row (empty)
  [ 'text', { value: 'text', ...props }] # third row
  ...
]

Parameters:

  • data (Array<Array>) 


109
110
111
112
113
114
115
116
117
 
# File 'lib/google_docs/sheet.rb', line 109

def update_cells(data)
  append_request(
    update_cells: SERVICE::UpdateCellsRequest.new(
      rows:   format_rows_from(data),
      fields: '*',
      range:  grid_range
    )
  )
end

#update_grid_properties(props) ⇒ Object

Examples:

of props value

https://developers.google.com/sheets/reference/rest/v4/spreadsheets#GridProperties
{
  row_count:           integer
  column_count:        integer
  frozen_row_count:    integer
  frozen_column_count: integer
  hide_gridlines:      bool
}

Parameters:

  • props (Hash) 


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

def update_grid_properties(props)
  append_request(
    update_sheet_properties: SERVICE::UpdateSheetPropertiesRequest.new(
      fields:     props.keys.map { |field| "gridProperties.#{StringUtils.camelize(field.to_s)}" }.join(','),
      properties: SERVICE::SheetProperties.new(
        grid_properties: SERVICE::GridProperties.new(props),
        sheet_id:        @sheet.properties.sheet_id
      )
    )
  )
end