使用pygsheets进行Google表格API的条件格式 [英] Conditional formatting with pygsheets for google sheets api

问题描述

我正在使用 http://pygsheets.readthedocs.io/en/Latest/index.html 围绕Google Sheets API v4的包装器.我对使用google-sheets-api v4设置条件格式感兴趣.我正在尝试使用自定义公式根据行中"Q"列的值突出显示一行.如果q列包含"TRASH"，则我想将行涂成红色.

I'm working with http://pygsheets.readthedocs.io/en/latest/index.html a wrapper around the google sheets api v4. I am interested in setting conditional formatting using the google-sheets-api v4. I'm trying to use a custom formula to highlight a row based on the value of the "Q" column in the row. if the q column contains 'TRASH', I want to colour the row red.

当我浏览 https://github中的pygheets库时.com/nithinmurali/pygsheets/blob/master/pygsheets/client.py 我碰到了，我相信这是发送此请求的方式:

As I look through the pygheets library in https://github.com/nithinmurali/pygsheets/blob/master/pygsheets/client.py I came across, which I believe is the way to send this request:

 # @TODO use batch update more efficiently
def sh_batch_update(self, spreadsheet_id, request, fields=None, batch=False):
    if type(request) == list:
        body = {'requests': request}
    else:
        body = {'requests': [request]}
    final_request = self.service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body,
                                                            fields=fields)
return self._execute_request(spreadsheet_id, final_request, batch)

此外，在 https://developers.google.com/中sheets/api/samples/conditional-formatting#add_a_custom_formula_rule_to_a_range ，给出了有关如何发送自定义请求的示例.基于此，我有:

Further, in https://developers.google.com/sheets/api/samples/conditional-formatting#add_a_custom_formula_rule_to_a_range, an example is given on how to send a custom request. Based on this I have:

import tkinter as tk
import tkFileDialog
import pygsheets

def cfr1(sheetId):

    # Apply to range: A1:R
    # Format cells if...: Custom formula is
    # (formula:) =$Q1="TRASH"

    return {"requests": [
        {
          "addConditionalFormatRule": {
            "rule": {
              "ranges": [
                {
                  "sheetId": sheetId,
                  "startColumnIndex": 'A',
                  "endColumnIndex": 'R',
                  "startRowIndex": 1,
                  "endRowIndex": 8
                }
              ],
              "booleanRule": {
                "condition": {
                  "type": "CUSTOM_FORMULA",
                  "values": [
                    {
                      "userEnteredValue": '=$Q1="TRASH"'
                    }
                  ]
                },
                "format": {
                    "backgroundColor": {
                          "red": 1.0
                          # "green": 0.0,
                          # "blue": 0.0
                        }


                }
              }
            },
            "index": 0
          }
        }
      ]
    }

root = tk.Tk()
root.withdraw()
file_path = tkFileDialog.askopenfilename()
print file_path
file_name = file_path.split('/')[-1]
print file_name
file_name_segments = file_name.split('_')
spreadsheet = file_name_segments[0]
worksheet = file_name_segments[1]+'_'+file_name_segments[2]
print worksheet
print spreadsheet

gc = pygsheets.authorize(outh_file='client_secret_xxx.apps.googleusercontent.com.json')
a =gc.list_ssheets()
wb_list = [d['name'] for d in a]
print wb_list
if spreadsheet not in wb_list:
    print "Doesn't exist .."
else:
    ssheet = gc.open(spreadsheet)
    print ssheet.title
    print 'ws '+worksheet
    ws = ssheet.worksheet('title',worksheet)

    gc.sh_batch_update(ssheet.id,cfr1(ws.id),'A1:R8')

但是我得到了:

googleapiclient.errors.HttpError: <HttpError 400 when requesting htps://sheets.googleapis.com/v4/spreadsheets/1Ypb_P**********pFt_SE:batchUpdate?fields=A1%3AR
8&alt=json returned "Invalid JSON payload received. Unknown name "requests" at 'requests[0]': Cannot find field.">

我在做什么错了?

推荐答案

在Json有效负载return {"requests": [中，您不需要密钥requests作为sh_batch_update内部的密钥，它已经将其包装在请求中.看看worksheet.py中delete_cols的含义，以查看示例用法.

In the Json payload return {"requests": [ you dont need the key requests as inside the sh_batch_update , its already wrapping it in requests. Take a look at the implimentation of delete_cols in worksheet.py to see an example usage.

您可以有效地做到这一点

So effectively you can just do,

return {
          "addConditionalFormatRule": {
            "rule": {

您也不需要在此处传递范围gc.sh_batch_update(ssheet.id,cfr1(ws.id),'A1:R8')字段param决定要返回的响应字段

also you dont need to pass the range here gc.sh_batch_update(ssheet.id,cfr1(ws.id),'A1:R8') the fields param decides the response fields to return

这篇关于使用pygsheets进行Google表格API的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！