CODEWITHSUNDEEP
rubygoogle-sheets-api
Cyril Duchon-Doris
Cyril Duchon-Doris

Reputation: 13999

Google Spreadsheet API returning grid limits error

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

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

Upvotes: 2

Views: 6980

Answers (4)

Chandrashekar CN
Chandrashekar CN

Reputation: 21

worksheet.resize(2000)

will resize your sheet 2000 rows

Upvotes: 2

Andrea
Andrea

Reputation: 11

It happened to me as well when I didn't have the empty columns (I removed all the empty columns from the spreadsheet). I simply added an empty one next to my last column and it works.

Upvotes: 1

邹洪学
邹洪学

Reputation: 46

this because out of range.

AA1 means column is AA, also means 27, so this start point AA1 not exist, that's why you met this error.

you can try Z1, this should be ok.

Upvotes: 3

Tanaike
Tanaike

Reputation: 201513

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:

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

Upvotes: 9

Related Questions