Google Spreadsheet API returning grid limits error

Question

I am trying to update a Google Sheet using the Ruby API (that is just a wrapper around the SheetsV4 API)

I am running into the following error

Google::Apis::ClientError: badRequest: Range ('MySheet'!AA1) exceeds grid limits. Max rows: 1000, max columns: 26

I have found references of this problem on the google forum, however there did not seem to be a solution to the problem other that to use a different method to write to the spreadsheet.

The thing is, I need to copy an existing spreadsheet template, and enter my raw data in various sheets. So far I have been using this code (where service is a client of the Ruby SheetsV4 API)

def write_table(values, sheet: 'Sheet1', column: 1, row: 1, range: nil, value_input_option: 'RAW')
  google_range = begin
    if range
      "#{sheet}!#{range}"
    elsif column && row
      "#{sheet}!#{integer_to_A1_notation(column)}#{row}"
    end
  end
  value_range_object = ::Google::Apis::SheetsV4::ValueRange.new(
    range: google_range, values: values
  )
  service.update_spreadsheet_value(spreadsheet_id,
    google_range,
    value_range_object,
    value_input_option: value_input_option
  )
end

It was working quite well so far, but after adding more data to my extracts, I went over the 26th column, (columns AA onwards) and now I am getting the error.

Is there some option to pass to update_spreadsheet_value so we can raise this limit ?

Otherwise, what is the other way to write to the spreadsheet using append ?

EDIT - A clear description of my scenario

I have a template Google spreadsheet with 8 sheets(tabs), 4 of which are titled RAW-XX and this is where I try to update my data.

At the beginning, those raw tabs only have headers on 30 columns (A1 --> AD1) My code needs to be able to fill all the cells A2 --> AD42

(1) for the first time
(2) and my code needs to be able to re-run again to replace those values by fresh ones, without appending

So basically I was thinking of using update_spreadsheet_value rather than append_xx because of the requirement (2). But becuase of this bug/limitation (unclear) in the API, this does not work. ALso important to note : I am not actually updating all those 30 columns in one go, but actually in several calls to the update method (with up to 10 columns each time)

I've thought that - Maybe I am missing an option to send to the Google API to allow more than 26 columns in one go ? - Maybe this is actually an undocumented hard limitation of the update API - Maybe I can resort to deleting existing data + using append

EDIT 2

Suppose I have a template at version 1 with multiple sheets (Note that I am using =xx to indicate a formula, and [empty] to indicate there is nothing in the cell, and 1 to indicate the raw value "1" was supplied

Sheet1 - RAW
RAW Number of foos | RAW Number of Bars |
[empty]            | [empty]            |

Sheet2 - FORMATTED
Number of foos       | Number of Bars
='Sheet1 - RAW'!A2   | ='Sheet1 - RAW'B2

Now I call my app "for the first time", this copies the existing template to a new file "generated_spreadsheet" and injects data in the RAW sheet. It turns out at this moment, my app says there is 1 foo and 0 bar

Sheet1 - RAW
RAW Number of foos | RAW Number of Bars |
1                  | 0                  |

Sheet2 - FORMATTED
Number of foos       | Number of Bars
='Sheet1 - RAW'!A2   | ='Sheet1 - RAW'!B2

Maybe if I call my app later, maybe the template AND the data have changed in between, so I want to REPLACE everything in my "generated_spreadsheet"

The new template has become in between

Sheet1 - RAW
RAW Number of foos | RAW Number of Bars |
[empty]            | [empty]            |

Sheet2 - FORMATTED
Number of foos       | Number of Bars     | All items
='Sheet1 - RAW'!A2   | ='Sheet1 - RAW'!B2 | =A2 + B2

Suppose now my app says there is still 1 foo and the number of bars went from 0 to 2, I want to update the "generated_spreadsheet" so it looks like

Sheet1 - RAW
RAW Number of foos | RAW Number of Bars |
1                  | 3                  |

Sheet2 - FORMATTED
Number of foos       | Number of Bars     | All items
='Sheet1 - RAW'!A2   | ='Sheet1 - RAW'!B2 | =A2 + B2

Tanaike Tanaike · Accepted Answer · 2018-12-04T23:35:02

How about using values.append? In my environment, I also experienced the same situation with you. In order to avoid this issue, I used values.append.

Please modify as follows and try it again.

From:

service.update_spreadsheet_value(

To:

service.append_spreadsheet_value(

Reference:

Method: spreadsheets.values.append

If this was not the result you want, I'm sorry.

Google Spreadsheet API returning grid limits error

4 Answers

From:

To:

Reference: