CODEWITHSUNDEEP
c#google-sheets-api
Asmaa Rezkallah
Asmaa Rezkallah

Reputation: 13

Google Sheet api v4 dynamic sheet range

I am trying to access a sheet to get data and copy it in a data table I'm using the google sheets api v4 but I found that I must add the range to the request params.

Although I can't know how many columns has data in that sheet but the user is the one who set the spreadsheet url, so it varies and I can't find the specific range to add to the request.

So how can I find the range with contains all the data of that spreadsheet (knowing that all the spreadsheets the use can choose has only one sheet).

Upvotes: 1

Views: 1824

Answers (2)

Shoniver Games
Shoniver Games

Reputation: 21

It seems like—even though the API does NOT mention this anywhere at all—if you write the sheet name as the "range" param, it will simply give you the full range of said sheet, like so:

SpreadsheetsResource.ValuesResource.GetRequest request = 
sheetsService.Spreadsheets.Values.Get([SPREADSHEET FILE ID], [SHEET NAME]);

In addition to that, if—like me—you didn't have the name of the sheet and just wanted to use, say, the first sheet of the spreadsheet file, you can do something like this to get the name of the sheet:

SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get([SPREADSHEET FILE ID]);
Spreadsheet response = request.Execute();
string firstSheetName = response.Sheets[0].Properties.Title;

Upvotes: 2

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

Unfortunately, there is no way of retrieving the data range of a sheet using the Google Sheets API.

Workarounds:

Google Apps Script has a .getDataRange() method of the Sheet class, which returns in A1 notation the range in which data is present in a Sheet. There is a workaround here which incorporates this method, though I am unsure if it is suitable for your use case.

Feature Request:

You can however let Google know that this is a feature that is important for the Sheets API and that you would like to request they implement it. Google's Issue Tracker is a place for developers to report issues and make feature requests for their development services. The page to file a Feature Request for the Google Sheets API is here.

References:

Upvotes: 1

Related Questions