GoogleSheetAPIv4 rubysdk access column by number

Question

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!

score 2 · Accepted Answer · edited Oct 24 '20 at 21:39

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.

GoogleSheetAPIv4 rubysdk access column by number

1 Answers1

Pattern 1:

Sample script:

Pattern 2:

Sample script:

References: