CODEWITHSUNDEEP
rubygoogle-sheets-api
gnk
gnk

Reputation: 159

GoogleSheetAPIv4 rubysdk access column by number

I can update the cell 'C11' like below,

@service.update_spreadsheet_value(SPREADSHEET_ID, 'C11', vr, value_input_option: 'USER_ENTERED')

Is there a way like below? I want to remove the alphabet from argument.

@service.update_spreadsheet_value(SPREADSHEET_ID, '[2][10]', vr, value_input_option: 'USER_ENTERED')

Thank you!

Upvotes: 0

Views: 107

Answers (1)

Tanaike
Tanaike

Reputation: 201513

  • You want to put a value to a cell without using the a1Notation.
  • You want to achieve this using googleapis with ruby.
  • You have already been able to get and put values for Google Spreadsheet with Sheets API.

update_spreadsheet_value() uses the method of spreadsheets.values.update in Sheets API. In this case, the a1Notation is required to be used as the range. So I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, the range of a1Notation is created from the column index and row index. The value is put with update_spreadsheet_value.

Sample script:

# Following function is from https://stackoverflow.com/a/31152792
def letter_sequence(n)
  n.to_s(26).each_char.map {|i| ('A'..'Z').to_a[i.to_i(26)]}.join
end

column_index = 2
row_index = 10
sheet_name = 'Sheet1'

range = sheet_name + '!' + letter_sequence(column_index) + (row_index + 1).to_s
@service.update_spreadsheet_value(SPREADSHEET_ID, range, vr, value_input_option: 'USER_ENTERED')
  • In this case, range is Sheet1!C11. And vr is put from the cell "C11".

Pattern 2:

In this pattern, the GridRange is used as the range. For this, the method of batchUpdate is used.

Sample script:

column_index = 2
row_index = 10
sheet_id = "###"  # Please set the sheet ID.

requests = {requests: [{update_cells: {
    rows: [{values: [{user_entered_value: {string_value: 'sample'}}]}],
    start: {sheet_id: sheet_id, row_index: row_index, column_index: column_index},
    fields: 'userEnteredValue'}
}]}
@service.batch_update_spreadsheet(SPREADSHEET_ID, requests, {})
  • In this case, the text of sample is put to the cell "C11".
  • Of course, you can put the several values. In that case, please check the official document.

References:

Upvotes: 2

Related Questions