Is it possible to fill down the entire column by Excel formula as in GUI? #gspread

user_12323524 0
Viewed 334

Is it possible in gspread library to fill down the entire column by Excel formula as in GUI, without going through each line?

Currently, I'm using the method below:

def _update_formula(sheet):
     for i in range(2, 100):
         sheet.update_acell(f'AL{i}', f'=if(B{i}=B{i+1},1,0)')

Is it possible, for example, to add 1 line and copy formula the rest of rows as in the GUI. Do you have any ideas how to optimize it?

gspread google-sheets-api google-sheets excel python
1 Answers

I believe your goal is as follows.

  • You want to put a formula of =if(B{i}=B{i+1},1,0) to all cells in the specific column.
  • You want to achieve this using gspread for python.

In your script, you use the formula of '=if(B{i}=B{i+1},1,0'. But in this case, it might be '=if(B{i}=B{i+1},1,0)'. In this case, how about the following modified script? In this modification, the formula is put using the RepeatCellRequest of batchUpdate method.

Modified script:

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

spreadsheet = client.open_by_key(spreadsheetId) # or client.open("###Spreadsheet title###")
sheet = spreadsheet.worksheet(sheetName)
requests = {
    "repeatCell": {
        "cell": {
            "userEnteredValue": {
                "formulaValue": "=if(B1=B2,1,0)"
            }
        },
        "range": {
            "sheetId": sheet.id,
            "startColumnIndex": 2,
            "endColumnIndex": 3,
            "startRowIndex": 1
        },
        "fields": "userEnteredValue.formulaValue"
    }
}
res = spreadsheet.batch_update({"requests": [requests]})
  • "startColumnIndex": 2 and "endColumnIndex": 3 of range means the column "C".
  • "startRowIndex": 1 of range and no endRowIndex means that the formula is put from the row 2 to end of row.
  • About GridRange, you can see it at the official document.

References:

user_71086530
Related