How to add next range from the previous range in google sheets

Question

I have a sheet which has multiple columns with formulas. I want to add a formula of next range in the next row of that column using script instead of doing it manually. In this code I'm trying to achieve that but every time it is going in "else" even though the previous cell has a formula. Please let me know where am I going wrong. Thank you.

    tokenPath='path_to_token_file'
    scopes = ['https://www.googleapis.com/auth/spreadsheets',
              'https://www.googleapis.com/auth/drive']

    credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
    client = gspread.authorize(credentials)

    sheet_title = 'my_sheet_title'
    sheet = client.open(sheet_title)
    spreadsheet_id = 'my_spreadsheet_id'

    worksheet = sheet.get_worksheet(1)  # 2nd sheet so index=1

    # Find the last cell with a formula in column C
    col_p_values = worksheet.col_values(3)  # Column C is index 3
    last_formula_cell = None

    for row_number, value in enumerate(col_p_values, start=1):
        if value.startswith('=SUM('):
            last_formula_cell = worksheet.cell(row_number, 3)  # Column C is index 3
            break

    if last_formula_cell:
        previous_formula = last_formula_cell.formula
        start_row = int(previous_formula.split(':')[1][1:])
        new_start_row = start_row + 1
        new_end_row = new_start_row + 6

        new_formula = f"=SUM(my_sheet_name!P{new_start_row}:P{new_end_row})"

        # Update the cell below the last formula cell with the new formula
        new_formula_range = f"C{last_formula_cell.row + 1}"  # updating column C
        worksheet.update(new_formula_range, new_formula)
        print("Formula updated successfully.")
    else:
        print("No formula found in the specified column.")

`col_p_values = worksheet.col_values(3)` would only populate the cell value, not cell formula — Century Tuna, Aug 24 '23 at 18:05

score 1 · Accepted Answer · answered Aug 25 '23 at 01:12

Modification points:

In order to retrieve the formulas with col_values, it is required to use value_render_option="FORMULA".
In your loop of for row_number, value in enumerate(col_p_values, start=1):, the 1st founded cell is retrieved. I thought that you might have wanted to retrieve the last founded cell of =SUM(.
In the case of start_row = int(previous_formula.split(':')[1][1:]), when the formula is =SUM(A1:A2), 2) is retrieved.
In the case of worksheet.update(new_formula_range, new_formula), the value is put as the cell value instead of the formula. In this case, it is required to use value_input_option='USER_ENTERED'.

When these points at reflected in your script, how about the following modification?

Modified script:

tokenPath='path_to_token_file'
scopes = ['https://www.googleapis.com/auth/spreadsheets',
          'https://www.googleapis.com/auth/drive']

credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
client = gspread.authorize(credentials)

sheet_title = 'my_sheet_title'
sheet = client.open(sheet_title)
spreadsheet_id = 'my_spreadsheet_id'

worksheet = sheet.get_worksheet(1)  # 2nd sheet so index=1

# I modified the below script.
col_p_values = worksheet.col_values(3, value_render_option="FORMULA")

last_formula_cell = None

for row_number, value in reversed(list(enumerate(col_p_values, start=1))):
    if value.startswith('=SUM('):
        last_formula_cell = worksheet.cell(row_number, 3, value_render_option='FORMULA')
        break

if last_formula_cell:
    previous_formula = last_formula_cell.value
    start_row = int(previous_formula.replace(')', '').split(':')[1][1:])
    new_start_row = start_row + 1
    new_end_row = new_start_row + 6
    new_formula = f"=SUM(my_sheet_name!P{new_start_row}:P{new_end_row})"
    new_formula_range = f"C{last_formula_cell.row + 1}"
    worksheet.update(new_formula_range, new_formula, value_input_option='USER_ENTERED',)
    print("Formula updated successfully.")
else:
    print("No formula found in the specified column.")

When I tested this modified script, I confirmed that a new formula is put into the next row of the last row of column "C" by retrieving the formula of the last row of column "C".

Note:

In your situation, when the formula like =SUM( has already existed in the cells, your goal might be able to be achieved by copying the cell. In that case, how about the following modified script?

tokenPath='path_to_token_file'
scopes = ['https://www.googleapis.com/auth/spreadsheets',
          'https://www.googleapis.com/auth/drive']

credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
client = gspread.authorize(credentials)

sheet_title = 'my_sheet_title'
sheet = client.open(sheet_title)
spreadsheet_id = 'my_spreadsheet_id'

worksheet = sheet.get_worksheet(1)  # 2nd sheet so index=1

# I modified the below script.
col_p_values = worksheet.col_values(3, value_render_option="FORMULA")

last_formula_cell = None

for row_number, value in reversed(list(enumerate(col_p_values, start=1))):
    if value.startswith('=SUM('):
        last_formula_cell = row_number
        break

if last_formula_cell:
    requests = [
        {
            "copyPaste": {
                "source": {"sheetId": worksheet.id, "startRowIndex": last_formula_cell - 1, "endRowIndex": last_formula_cell, "startColumnIndex": 2, "endColumnIndex": 3},
                "destination": {"sheetId": worksheet.id, "startRowIndex": last_formula_cell, "endRowIndex": last_formula_cell + 1, "startColumnIndex": 2, "endColumnIndex": 3},
                "pasteType": "PASTE_FORMULA"
            }
        }
    ]
    sheet.batch_update({"requests": requests})
    print("Formula updated successfully.")
else:
    print("No formula found in the specified column.")

References:

Hi, thank you for the solution. It is working fine but if I want to update the sheet with its name and not by index then how should I do that? — Kartikeya Kawadkar, Aug 28 '23 at 05:22
@Kartikeya Kawadkar Thank you for replying. From `It is working fine`, I understood that your issue was resolved. I'm glad for it. About `if I want to update the sheet with its name and not by index then how should I do that?`, although I'm not sure whether I could correctly understand this, if you want to use `worksheet` with the sheet name, please modify `worksheet = sheet.get_worksheet(1)` to `worksheet = sheet.worksheet("### your sheet name ###")`. If I misunderstood your additional request, I apologize. At that time, can you provide more information? By this, I would like to confirm it. — Tanaike, Aug 28 '23 at 06:04
In the code as you can see I'm using this line 'worksheet = sheet.get_worksheet(1)' so, instead of index "1" can we specify the name of that worksheet to update because we can make a mistake while giving index. — Kartikeya Kawadkar, Aug 28 '23 at 06:05
@Kartikeya Kawadkar Thank you for replying. Sorry. I just commented about it. Please confirm it again. In that case, please use `worksheet = sheet.worksheet("### your sheet name ###")` instead of `worksheet = sheet.get_worksheet(1)`. — Tanaike, Aug 28 '23 at 06:06

George · Answer 2 · 2023-08-25T05:01:07.473

Alternate Solution:

Note: This code is based on your existing code and explanation,it would be best to share a reproducible example for an intended solution that caters your exact data

The issue with your existing script is on the line col\_p\_values = worksheet.col\_values(3).

The current line only returns the value of the specified column if you want to extract all the formulas in the column you need to add the parameter value_render_option.

This explains why the code goes directly to else statement since it does not enter your for-loop and no value was added to last\_formula\_cell

Modified code:

The modified code works as follows:

Retrieve the list of formulas present in column C using value\_render\_option="FORMULA"
Obtain the last formula in the list
Create a new start row and new end row from the last formula (based on your existing script)

Add a new formula to the cell below the last row with formula in Column C using the update method with value\_entered\_option="USER\_ENTERED parameter

gc = gspread.authorize(creds)

sheet_title = 'formula python'
sheet = gc.open(sheet_title)
spreadsheet_id = 'sheet_id'

worksheet = sheet.get_worksheet(0)  # 1st sheet so index=0
col_p_formula= worksheet.col_values(3,value_render_option='FORMULA')
last_formula_cell = col_p_formula[-1]

if last_formula_cell:
  start_row = ''.join(char for char in last_formula_cell if char.isdigit()) # extract all the digits from the last formula
  new_start_row = int(start_row[-1]) + 1
  new_end_row = int(start_row[-1]) + 6
  new_formula = f"=SUM(E{new_start_row}:E{new_end_row})" # you can edit here (new formula gets data from my sample sheet in column E)
  new_formula_range = f"C{len(col_p_formula)+1}" # length + 1 of column C with formulas for the new column range
  worksheet.update(new_formula_range, new_formula, value_input_option='USER_ENTERED')
  print("Formula updated successfully.")
else:
  print("No formula found in the specified column.")

Output:

References:

https://docs.gspread.org/en/v4.0.1/user-guide.html

How to add next range from the previous range in google sheets

2 Answers2

Modification points:

Modified script:

Note:

References:

Alternate Solution:

Modified code:

Output:

References: