2

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?

Bartłomiej Kanabus
  • 21
  • 2

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165