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

Question

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?

Tanaike · Accepted Answer · 2019-11-24 23:02:15Z

4

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.

answered Nov 24, 2019 at 23:02

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

zeppelin11 Over a year ago

Thanks for the reply @Tanaike, do you know how I can preserve the cell format in the sheet I copy to? I only want to change the cell value not the existing format.

Tanaike Over a year ago

@zeppelin11 Thank you for replying. At first, can I ask you whether my answer could resolve your issue? And about your new question, although I'm not sure about your situation from your replying, for example, when the date format of yyyymmdd is used for the column "A" and you want to follow the format, please modify fields from "fields": "userEnteredValue,userEnteredFormat" to "fields": "userEnteredValue". By this, userEnteredFormat is ignored. So in that case, userEnteredFormat": {} is not required. Is my understanding of your new question correct?

ChasedByDeath Over a year ago

@Tanaike If I had to do the same as in this question but with different values then which of the batch_update request body would you suggest me to use? I know I can use this repeatCellRequest by mentioning different ranges.

Tanaike Over a year ago

@Sadman Sakib Thank you for replying. If you want to update the cells in a column by the different values, how about using UpdateCellsRequest? Ref But I cannot still clearly see the vision of your goal. So if I misunderstood your goal, I apologize. At that time, can you post it as new question by including the detail information?

ChasedByDeath Over a year ago

@Tanaike UpdateCellsRequests did the work. Thank you!

|

Collectives™ on Stack Overflow

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

1 Answer 1

Sample script:

References:

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

References:

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related