How to Change color of range in google sheets api

问题:

I tried to use google sheets api v4 to modify an existing spreadsheet in VB.Net. I read and write in the file with BatchGetRequest and BatchUpdateRequest:

        'Get File's Datas
        Dim reqBatchGet As SpreadsheetsResource.ValuesResource.BatchGetRequest = service.Spreadsheets.Values.BatchGet(fileId)
        reqBatchGet.Ranges = "'2016'!A1:AP60"
        Dim test = reqBatchGet.Execute()

        Dim myBatchUpdate As BatchUpdateValuesRequest = New BatchUpdateValuesRequest()
        myBatchUpdate.ValueInputOption = "RAW"
        myBatchUpdate.Data = New List(Of ValueRange)


        For Each res As GetDatasMonitoringAgencyResult In result

            Dim vrxN As ValueRange = New ValueRange()
            Dim lstN As IList(Of IList(Of Object)) = New List(Of IList(Of Object))

            lstN.Add(New List(Of Object)(New Object() {res.N01, res.N02, res.N03, res.N04, res.N05, res.N06, res.N07, res.N08, res.N09, res.N10, res.N11, res.N12}))

            For Each n In lstN

                For Each dataInN In n

                    If dataInN Is Nothing Then
                        dataInN = "-"
                    End If
                Next
            Next

            vrxN.Values = lstN

            Dim RangeN As String = String.Format("'{0}'!D{1}:O{1}", Now.Year, res.ligne)
            vrxN.Range = RangeN
            vrxN.MajorDimension = "ROWS"

            Dim vrxN_1 As ValueRange = New ValueRange()
            Dim lstN_1 As IList(Of IList(Of Object)) = New List(Of IList(Of Object))

            lstN_1.Add(New List(Of Object)(New Object() {res.N_01, res.N_02, res.N_03, res.N_04, res.N_05, res.N_06, res.N_07, res.N_08, res.N_09, res.N_10, res.N_11, res.N_12}))

            For Each n_1 In lstN_1

                For Each dataInN_1 In n_1

                    If dataInN_1 Is Nothing Then
                        dataInN_1 = ""
                    End If
                Next
            Next

            vrxN_1.Values = lstN_1
            Dim RangeN_1 As String = String.Format("'{0}'!AD{1}:AO{1}", Now.Year.ToString(), res.ligne)
            vrxN_1.Range = RangeN_1
            vrxN_1.MajorDimension = "ROWS"

            myBatchUpdate.Data.Add(vrxN)
            myBatchUpdate.Data.Add(vrxN_1)
        Next
        Dim reqBatchUpdate As SpreadsheetsResource.ValuesResource.BatchUpdateRequest = service.Spreadsheets.Values.BatchUpdate(myBatchUpdate, fileId)

        reqBatchUpdate.Execute()

Now, I want change color of some cells. I tried with conditionalRule:

  Dim req As Request = New Request()
        Dim acfrr As AddConditionalFormatRuleRequest = New AddConditionalFormatRuleRequest()
        acfrr.Rule = New ConditionalFormatRule()
        Dim range1 As GridRange = New GridRange()
        range1.SheetId = 0
        range1.StartColumnIndex = 1
        range1.StartRowIndex = 1
        acfrr.Rule.Ranges = New List(Of GridRange)
        acfrr.Rule.Ranges.Add(range1)
        acfrr.Rule.GradientRule = New GradientRule()
        acfrr.Rule.GradientRule.Minpoint = New InterpolationPoint()
        acfrr.Rule.GradientRule.Minpoint.Type = "MIN"
        acfrr.Rule.GradientRule.Minpoint.Color = New Google.Apis.Sheets.v4.Data.Color()
        acfrr.Rule.GradientRule.Minpoint.Color.Red = 1
        acfrr.Rule.GradientRule.Minpoint.Color.Green = 0
        acfrr.Rule.GradientRule.Minpoint.Color.Blue = 0
        acfrr.Rule.GradientRule.Midpoint = New InterpolationPoint()
        acfrr.Rule.GradientRule.Midpoint.Type = "NUMBER"
        acfrr.Rule.GradientRule.Midpoint.Value = 0.5
        acfrr.Rule.GradientRule.Midpoint.Color = New Google.Apis.Sheets.v4.Data.Color()
        acfrr.Rule.GradientRule.Midpoint.Color.Red = 0
        acfrr.Rule.GradientRule.Midpoint.Color.Green = 1
        acfrr.Rule.GradientRule.Midpoint.Color.Blue = 0
        acfrr.Rule.GradientRule.Maxpoint = New InterpolationPoint()
        acfrr.Rule.GradientRule.Maxpoint.Type = "MAX"
        acfrr.Rule.GradientRule.Maxpoint.Color = New Google.Apis.Sheets.v4.Data.Color()
        acfrr.Rule.GradientRule.Maxpoint.Color.Red = 0
        acfrr.Rule.GradientRule.Maxpoint.Color.Green = 0
        acfrr.Rule.GradientRule.Maxpoint.Color.Blue = 1
        acfrr.Index = 0
        req.AddConditionalFormatRule = acfrr
        Dim testReq As BatchUpdateSpreadsheetRequest = New BatchUpdateSpreadsheetRequest()
        testReq.Requests = New List(Of Request)
        testReq.Requests.Add(req)
        Dim responseAddConditionalRules = service.Spreadsheets.BatchUpdate(testReq, fileId).Execute

