CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-apigspread
zeppelin11
zeppelin11

Reputation: 113

Quickest way to write a column with google sheet API and gspread

I have a column (sheet1!A:A) with 6000 rows, I would like to write today's date (todays_date) to each cell in the column. Currently doing it by using .values_update() method in a while loop but it takes too much time and giving APIError due to quota limit.

x=0
while x <= len(column):
    sh.values_update(
        'Sheet1!A'+str(x),
        params={
            'valueInputOption': 'USER_ENTERED'
        } ,
        body={
            'values': todays_date]
        }
    )
    x+=1

Is there any other way that I can change the cell values altogether?

Upvotes: 1

Views: 1563

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to put a value to all cells in the column "A" in "Sheet1".
  • You want to achieve this using gspread with python.
  • You want to reduce the process cost for this situation.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In this answer, I used the method of batch_update of gspread and RepeatCellRequest of the method of batchUpdate in Sheets API. In this case, above situation can be achieved by one API call.

Sample script:

Before you run the script, please set the variables of spreadsheetId and sheetName.

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

sh = client.open_by_key(spreadsheetId)
sheetId = sh.worksheet(sheetName)._properties['sheetId']
todays_date = (datetime.datetime.now() - datetime.datetime(1899, 12, 30)).days
requests = [
    {
        "repeatCell": {
            "range": {
                "sheetId": sheetId,
                "startRowIndex": 0,
                "startColumnIndex": 0,
                "endColumnIndex": 1
            },
            "cell": {
                "userEnteredValue": {
                    "numberValue": todays_date
                },
                "userEnteredFormat": {
                    "numberFormat": {
                        "type": "DATE",
                        "pattern": "dd/mm/yyyy"
                    }
                }
            },
            "fields": "userEnteredValue,userEnteredFormat"
        }
    }
]
res = sh.batch_update({'requests': requests})
print(res)
  • When you run above script, the today's date is put to all cells of the column "A" as the format of dd/mm/yyyy.
    • If you want to change the value and format, please modify above script.
  • At (datetime.datetime.now() - datetime.datetime(1899, 12, 30)).days, the date is converted to the serial number. By this, the value can be used as the date object in Google Spreadsheet.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 4

Related Questions