How to get last rows from google sheets with c#-CodePudding

I want to get last 100 rows from my google table with c# Google.Apis.Sheets.v4. Also i'd like to be able to get all rows with column A value greater than 120. Is there a way to do this without loading all the rows from table into memory to filter them?

CodePudding user response：

I want to get last 100 rows from my google table with c# Google.Apis.Sheets.v4.

There is currently no way to get this information directly using Sheets API.
However, to workaround this you can declare a generic range on your request to Sheets API. For example, to fetch the entire column A starting at the 500th row, you can use A500:A.

Also i'd like to be able to get all rows with column A value greater than 120. Is there a way to do this without loading all the rows from table into memory to filter them?

Same scenario, Sheets API currently doesn’t support a “conditional fetch”. You must fetch a range of cells and treat the cells values in your script.

CodePudding user response：

Disregard I read the question wrong.

To do this, you can use the SpreadsheetsResource.ValuesResource.GetRequest class from the Google.Apis.Sheets.v4 namespace and specify the range of cells that you want to retrieve as well as a filter criteria using the MajorDimension and ValueInputOption parameters.

Here is an example of how to get the last 100 rows from a Google Sheets table and filter the rows by a specific column value using the Google Sheets API for .NET:

using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;

// ...

// Set the ID of the spreadsheet and the range of cells to retrieve
string spreadsheetId = "YOUR_SPREADSHEET_ID";
string range = "A:Z";

// Create a request to get the values from the specified range
SpreadsheetsResource.ValuesResource.GetRequest request =
    service.Spreadsheets.Values.Get(spreadsheetId, range);

// Set the major dimension to "ROWS" to retrieve the last 100 rows
request.MajorDimension = "ROWS";

// Set the value input option to "RAW" to return the unformatted values
request.ValueInputOption = SpreadsheetsResource.ValuesResource.GetRequest.ValueInputOptionEnum.RAW;

// Execute the request and get the response
ValueRange response = request.Execute();

// Get the values from the response as a 2D array
IList<IList<object>> values = response.Values;

// Filter the values to include only rows with column A value greater than 120
values = values.Where(row => Convert.ToDouble(row[0]) > 120).ToList();

// Do something with the filtered values