It work if conditions are True (if value is MIN, Number format or Max) But I want just change color range without conditions. But I don't find an example to understand how to do it.

回答1:

string spreadsheetId = "1DD3zfGe6.......UtENHhnBwz0CA";

//get sheet id by sheet name
Spreadsheet spr = service.Spreadsheets.Get(spreadsheetId).Execute();
Sheet sh = spr.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int sheetId = (int)sh.Properties.SheetId;

//define cell color
var userEnteredFormat = new CellFormat()
{
    BackgroundColor = new Color()
    {
        Blue = 0,
        Red = 1,
        Green = (float)0.5,
        Alpha = (float)0.1
    },
    TextFormat = new TextFormat()
    {
        Bold = true
    }
};
BatchUpdateSpreadsheetRequest bussr = new BatchUpdateSpreadsheetRequest();

//create the update request for cells from the first row
var updateCellsRequest = new Request()
{
    RepeatCell = new RepeatCellRequest()
    {
        Range = new GridRange()
        {
            SheetId = sheetId,
            StartColumnIndex = 0,
            StartRowIndex = 0,
            EndColumnIndex = 28,
            EndRowIndex = 1
        },
        Cell = new CellData()
        {
            UserEnteredFormat = userEnteredFormat
        },
        Fields = "UserEnteredFormat(BackgroundColor,TextFormat)"
    }
};
bussr.Requests = new List<Request>();
bussr.Requests.Add(updateCellsRequest);            
bur = service.Spreadsheets.BatchUpdate(bussr, spreadsheetId);
bur.Execute();

回答2:

Conditional format rules are just that: conditional. They apply the format only if the condition is true (or in the case of gradients: always apply a varying color based on the scale of the gradient).

If you want to unconditionally set the background color of a cell, do just that: set the background color on the cell. The Samples - Formatting page has a lot of examples of how to do that. Specifically: Format a header row

Copying the JSON from that page (which would translate straightforwardly into any of the client libraries),

    POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate
    {
      "requests": [
        {
          "repeatCell": {
            "range": {
              "sheetId": sheetId,
              "startRowIndex": 0,
              "endRowIndex": 1
            },
            "cell": {
              "userEnteredFormat": {
                "backgroundColor": {
                  "red": 0.0,
                  "green": 0.0,
                  "blue": 0.0
                },
                "horizontalAlignment" : "CENTER",
                "textFormat": {
                  "foregroundColor": {
                    "red": 1.0,
                    "green": 1.0,
                    "blue": 1.0
                  },
                  "fontSize": 12,
                  "bold": true
                }
              }
            },
            "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)"
          }
        },
        {
          "updateSheetProperties": {
            "properties": {
              "sheetId": sheetId,
              "gridProperties": {
                "frozenRowCount": 1
              }
            },
            "fields": "gridProperties.frozenRowCount"
          }
        }
      ]
    }