如何在Google表格API中使用Python进行批量更新,将一列值输入到特定列中。

huangapple go评论67阅读模式
英文:

How to input a list of values to a specific column using batch update in googles spreadsheet api, in python

问题

"updateCells": {
    "rows": [
        {
            "values": [
                {
                    "userEnteredValue": {
                        "numberValue": 5
                    }
                },
                {
                    "userEnteredValue": {
                        "numberValue": 5
                    }
                },
                {
                    "userEnteredValue": {
                        "numberValue": 5
                    }
                }
            ]
        }
    ],
    "range": {
        "startColumnIndex": 10,
        "endColumnIndex": 11,
        "startRowIndex": 0
    },
    "fields": "userEnteredValue"
}
英文:

I would like to input data into a column with googlesheets api in python. Currently using both append and updatecells it only allows you to input the data into a specific row. In the example below that would input the value 5 into 3 different columns of the same row. (similar output with append. I would like all 3 values to be input into the same column in consecutive rows.

"updateCells": {
            "rows": [
                {
                "values": [
                    {
                        "userEnteredValue": {
                            "numberValue": 5
                        }
                    },
                    {
                        "userEnteredValue": {
                            "numberValue": 5
                        }
                    },
                    {
                        "userEnteredValue": {
                            "numberValue": 5
                        }
                    }
                ]
                }
            ],
            "range": {
                "startColumnIndex": 10,
                "endColumnIndex": 11,
                "startRowIndex": 0
            },
            "fields": "userEnteredValue"
        }

答案1

得分: 1

I believe your goal is as follows.

  • You want to put a list to a column of a Spreadsheet using Sheets API.

In your request body, 3 values of 5 are put into a row of 3 columns. In this case, how about the following modification?

Sample script 1:

In this sample, "Method: spreadsheets.batchUpdate" is used.

service = ###  # 请使用您的客户端。
spreadsheet_id = "###"  # 请放入您的电子表格ID。
sheet_id = "0"  # 请放入您想要使用的工作表的工作表ID。
sample_value = [1, 2, 3] # 这是一个示例值。

requests = {
    "updateCells": {
        "rows": [
            {"values": {"userEnteredValue": {"numberValue": e}}}
            for e in sample_value
        ],
        "range": {"startColumnIndex": 10, "startRowIndex": 0},
        "fields": "userEnteredValue",
    }
}
service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id, body={"requests": requests}
).execute()
  • When this script is run, the values of [1, 2, 3] are put into the cells "K1:K3" of "Sheet1".

Sample script 2:

In this sample, "Method: spreadsheets.values.append" is used.

service = ###  # 请使用您的客户端。
spreadsheet_id = "###"  # 请放入您的电子表格ID。
sample_value = [1, 2, 3] # 这是一个示例值。

service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    body={"values": [sample_value], "majorDimension": "COLUMNS"},
    valueInputOption="USER_ENTERED",
    range="Sheet1!K1",
).execute()
  • When this script is run, the values of [1, 2, 3] are appended to the column "K".

References:

英文:

I believe your goal is as follows.

  • You want to put a list to a column of a Spreadsheet using Sheets API.

In your request body, 3 values of 5 are put into a row of 3 columns. In this case, how about the following modification?

Sample script 1:

In this sample, "Method: spreadsheets.batchUpdate" is used.

service = ###  # Please use your client.
spreadsheet_id = "###"  # Please put your Spreadsheet ID.
sheet_id = "0"  # Please put the sheet ID of the sheet you want to use.
sample_value = [1, 2, 3] # This is a sample value.

requests = {
    "updateCells": {
        "rows": [
            {"values": {"userEnteredValue": {"numberValue": e}}}
            for e in sample_value
        ],
        "range": {"startColumnIndex": 10, "startRowIndex": 0},
        "fields": "userEnteredValue",
    }
}
service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id, body={"requests": requests}
).execute()
  • When this script is run, the values of [1, 2, 3] is put to the cells "K1:K3" of "Sheet1".

Sample script 2:

In this sample, "Method: spreadsheets.values.append" is used.

service = ###  # Please use your client.
spreadsheet_id = "###"  # Please put your Spreadsheet ID.
sample_value = [1, 2, 3] # This is a sample value.

service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    body={"values": [sample_value], "majorDimension": "COLUMNS"},
    valueInputOption="USER_ENTERED",
    range="Sheet1!K1",
).execute()
  • When this script is run, the values of [1, 2, 3] is appended to the columne "K".

References:

huangapple
  • 本文由 发表于 2023年3月7日 05:11:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75655886.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定