CODEWITHSUNDEEP
pythonexcelgoogle-sheetsgspread
StarrySky123
StarrySky123

Reputation: 3

gspread.exceptions.APIError exceeds grid limits - Adding new rows and dataframe into gsheet using Python

I have a google sheet that needs to be updated weekly from an excel sheet using Python. The problem is, somewhat I'm able to run this code on the first try or a few random tries then I'll start receiving this error:

Error:

gspread.exceptions.APIError: {'code': 400, 'message': "Range ('test sheet'!A2155:M2987) exceeds grid limits. Max rows: 2154, max columns: 13", 'status': 'INVALID_ARGUMENT'}

Additional info:

  1. This google sheet is also updated by other users so I don't want to overwrite any data - I just want to insert my dataframe right after the final non-blank row.
  2. Usually there are no rows right after the final non-blank row.image here
  3. file in the code refers to the excel file: it's a variable where I'll be changing file_1 to file_2, file_3, file_4... etc every week
  4. Upon receiving the error, I thought the issue is because there aren't any rows after the non-blank row (as show in the image file) - thus I've tried append_rows, insert_rows, add_rows to the sheet but none of them worked.

However, if I tried adding a row beneath the non-blank row manually into the sheet, it works.

  1. Running it with same/different excel files still doesn't solve the problem.
  2. I've already tried searching for a similar issue but I couldn't find one.

I'm frustrated and confused why would it work and not work at the same time.

  1. Is there a way I can do this while preventing this error?
  2. And why is this happening?

Code:

    import pandas as pd
    from oauth2client.service_account import ServiceAccountCredentials
    import os.path
    from pathlib import Path
    import xlsxwriter
    import gspread
    from gspread_dataframe import set_with_dataframe
    import pygsheets
    
    file = Path('C:\\Users\\Username\\Desktop\\Folder 1\\Weekly Process\\file_1')
    open(file)
    
    df_total = pd.DataFrame()
    sheets = pd.ExcelFile(file).sheet_names
    
    for sheet in sheets:
        df = pd.read_excel(file,sheet_name=sheet, usecols = ['User ID','History','City','Score','Score_category'])
        df_total = df_total.append(df)
    
    
    df = df_total.reindex(columns = ['Column A','User ID','History','Column D','Column E',\
        'Column F','Column G','Column H','City','Column J','Column K','Score','Score_category']) 
    
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('My Project.json', scope)
    client = gspread.authorize(creds)
    sheet = client.open('ABC sheet')
    sheet_instance = sheet.get_worksheet(0)
    
    df = df.assign(Column A='Column A', Column D='Column D',\
        Column E='Column E', Column F='Column F', Column G='Column G'\
        Column H='Column H', Column J='Column J', Column K='Column K')
    
    cells = sheet_instance.get_all_values()
    end_row = len(cells)
    print('To insert data AFTER this row:',end_row)
    
    set_with_dataframe(sheet_instance, df, row=end_row + 1,  include_column_header=False)
    
    print('Done')

Upvotes: 0

Views: 2022

Answers (1)

SushantPatade
SushantPatade

Reputation: 364

After adding row, re-initialise the sheet object, like this:

sheet.add_rows(push_table.shape[0])
appended_sheet = spreadsheet.worksheet(wks_name)

And use the new object in set_with_dataframe. For example:

gd.set_with_dataframe(worksheet=appended_sheet, dataframe=push_table, include_index=False, include_column_header=False, row= endrow+1, resize=False)

Upvotes: 1

Related Questions