2

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)

DisappointedByUnaccountableMod
  • 6,656
  • 4
  • 18
  • 22
αԋɱҽԃ αмєяιcαη
  • 11,825
  • 3
  • 17
  • 50

2 Answers2

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you. I've achieved it by another way of clearing the sheet then resize according to length of rows and cols. but within your answer, i don't know how to send `myfilt` which include my data – αԋɱҽԃ αмєяιcαη Sep 01 '20 at 22:38
  • 1
    @αԋɱҽԃ αмєяιcαη Thank you for replying. When you want to put the values, I think that there are 2 methods. 1. Include the UpdateCellsRequest to the above request body. [Ref](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest) The UpdateCellsRequest can put the values to the cells. In this case, one API call is used. 2. After above script is run, the values are put with gspread. In this case, 2 API calls are used. – Tanaike Sep 01 '20 at 22:42
  • you welcome. in case if you've time . kindly check [that issue](https://stackoverflow.com/questions/63696461/error-occurred-while-trying-to-write-emails-to-google-sheet) – αԋɱҽԃ αмєяιcαη Sep 01 '20 at 22:52
  • @αԋɱҽԃ αмєяιcαη Thank you for replying. I checked it and posted a modification point. Could you please confirm it? – Tanaike Sep 01 '20 at 23:04
2

I used the following to solve my issue as well:

worksheet.clear() # to clear the sheet firstly.
head = worksheet.insert_row(header, 1) # inserting the header at first row
res = worksheet.insert_rows(mydata, 2) # inserting my data.
worksheet.resize(rows=len(mydata) + 1, cols=len(header)) # resize according to length of cols and rows.
αԋɱҽԃ αмєяιcαη
  • 11,825
  • 3
  • 17
  • 50