1

I am trying to access the values in a Google Spreadsheet that is localized to Russian. In Russian, Sheet is Лист and so the default sheet names are Лист1 and Лист2, etc.

The method I am using to retrieve values from a specific sheet is to use the spreadsheet values API and A1 notation to limit results to the specific sheet:

/v4/spreadsheets/{spreadsheet-it}/values/Лист1!1:1

However, the Google API returns a 400 bad request. If I manually rename the sheet to use only latin characters (like Sheet1) it works just fine.

But this is too much to ask our users using languages with different alphabets - I'm hoping there is a way I am not seeing in the documentation to access values for a specific sheet using the numeric Sheet ID rather than the sheet title?

Thanks!

Nathan
  • 12,290
  • 3
  • 29
  • 28

1 Answers1

1

Answer for question 1:

In my environment, I can confirm that https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]/values/Лист1!1:1 works. And when I saw the response value, "range": "'Лист1'!A1:Z1" can be seen. So for example, how about enclosing Лист1 by the single quotes like https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]/values/'Лист1'!1:1?

When the curl command is used, it is as follows.

curl \
  "https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]/values/%27%D0%9B%D0%B8%D1%81%D1%821%27%211%3A1" \
  -H "Authorization: Bearer ###"

  • %27%D0%9B%D0%B8%D1%81%D1%821%27%211%3A1 is 'Лист1'!1:1. In this case, the range value is encoded by the URL encode.

    • Also I thought that in your case, the URL encode like above might be required.

  • By the way, in my environment, I can confirm that the following curl command also works.

      curl \
    'https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]/values/Лист1!1:1' \
    -H 'Authorization: Bearer ###'

Answer for question 2:

If you want to retrieve the cell values using the sheet ID, how about the following method? In this case, https://docs.google.com/spreadsheets/d/[SPREADSHEETID]/gviz/tq?gid=[SHEETID]&range=1:1&tqx=out:csv is used as the endpoint.

curl \
  'https://docs.google.com/spreadsheets/d/[SPREADSHEETID]/gviz/tq?gid=[SHEETID]&range=1:1&tqx=out:csv' \
  -H "Authorization: Bearer ###"
  • When above curl command is run, the value is returned as the CSV format.
  • By using the access token, the values can be retrieved without publishing.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165