Unable to apply formula in a column of google sheet using python-CodePudding

import pandas as pd
import pygsheets
import gspread
from gspread_dataframe import set_with_dataframe
from google.oauth2.service_account import Credentials

def csv_to_sheets():
  tokenPath ='path for service account file.json'
  scopes = ['https://www.googleapis.com/auth/spreadsheets',
         'https://www.googleapis.com/auth/drive']

  credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
  gc = gspread.authorize(credentials)
  gs = gc.open('csv_to_gSheet')
  workSheet1 = gs.worksheet('sheet1')
  my_csv = pd.read_csv("my csv file path")
  my_csv_values = my_csv.values.tolist()


  row = str(len(workSheet1.get_all_values())   1)
  workSheet1.batch_update([
  {"range": "A"   row,"values": [[r[0]] for r in my_csv_values]},
  {"range": "S"   row,"values": [r[1:] for r in my_csv_values]},
  ],value_input_option="USER_ENTERED")

  workSheet1.batch_update("AF","=SUM(S:AE)")
  csv_to_sheets()

Here, I'm trying to add the data from my csv to google sheet from columns 'S' to 'AE' which is happening correctly. Then I'm trying to apply the formula in column 'AF' but it is throwing an error : gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid value at 'data[0].values' (type.googleapis.com/google.protobuf.ListValue), "=SUM(S:AE)"'

And even when I give only 2 arguments in the update method, it still says 3 given.

This is the google sheet in which I want to use this formula but from the 17th row as the first 16 rows are pre-filled.

Please help. Thank you in advance.

CodePudding user response：

I believe your goal is as follows.

You want to put a formula of =SUM(S:AE) to a cell "AF17" using gspread for python.

When I saw your script, I thought that workSheet1.batch_update("AF","=SUM(S:AE)") is required to be modified. So, how about the following modification?

From:

workSheet1.batch_update("AF","=SUM(S:AE)")

To:

workSheet1.batch_update([{"range": "AF17", "values": [["=SUM(S:AE)"]]}], value_input_option="USER_ENTERED")

From your showing sample image, I thought that you might want to put the formula to the cell "AF17". If you want to put it in another row, please modify it.
In this case, I thought that the following modification might be able to be used.
```
  workSheet1.update("AF17", "=SUM(S:AE)", value_input_option="USER_ENTERED")
```

Note:

Although I'm not sure whether I could correctly understand your question, for example, when you want to put the formula to each row of my_csv_values, how about the following modification?

From

  workSheet1.batch_update([
  {"range": "A"   row,"values": [[r[0]] for r in my_csv_values]},
  {"range": "S"   row,"values": [r[1:] for r in my_csv_values]},
  ],value_input_option="USER_ENTERED")

  workSheet1.batch_update(
      [
          {"range": "A"   row, "values": [[r[0]] for r in my_csv_values]},
          {"range": "S"   row, "values": [r[1:] for r in my_csv_values]},
          {"range": "AF"   row, "values": [["=SUM(S"   str(i   int(row))   ":AE"   str(i   int(row))   ")"] for i, _ in enumerate(my_csv_values)]},
      ],
      value_input_option="USER_ENTERED",
  )

From:

To:

Note:

References: