如何更改工作表标签的颜色 [英] How can I change the color of a worksheet's tab

问题描述

我有一个python脚本，该脚本从外部资源中提取数据并将数据添加到Google表格中.在大多数情况下，我都能正常工作，但我想更改标签的颜色以表示脚本状态.

I have a python script that is pulling data from an external resource and adding data to a Google Sheet. For the most part I have everything working, except I'd like to change the color of a tab to signify the script status.

整个过程从复制工作表中的现有标签开始.默认情况下，模板选项卡具有黑色突出显示.然后，我想将黑色更改为另一种颜色，以显示正在进行数据收集.完成后，根据数据结果将颜色更改为绿色或红色.

The whole process starts by duplicating an existing tab within the sheet. The template tab has a black highlight by default. I then want to change the black to another color to show the data collection is in progress. And when done, change the color to green or red depending on if the results of the data.

但是我找不到如何更改颜色的有效示例.这是我到目前为止的内容:

However I cannot find a working example of how to change the color. Here is what I have so far:

    title = 'Duplicate'
    template_id = 1000

    sheet = open_worksheet() # does all the auth/credential work
    sheet.duplicate_sheet(template_id, insert_sheet_index=0, new_sheet_name=title)
    new_tab = sheet.worksheet(title)

    body = {
        "requests": [
            {
                "updateSheetProperties": {
                    "properties": {
                        "sheetId": 1001,
                        "title": title,
                        "tabColor": {
                            "red": 1.0,
                            "green": 0.3,
                            "blue": 0.4
                        }
                    },
                    "fields": "*"
                }
            }
        ]
    }

    try:
        res = sheet.batch_update(body)
        # res = new_tab.batch_update(body)
        pprint(res)
    except gspread.exceptions.APIError as gea:
        pprint(gea.args[0], width=100)

如果我尝试对 new_tab 指针运行 batch_update()，我会得到:

If I try running the batch_update() against the new_tab pointer I get:

dict(vr, range=absolute_range_name(self.title, vr['range']))
TypeError: string indices must be integers

如果针对整个 sheet 运行它，则会得到:

If I run it against the whole sheet I get:

{'code': 400,
 'message': "Invalid requests[0].updateSheetProperties: You can't delete all the rows on the sheet.",
 'status': 'INVALID_ARGUMENT'}

如何解决我的请求，以使其正确更新单个标签?

How do I fix my request so that it correctly updates the single tab?

此处是 Google表格API 和 Sheet属性，我一直在这里查找请求应该看起来.

Here is a link to the Google Sheet API and Sheet properties where I've been looking up how the request should look.

推荐答案

我相信您的目标如下.

• 您要使用gspread更改Google Spreadsheet的标签颜色.
• 您要使用 sheet.duplicate_sheet(template_id，insert_sheet_index = 0，new_sheet_name = title)插入新工作表，并希望更改新工作表的标签颜色.

• You want to change the tab color of Google Spreadsheet using gspread.
• You want to insert new sheet using sheet.duplicate_sheet(template_id, insert_sheet_index=0, new_sheet_name=title) and want to change the tab color of the new sheet.

• 在这种情况下，可以使用 new_tab.id 检索工作表ID.您可以将此值用于请求正文.
• 根据您的情况，我认为可以使用 tabColor 代替 * 作为 fields 的值.
  • 我认为这可能是您遇到问题的原因.因为使用 * 时，除 tabColor 之外的其他字段也会更改.
  • In this case, the sheet ID can be retrieved with new_tab.id. You can use this value to the request body.
  • In your situation, I think that tabColor instead of * can be used as the value of fields.
    • I thought that this might be the reason of your issue. Because when * is used, other fields except for tabColor are also changed.

    当以上几点反映到您的脚本中时，它如下所示.

    When above points are reflected to your script, it becomes as follows.

    请按如下所示修改 body 并再次进行测试.

    Please modify body as follows and test it again.

    body = {
        "requests": [
            {
                "updateSheetProperties": {
                    "properties": {
                        "sheetId": new_tab.id,
                        # "title": title, # In this case, I think that this might not be required to be used.
                        "tabColor": {
                            "red": 1.0,
                            "green": 0.3,
                            "blue": 0.4
                        }
                    },
                    "fields": "tabColor"
                }
            }
        ]
    }
    

    参考文献:

    • batch_update(body)
    • 方法:sheetssheets.batchUpdate
    • UpdateSheetPropertiesRequest

    这篇关于如何更改工作表标签的颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！