Google表格API:从单元格复制公式 [英] Google sheets API: copy formula from cell

问题描述

由于所有类似问题都没有真正的答案，因此我将尝试一下.

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

我正在使用预先存在的电子表格上的Google Sheets API.

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

我想将A1:=A7/A2中的公式复制到B1:=B7/B2

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()

有没有办法复制公式?

推荐答案

• 您要将单元格"A1"的公式=A7/A2复制为单元格"B1"作为=B7/B2.
• 您想使用带有Google的google-api-python-client的Sheets API来实现此目的.
  • 从您的脚本中，我认为您可以将google-api-python-client与Python一起使用，尽管找不到Python的标记.
  • 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.

    如果我的理解是正确的，那么该修改如何?在此修改中，我使用了Sheets API的电子表格.batchUpdate方法的"CopyPasteRequest".

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

    在使用此脚本之前，请设置spreadsheet_id和sheetId的变量.

    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)
    

    • 请将该范围设置为gridrange.
      startRowIndex，endRowIndex，startColumnIndex和endColumnIndex的
      • 0, 1, 0, 1表示单元格"A1".
      startRowIndex，endRowIndex，startColumnIndex和endColumnIndex的
      • 0, 1, 1, 2表示单元格"B1".
      • 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".
        • 如果在"A1"单元格具有=A7/A2的情况下运行上述脚本，则会将=B7/B2放入单元格"B1".
        • When above script is run under that the cell of "A1" has =A7/A2, =B7/B2 is put to the cell "B1".
        • 在上面的脚本中，授权过程被省略.请注意这一点.因此，请使用您的脚本.
        • 方法:sheetssheets.batchUpdate
        • CopyPasteRequest
        • GridRange
        • 电子表格ID
        • Sheet ID
        • Method: spreadsheets.batchUpdate
        • CopyPasteRequest
        • GridRange
        • Spreadsheet ID
        • Sheet ID

        如果我误解了您的问题，而这不是您想要的结果，我深表歉意.

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

        这篇关于Google表格API:从单元格复制公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！