Skip to content

Get data from spreadsheet

Jump to bottom
Danylo Lytvynenko edited this page Sep 24, 2020 · 7 revisions

Warning: If the cells in the google table are not filled with data, as in the example below, then when accessing the cell by address it will not match the address in the google table, since transferring information, the site api ignores empty cells (but the lines remain unchanged, even if all its cells are empty)

image

Get spreadsheet

First you need to get the desired saved spreadsheet

var spreadsheet = GoogleDocConnector.GetSpreadsheet("{ID}");

Also, if you need from this class, you can get the absolute web url for the spreadsheet. string url = GoogleDocConnector.GetSpreadsheetWebUrl("{ID}");

Get sheet

There are 2 ways to get the sheet you need, here they are:

  1. Use methods
+ Gets spreadsheet sheet by it's id.
var sheet = spreadsheet.GetSheet(500028786);
+ Gets first spreadsheet sheet by it's name.
sheet = spreadsheet.GetSheet("Sheet1");

Also you can check if this sheet is there

+ Returns `true` if spreadsheet contains a sheet with specified id, otherwise `false`.
bool checkSheetId = spreadsheet.GetSheet(500028786);
+ Returns `true` if spreadsheet contains a sheet with the name, otherwise `false`.
bool checkSheetName = spreadsheet.GetSheet("Sheet1");
  1. The second way is to get a list of sheets IEnumerable<Sheet> sheets = spreadsheet.Sheets and work with it yourself var sh = sheets.First(); or smth else

Get cell

When you find the sheet you want, then you can get any cell that is filled and store there

  1. Use methods
+ Get sell by name.
var cell = sheet.GetCell("B5");
+ Gets cell from specified row & col. Indexs starts from 0
var cell1 = sheet.GetSheet(0, 2); // C1
  1. The second way is to get a list of Rows IEnumerable<RowData> rows= sheet.Sheets.Rows and take the desired cell from the desired row

Get cell Value

To get the value of a cell, we need to get a cell and take the Value property from it or use the methods below

+ Gets converted cell value  by name.
double cellValue = sheet.GetCellValue<double>("B5");
+ Gets converted cell value from specified row & col. Index starts from 0
DateTimecellValue = sheet.GetCellValue<DateTime>(0, 2); // C1

Rows and Columns

If you need to get cells or values from the required columns or rows, then use the following methods

Row

Returns all the cells in the row. Row index. Index starts from 0

List<Cell> lc = sheet.GetRow(0);

Returns all the cells converted values in the row. See CellValue.GetValue for more info.

List<int> lc = sheet.GetRowValues<int>(3);

Column

Returns all the cells in the row. Column index. Index starts from 0

List<Cell> lc = sheet.GetColumn(0);

Returns all the cells in the column by name of the column

List<Cell> lc = sheet.GetColumn("C");

Returns all the cells converted values in the row. See CellValue.GetValue for more info.

List<string> lc = sheet.GetColumnValues<string>(3);
List<string> lc = sheet.GetColumnValues<string>("C");

Ranges and NamedRanges

When you want to get a range of cells by address, or a range of cells by name of a range, you can use the examples below

GridRange range = new GridRange(0, 0, 2, 3);
List<Cell> cells = sheet.GetRange(range);
+ Exaples: "A1:B2", "1:2", "A:B"
List<Cell> cells = sheet.GetRange("A3:C4");
+ Returns a list of Cell objects of the requested Named Range.
List<Cell> cells = sheet.GetNamedRangeCells("Bike3");
+ Returns all the cells converted values of the requested Named Range.
List<string> cellValues = sheet.GetNamedRangeValues<string>("Bike3");
Clone this wiki locally