CODEWITHSUNDEEP
pythongoogle-sheets-apigspread
LucSpan
LucSpan

Reputation: 1971

How to get first 300 rows of Google sheet via gspread

Set-up

I create a Pandas dataframe from all records in a google sheet like this,

df = pd.DataFrame(wsheet.get_all_records())

as explained in the Gspread docs.

Issue

It seems Python stays in limbo when I execute the command since today. I don't get any error; I interrupt Python with KeyboardInterrupt after a while.

I suspect Google finds the records too much; ±3500 rows with 18 columns.

Question

Now, I actually don't really need the entire sheet. The first 300 rows would do just fine.

The docs show values_list = worksheet.row_values(1), which would return the first row values in a list.

I guess I could create a loop, but I was wondering if there's a build-in / better solution?

Upvotes: 1

Views: 3933

Answers (2)

Tanaike
Tanaike

Reputation: 201553

I believe your goal as follows.

  • You want to retrieve the values from 1st row to 300 row from a sheet in Google Spreadsheet.
    • From I suspect Google finds the records too much; ±3500 rows with 18 columns., you want to retrieve the values from the columns "A" to "R"?
  • You want to convert the retrieved values to the dataFrame.
  • You want to achieve this using gspread.

In order to achieve this, I would like to propose the following sample script.

In this answer, I used the method of values_get.

Sample script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.
rangeA1notation = "Sheet1!A1:R300"  # Please set the range using A1Notation.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
values = spreadsheet.values_get(rangeA1notation)
v = values['values']
df = pd.DataFrame(v)
print(df)

Note:

  • Please set the range as the A1Notation. In this case, when "A1:R300" instead of "Sheet1!A1:R300" is used, the values are retrieved from the 1st tab in the Spreadsheet.

  • When "A1:300" is used, the values are retrieved from the column "A" to the last column of the sheet.

  • When the 1st row is the header row and the data is after the 2nd row, please modify as follows.

    • From

        df = pd.DataFrame(v)

    • To

        df = pd.DataFrame(v[1:], columns=v[0])

Reference:

Upvotes: 1

Umar Awan
Umar Awan

Reputation: 86

I used openpyxl package.

import openpyxl as xl

wb = xl.load_workbook('your_file_name')>
sheet = wb['name_of_your_sheet']

Specify your range.
for row in range(1, 300):

Now you can perform many opertions e.g this will point at row(1) & col(3) in first iteration
cell = sheet.cell(row, 3)

if you want to change the cell value
cell.value = 'something'

It's has pretty much all of it. Here is a link to the docs: https://openpyxl.readthedocs.io/en/stable/

Upvotes: 1

Related Questions