更新整个工作表的单行格式 [英] Update single row formatting for entire sheet

问题描述

我只想应用JSON条目中的格式.我要做的第一件事是在电子表格中为所有列的第二行设置所需的格式.然后，我用.get请求(从A2到AO3)检索了它们.

I want to just apply a formatting from a JSON Entry. The first thing I did was make my desirable format on my spreadsheet for the second row of all columns. I then retrieved them with a .get request (from A2 to AO3).

request = google_api.service.spreadsheets().get(
    spreadsheetId=ss_id,
    ranges="Tab1!A2:AO3",
    includeGridData=True).execute()

我接下来要做的是收集每一列的每种格式，并将它们记录在字典中.

The next thing I did was collect each of the formats for each column and record them in a dictionary.

my_dictionary_of_formats = {}
row_values = row_1['sheets'][0]['data'][0]['rowData'][0]['values']
for column in range(0, len(row_values)):
    my_dictionary_of_formats[column] = row_values[column]['effectiveFormat']

现在我对我所有的列都有所有有效格式的字典.我现在在将该格式应用于每列的所有行时遇到了麻烦.我尝试了batchUpdate请求:

Now I have a dictionray of all my effective formats for all my columns. I'm having trouble now applying that format to all rows in each column. I tried a batchUpdate request:

cell_data = {
  "effectiveFormat": my_dictionary_of_formats[0]}

row_data = {
  "values": [
      cell_data
  ]
}

update_cell = {
  "rows": [
       row_data
  ],
  "fields": "*",
  "range": 
    {
      "sheetId": input_master.tab_id,
      "startRowIndex": 2,
      "startColumnIndex": 0,
      "endColumnsIndex": 1
    }
}

request_body = {
  "requests": [
      {"updateCells": update_cell}],
  "includeSpreadsheetInResponse": True,
  "responseIncludeGridData": True}

service.spreadsheets().batchUpdate(spreadsheetId=my_id, body=request_body).execute()

这消灭了一切，我不确定为什么.我认为我不了解fields='*属性.

This wiped out everything and I'm not sure why. I don't think I understand the fields='* attribute.

TL; DR 我想将格式应用于单个列中的所有行.就像我在第二行，第一列中使用绘画格式"工具并将其一直拖动到最后一行一样.

TL;DR I want to apply a format to all rows in a single column. Much like if I used the "Paint Format" tool on the second row, first column and dragged it all the way down to the last row.

-----更新

感谢评论，这是我的解决方案:

Hi, thanks to the comments this was my solution:

###collect all formats from second row
import json
row_2 = goolge_api.service.spreadsheets().get(
    spreadsheetId=spreadsheet_id,
    ranges="tab1!A2:AO2",
    includeGridData=True).execute()
my_dictionary = {}
row_values = row_2['sheets'][0]['data'][0]['rowData'][0]['values']
for column in range(0,len(row_values)):
    my_dictionary[column] = row_values[column]
json.dumps(my_dictionary,open('config/format.json','w'))

###Part 2, apply formats
requests = []
my_dict = json.load(open('config/format.json'))
for column in my_dict:
    requests.append(

    {
      "repeatCell": {
        "range": {
            "sheetId": tab_id,
            "startRowIndex": str(1),
            "startColumnIndex":str(column),
            "endColumnIndex":str(int(column)+1)
    },
       "cell": {
          "userEnteredFormat": my_dict[column]
    },
        'fields': "userEnteredFormat({})".format(",".join(my_dict[column].keys()))
        }
    })

body = {"requests": requests}
google_api.service.spreadsheets().batchUpdate(spreadsheetId=s.spreadsheet_id,body=body).execute()

推荐答案

当您将fields作为请求的一部分时，您向API端点指示它将使用信息覆盖目标范围内的指定字段.在您上传的资源中找到. fields="*"相应地解释为此请求指定给定范围的整个数据和元数据.从范围中删除任何以前的数据和元数据，而使用提供的内容."

When you include fields as a part of the request, you indicate to the API endpoint that it should overwrite the specified fields in the targeted range with the information found in your uploaded resource. fields="*" correspondingly is interpreted as "This request specifies the entire data and metadata of the given range. Remove any previous data and metadata from the range and use what is supplied instead."

因此，您在updateCells请求中未指定的所有内容都将从请求中提供的range中删除(例如值，公式，数据验证等).

Thus, anything not specified in your updateCells requests will be removed from the range supplied in the request (e.g. values, formulas, data validation, etc.).

您可以在 batchUpdate 指南中了解更多信息.

You can learn more in the guide to batchUpdate

对于 updateCell请求，fields参数如下所述:

For an updateCell request, the fields parameter is as described:

应更新的CellData字段.必须至少指定一个字段.根是CellData; 行值".不应该指定.单个"*"可用作列出每个字段的简写.

The fields of CellData that should be updated. At least one field must be specified. The root is the CellData; 'row.values.' should not be specified. A single "*" can be used as short-hand for listing every field.

如果您随后查看 CellData ，您会观察以下字段:

If you then view the resource description of CellData, you observe the following fields:

• "userEnteredValue"
• 有效值"
• "formattedValue"
• "userEnteredFormat"
• "effectiveFormat"
• 超链接"
• 笔记"
• "textFormatRuns"
• "dataValidation"
• 数据透视表"

• "userEnteredValue"
• "effectiveValue"
• "formattedValue"
• "userEnteredFormat"
• "effectiveFormat"
• "hyperlink"
• "note"
• "textFormatRuns"
• "dataValidation"
• "pivotTable"

因此，您的请求的正确字段规范可能是fields="effectiveFormat"，因为这是您在row_data属性中提供的唯一字段.

Thus, the proper fields specification for your request is likely to be fields="effectiveFormat", since this is the only field you supply in your row_data property.

还考虑使用 repeatCell请求，如果您只是指定一种格式.

Consider also using the repeatCell request if you are just specifying a single format.

这篇关于更新整个工作表的单行格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！