0

I am posting the following (incomplete code) to Google sheets batch update values:

{"valueInputOption":"USER_ENTERED","data":[{"range":"'Home Sheet'!B2:B2","values":[

And for some reason I am getting:

{
  "error": {
    "code": 400,
    "message": "Invalid data[0]: Unable to parse range: 'Home Sheet'!B2:B2",
    "status": "INVALID_ARGUMENT"
  }
}

However if you refer to https://developers.google.com/sheets/api/guides/concepts this clearly looks like the range is valid. Interestingly this is only in the Javascript not if I use the PHP sdk.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Antony
  • 3,875
  • 30
  • 32
  • What if you edit that to `'Home Sheet'!B2`? – Diego Jul 18 '18 at 10:34
  • Wierdly I had tried that prior to this but unfortunately still no good – Antony Jul 18 '18 at 10:35
  • `INVALID_ARGUMENT` means the sheet or range cannot be found. Test with another sheet: "Sheet1!A1" or another file. – Max Makhrov Jul 18 '18 at 10:49
  • The sheet definitely exists ... – Antony Jul 18 '18 at 10:57
  • And what do you get from the API when you query `spreadsheets#get` for the sheet names? e.g. https://developers.google.com/apis-explorer/#p/sheets/v4/sheets.spreadsheets.get?spreadsheetId=yourSpreadsheetId&fields=sheets%252Fproperties%252Ftitle – tehhowch Jul 18 '18 at 13:18

1 Answers1

3

When accessing a public sheet via URL like this:

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetID}/values/{sheetName}?key={API Key}

I have to replace the space in the sheet name with %20, or remove the space from the sheet name if possible. Like this:

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetID}/values/Home%20Sheet!B2:B2?key={API Key}

So this works for me so long as the sheet sharing is set to "Anyone with the link can view."

See this answer for more detailed info.

Alex B
  • 35
  • 1
  • 6
  • 1
    Most client libraries (Google's, at least) will automatically URL-encode querystring / URL parameters, such as spaces to `%20`. – tehhowch Jul 19 '18 at 23:03