Search code examples
pythongoogle-sheets-apigspread

How to predefined number of rows and cols within gspread

I do have a scraped data which i overwriting google sheet daily with it.

The point here that I'm unable to find an option where i can set number of rows and cols for the existing google sheet.

I noticed that can be done only for new created sheet according to documentation but i don't know how to do it for existing sheet!

def api(key):
    myfilt = [list of lists]
    columns = [name of columns]
    gc = gspread.service_account(filename='Auth.json')
    sh = gc.open_by_key(key)
    worksheet = sh.sheet1
    worksheet.clear()
    head = worksheet.insert_row(columns, 1)
    res = worksheet.insert_rows(myfilt, 2)


api("MyAPIHere")

My target here is to predefined number of rows according to len(myfilt) and number of cols according to len(cols)

Solution

  • I believe your goal as follows.

    • You want to change the max row and column number of the existing sheet in the Google Spreadsheet.
    • You want to achieve this using gspread with python.
    • You have already been able to get and put values for Google Spreadsheet using Sheets API.

    Points for achieving your goal:

    In this case, it is required to use the method of "spreadsheets.batchUpdate" in Sheets API. And I would like to propose the following flow.

    1. Insert one row.
    2. Insert one column.
    3. Delete rows from 2 to end.
    4. Delete columns from 2 to end.
    5. Insert rows. In this case, you can set the number of rows you want to insert.
    6. Insert columns. In this case, you can set the number of columns you want to insert.

    1 and 2 are used for avoiding the error. Because when the DeleteDimensionRequest is run for the sheet which has only one row or one column, an error occurs.

    When above flow is reflected to the script using gspread, it becomes as follows.

    Sample script:

    Please set the Spreadsheet ID and sheet name.

    spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "###"  # Please set the sheet name.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
# worksheet = spreadsheet.worksheet(sheetName)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']
rows = len(myfilt)
columns = len(cols)

req = {
    "requests": [
        {
            "insertDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 0,
                    "endIndex": 1,
                    "dimension": "ROWS"
                }
            }
        },
        {
            "insertDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 0,
                    "endIndex": 1,
                    "dimension": "COLUMNS"
                }
            }
        },
        {
            "deleteDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 1,
                    "dimension": "ROWS"
                }
            }
        },
        {
            "deleteDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 1,
                    "dimension": "COLUMNS"
                }
            }
        },
        {
            "insertDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 0,
                    "endIndex": rows - 1,
                    "dimension": "ROWS"
                }
            }
        },
        {
            "insertDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 0,
                    "endIndex": columns - 1,
                    "dimension": "COLUMNS"
                }
            }
        }
    ]
}
res = spreadsheet.batch_update(req)
print(res)

    References: