How to copy a cell formula into 1 row above, but reference the new row's data (and not the previous rows data)

Question

When I use gspread to copy a cell formula from (target_cell) to (target_cell+1 row) the formula is correctly passed, however it is still referencing data from (target_cell)'s row. I need it to reference the new row's data. Similar to when you use the "drag" or copy+shift+select all appropriate cells+paste method in excel/google sheets.

I currently can use gspread to get a formula from a particular cell using this: ([source] How to copy a formula from one gsheet to another using python?)

formula = sheet.acell("C3", value_render_option='FORMULA').value
    print(formula)

I want to copy this formula (=B3+A3) in the row above but reference A2 and B2 isntead. The only way I can see so far to update a cell is like this, however can I manipulate the formula in between?

sheet.update_acell("C2", formula)

Here is the entire code using the first response I got:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

#this portion is to ensure I can reach the target spreadsheet and manipulate the spreadsheet. It does work.
sheet = client.open("stocks").worksheet("sandbox")
input=sheet.find(r"start")
tgtrow=input.row
tgtcol=(input.col)
sheet.insert_row(["",""],tgtrow+1)
sheet.update_cell(tgtrow,tgtcol+4, "hi")

#running recomended code
spreadsheetId = "stocks"
sheetName = "sandbox"

client = gspread.authorize(creds)
ss = client.open_by_key(spreadsheetId)
sheetId = ss.worksheet(sheetName)._properties['sheetId']
body = {
    "requests": [
        {
            "copyPaste": {
                "source": {
                    "sheetId": sheetId,
                    "startRowIndex": 2,
                    "endRowIndex": 3,
                    "startColumnIndex": 2,
                    "endColumnIndex": 3
                },
                "destination": {
                    "sheetId": sheetId,
                    "startRowIndex": 1,
                    "endRowIndex": 2,
                    "startColumnIndex": 2,
                    "endColumnIndex": 3
                },
                "pasteType": "PASTE_FORMULA"
            }
        }
    ]
}
res = ss.batch_update(body)

Answer 1

• You want to copy a formula to a cell using gspread.
• For example, when there is a formula of =B3+A3 in the cell "C3", you want to put the formula =B2+A2 to the cell "C2".
• You have already been used Sheets API with gspread.

If my understanding is correct, how about this modification? I think that there are several solutions for your situation. So please think of this as just one of them.

Modification point:

• In this modification, the copyPaste request is used by the method of batchUpdate in Sheets API.

Modified script:

When you use this, please set the variables of spreadsheetId and sheetName. In this sample script, for the sheet name of "Sheet1", the formula of the cell "C3" is copied to the cell "C2". At that time, =B3+A3 is automatically modified to =B2+A2.

spreadsheetId = "###"
sheetName = "Sheet1"

client = gspread.authorize(credentials)
ss = client.open_by_key(spreadsheetId)
sheetId = ss.worksheet(sheetName)._properties['sheetId']
body = {
    "requests": [
        {
            "copyPaste": {
                "source": {
                    "sheetId": sheetId,
                    "startRowIndex": 2,
                    "endRowIndex": 3,
                    "startColumnIndex": 2,
                    "endColumnIndex": 3
                },
                "destination": {
                    "sheetId": sheetId,
                    "startRowIndex": 1,
                    "endRowIndex": 2,
                    "startColumnIndex": 2,
                    "endColumnIndex": 3
                },
                "pasteType": "PASTE_FORMULA"
            }
        }
    ]
}
res = ss.batch_update(body)

References:

• batch_update(body)
• Method: spreadsheets.batchUpdate
• CopyPasteRequest

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

Issue:

From your additional script, I could understand about the issue. The issue was that you are using the filename of Spreadsheet (in your case, it's stocks.) as the Spreadsheet ID. If you want to use the filename of Spreadsheet, please modify as follows.

From:

spreadsheetId = "stocks"

To:

spreadsheetId = client.open("stocks").id