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.")

Answer 1

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:

• col_values
• update