Google sheets API: copy formula from cell

Question

As none of the similar questions have real answers, I'll give it a try.

I'm using the Google Sheets API on a pre-existing spreadsheet.

I want to copy the formula in A1: =A7/A2 to B1: =B7/B2

I can copy contents fine, but not formulas with this:

body = {'values': [[result.get("values")[0]]]}
new_range = 'B1'
up_result = sheet.values().update(
spreadsheetId=SPREADSHEET_ID, range=new_range, 
    valueInputOption='USER_ENTERED', body=body).execute()

Is there a way to copy the formula?

A developer created their own CopyFormulasDown function to mimic the same operation in Excel, if you choose to use their solution. — PeterT

Tanaike Tanaike · Accepted Answer · 2019-08-08T22:51:03

You want to copy the formula =A7/A2 of cell "A1" to the cell "B1" as =B7/B2.
You want to achieve this using Sheets API with google-api-python-client of Python.
- From your script, I thought that you might use google-api-python-client with Python, although the tag of Python was not found.
You have already been able to put and get values for Spreadsheet using Sheets API.

If my understanding is correct, how about this modification? In this modification, I used "CopyPasteRequest" of the method of spreadsheets.batchUpdate of Sheets API.

Modified script:

Before you use this script, please set the variables of spreadsheet_id and sheetId.

service = build('sheets', 'v4', credentials=creds)
spreadsheet_id = '###'  # Please set Spreadsheet ID.
sheetId = "###"  # Please set sheet ID.

batch_update_spreadsheet_request_body = {
    "requests": [
        {
            "copyPaste": {
                "source": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": 1
                },
                "destination": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 1,
                    "endColumnIndex": 2
                },
                "pasteType": "PASTE_FORMULA"
            }
        }
    ]
}
request = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=batch_update_spreadsheet_request_body)
response = request.execute()
print(response)

Please set the range as the gridrange.
- 0, 1, 0, 1 for startRowIndex, endRowIndex, startColumnIndex and endColumnIndex mean the cell "A1".
- 0, 1, 1, 2 for startRowIndex, endRowIndex, startColumnIndex and endColumnIndex mean the cell "B1".

Result:

When above script is run under that the cell of "A1" has =A7/A2, =B7/B2 is put to the cell "B1".

Note:

In above script, the authorization process is omitted. Please be careful this. So about this, please use your script.

References:

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