Google spreadsheet API, 400 error bad request : unable to parse range

Question

I am trying to access Google spreadsheets using a spreadsheet example. When I run the example code it worked fine. I just change the SpreadsheetId and range. It started giving me:

Exception in thread "main" com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Unable to parse range: Class Data!A2:A4",
    "reason" : "badRequest"
  } ],
  "message" : "Unable to parse range: Class Data!A2:A4",
  "status" : "INVALID_ARGUMENT"
}
    at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:321)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1065)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469)
    at poc.mainPOC.main(mainPOC.java:157)

Below is the code:

  String spreadsheetId = "my spread sheet ID";
    String range = "Class Data!A2:A4";
    ValueRange response = service.spreadsheets().values()
        .get(spreadsheetId, range)
        .execute();

score 156 · Accepted Answer · answered Jun 18 '16 at 05:28

156

Try replacing Class Data!A2:A4 with A2:A4

answered Jun 18 '16 at 05:28

ritesh.garg

3,725
1
15
14

1

i want to access a particular spreadsheet how can i do it ? ValueRange response = service.spreadsheets().values() .get(spreadsheetId, range) .execute(); Currently i'm using this to get data – Hemant Yadav Jun 21 '16 at 04:21
26

In the example it seems like 'Class Data' was the name of the worksheet. If there are no sheets with that name, then it throws the error. @HemantYadav when you say spreadsheet, do you mean worksheet? If it's spreadsheet then you provide the spreadsheetId. If it's worksheet, you provide it in the range like "NameOfWorksheet!A2:A4" where NameOfWorksheet is the name of the worksheet including spaces. By omitting the NameOfWorksheet and exclamation mark, it goes to the first worksheet. – zeta Aug 05 '16 at 20:43
Can you please answer for my question --> http://stackoverflow.com/questions/39096279/inserting-datas-into-google-sheets-api-using-javascript @ritesh.garg – Saravana Kumar Aug 23 '16 at 10:28
Thanks a ton bro. And the explanation by @zeta is puts a lot unknown things into perspective. – Mohit Arvind khakharia Oct 16 '16 at 19:13

RamelHenderson · Answer 2 · 2016-09-08T14:14:36.767

25

If you look at the sheet itself you will notice that the Worksheet is titled "Class Data". So just put the name of your sheet where is says "Class Data". Example: String range = "SheetName!A1:C";

edited Sep 08 '16 at 14:14

answered Sep 08 '16 at 13:52

RamelHenderson

2,151
2
13
12

score 14 · Answer 3 · answered Mar 21 '19 at 08:46

14

String range = "Class Data!A2:A4";

The Class Data is your worksheet's name, FYI: the name on the tab at the bottom, the default one is "Sheet1". Replace Class Data with the one you want to work with.

answered Mar 21 '19 at 08:46

Tan Phan

141
1
2

slifszyc · Answer 4 · 2017-09-25T19:45:30.717

4

I was trying to add some data to a sheet named Emmett that did not existed yet and was receiving this error:

Error: Unable to parse range: Emmet!A2:C12

I had to manually create the sheet named Emmett in the spreadsheet and then it worked like a charm.

edited Sep 25 '17 at 19:45

answered Sep 25 '17 at 19:23

slifszyc

146
10

I had the problem because I was opening the wrong document (dumb bug confusing the input and output spreadsheet IDs). It is unfortunate that the error message is not 'can't find sheet "Emmet"' instead of complaining about parsing the range expression, because the syntax is perfectly fine. – Mark Lakata Oct 03 '22 at 16:38

score 4 · Answer 5 · answered Apr 20 '18 at 01:40

4

I ran into this error when I had a typo in the name of the tab. In your case "Class Data" didn't match the name of the tab

answered Apr 20 '18 at 01:40

CodingYourLife

7,172
5
55
69

score 1 · Answer 6 · answered Jun 08 '20 at 10:30

1

In my case there was an extra space in the google sheet while I was trimming the sheet name at my end. Once I removed the trimming logic, everything worked fine.

answered Jun 08 '20 at 10:30

jarora

5,384
2
34
46

score 0 · Answer 7 · answered Jun 12 '20 at 15:34

0

Something that I learned was that If the column name doesn't exist yet, this command won't be able to find it. You need to make sure the column you're writing to already exists -- I did this manually from the google sheet.

answered Jun 12 '20 at 15:34

maya

1

Google spreadsheet API, 400 error bad request : unable to parse range

7 Answers7

Linked

Related