التنسيق المشروط

يتيح لك التنسيق الشرطي تنسيق الخلايا بحيث يتغيّر شكلها ديناميكيًا وفقًا للقيمة التي تحتوي عليها أو للقيم في الخلايا الأخرى. هناك العديد من الاستخدامات المحتملة للتنسيق الشرطي، بما في ذلك هذه الاستخدامات:

• ميِّز الخلايا التي تزيد قيمتها عن حدّ معيّن (على سبيل المثال، باستخدام نص غامق ل جميع المعاملات التي تزيد قيمتها عن 2,000 دولار أمريكي).
• تنسيق الخلايا بحيث يختلف لونها حسب قيمتها (على سبيل المثال، تطبيق خلفية حمراء أكثر كثافة مع زيادة المبلغ الذي يتجاوز 2,000 دولار أمريكي)
• تنسيق الخلايا ديناميكيًا استنادًا إلى محتوى الخلايا الأخرى (على سبيل المثال، تمييز عنوان المواقع التي يكون فيها حقل "المدة على السوق" أكبر من 90 يومًا)

يمكنك أيضًا تنسيق الخلايا استنادًا إلى قيمتها وقيم الخلايا الأخرى. على سبيل المثال، يمكنك تنسيق نطاق خلايا استنادًا إلى قيمته مقارنةً بالقيمة المتوسطة للنطاق:

الشكل 1: التنسيق لإبراز القيم أعلى أو أقل من متوسط العمر

في هذا المثال، يتم تنسيق الخلايا في كل صف وفقًا لمقارنة القيمة في عمود age بالمتوسط الحسابي لجميع الأعمار. تظهر الصفوف التي يكون فيها العمر أعلى من المتوسط بنص أحمر، بينما تظهر الصفوف التي يكون فيها العمر أقل من المتوسط بخلفية حمراة. يتضمّن صفان قيمة age تتطابق مع متوسّط العمر (48)، ولا تتلقّى هذه الخلايا أي تنسيق خاص. (للاطّلاع على رمز المصدر الذي يُنشئ هذا التنسيق الشرطي، راجِع المثال أدناه).

قواعد التنسيق الشرطي

يتم التعبير عن التنسيق الشرطي باستخدام قواعد التنسيق. يخزِّن كل جدول بيانات قائمة بهذه القواعد ويطبّقها بالترتيب نفسه الذي تظهر به في القائمة. تتيح لك واجهة برمجة التطبيقات "جداول بيانات Google" إضافة قواعد التنسيق هذه وتعديلها وحذفها.

وتحدّد كل قاعدة نطاقًا مستهدَفًا ونوع القاعدة وشروط تفعيل القاعدة وأي تنسيق مطلوب تطبيقه.

النطاق المستهدَف: يمكن أن يكون خلية واحدة أو نطاقًا من الخلايا أو عدة نطاقات.

نوع القاعدة: هناك فئتان من القواعد:

• لا تطبِّق القواعد المنطقية تنسيقًا إلا في حال استيفاء معايير محدّدة.
• تحسب قواعد التدرّج لون خلفية الخلية، استنادًا إلى قيمة الخلية.

تختلف الشروط التي يتم تقييمها والتنسيقات التي يمكنك تطبيقها حسب كل نوع من أنواع القواعد هذه، كما هو موضّح بالتفصيل في الأقسام التالية.

القواعد المنطقية

يحدِّد BooleanRule ما إذا كان سيتم تطبيق تنسيق معيّن، استنادًا إلى BooleanCondition الذي يتم تقييمه على أنّه true أو false. تأخذ القاعدة المنطقية الشكل التالي:

{
  "condition": {
    object(BooleanCondition)
  },
  "format": {
    object(CellFormat)
  },
}

يمكن أن يستخدم الشرط القيمة المضمّنة ConditionType، أو يمكن أن يستخدم صيغة مخصّصة لتقييمات أكثر تعقيدًا.

تتيح لك الأنواع المضمّنة تطبيق التنسيق وفقًا للحدود الدنيا الرقمية أو مقارنة النصوص أو ما إذا كانت الخلية مملوءة. على سبيل المثال، يعني NUMBER_GREATER أنّ قيمة الخلية يجب أن تكون أكبر من قيمة الشرط. يتم تقييم القواعد دائمًا وفقًا للخلية المستهدَفة.

الصيغة المخصّصة هي نوع خاص من الشروط يتيح لك تطبيق التنسيق وفقًا لتعبير عشوائي، ما يسمح أيضًا بتقييم أي خلية، وليس الخلية المستهدَفة فقط. يجب أن تؤدي صيغة الشرط إلى القيمة true.

لتحديد التنسيق الذي تطبّقه قاعدة منطقية، استخدِم مجموعة فرعية من نوع CellFormat لتحديد:

• ما إذا كان النص في الخلية غامقًا أو مائلًا أو مشطوبًا
• لون النص في الخلية.
• لون خلفية الخلية.

استخدِم ميزات التنسيق المذكورة أعلاه فقط. على الرغم من أنّ نوع CellFormat يمكنه التعبير عن تنسيقات أخرى، بما في ذلك التشديد أو المحاذاة أو الحدود، يؤدي محاولة استخدام هذه العناصر في قاعدة تنسيق شَرطي إلى ظهور خطأ 400 invalid request.

قواعد التدرّج

تحدِّد سمة GradientRule نطاقًا من الألوان التي تتوافق مع نطاق من القيم. تكون قاعدة التدرّج على النحو التالي:

{
  "minpoint": {
    object(InterpolationPoint)
  },
  "midpoint": {
    object(InterpolationPoint)
  },
  "maxpoint": {
    object(InterpolationPoint)
  },
}

يحدِّد كل InterpolationPoint لونًا وقيمته المقابلة. تحدِّد مجموعة من ثلاث نقاط ملفًا شخصيًا لتدرّج الألوان.

إدارة قواعد التنسيق الشرطي

لإنشاء قواعد تنسيق شرطي أو تعديلها أو حذفها، استخدِم الطريقة spreadsheets.batchUpdate مع نوع الطلب المناسب:

• أضِف قواعد إلى القائمة في الفهرس المحدّد باستخدام الرمز AddConditionalFormatRuleRequest.

• استبدِل القواعد في القائمة أو أعِد ترتيبها في الفهرس المحدَّد باستخدام الرمز UpdateConditionalFormatRuleRequest.

• أزِل القواعد من القائمة في الفهرس المحدّد باستخدام الرمز DeleteConditionalFormatRuleRequest.

مثال

يوضّح المثال التالي كيفية إنشاء التنسيق الشرطي المعروض في لقطة الشاشة في أعلى هذه الصفحة. للحصول على أمثلة إضافية، يُرجى الاطّلاع على صفحة عيّناتالتنسيق الشرطي.

برمجة تطبيقات

sheets/api/spreadsheet_snippets.gs

العرض على GitHub

/**
 * conditional formatting
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} spreadsheet
 */
Snippets.prototype.conditionalFormatting = (spreadsheetId) => {
  try {
    let myRange = Sheets.newGridRange();
    myRange.sheetId = 0;
    myRange.startRowIndex = 0;
    myRange.endRowIndex = 11;
    myRange.startColumnIndex = 0;
    myRange.endColumnIndex = 4;

    // Request 1
    let rule1ConditionalValue = Sheets.newConditionValue();
    rule1ConditionalValue.userEnteredValue = '=GT($D2,median($D$2:$D$11))';

    let rule1ConditionFormat = Sheets.newCellFormat();
    rule1ConditionFormat.textFormat = Sheets.newTextFormat();
    rule1ConditionFormat.textFormat.foregroundColor = Sheets.newColor();
    rule1ConditionFormat.textFormat.foregroundColor.red = 0.8;

    let rule1Condition = Sheets.newBooleanCondition();
    rule1Condition.type = 'CUSTOM_FORMULA';
    rule1Condition.values = [rule1ConditionalValue];

    let rule1BooleanRule = Sheets.newBooleanRule();
    rule1BooleanRule.condition = rule1Condition;
    rule1BooleanRule.format = rule1ConditionFormat;

    let rule1 = Sheets.newConditionalFormatRule();
    rule1.ranges = [myRange];
    rule1.booleanRule = rule1BooleanRule;

    let request1 = Sheets.newRequest();
    let addConditionalFormatRuleRequest1 =
      Sheets.newAddConditionalFormatRuleRequest();
    addConditionalFormatRuleRequest1.rule = rule1;
    addConditionalFormatRuleRequest1.index = 0;
    request1.addConditionalFormatRule = addConditionalFormatRuleRequest1;

    // Request 2
    let rule2ConditionalValue = Sheets.newConditionValue();
    rule2ConditionalValue.userEnteredValue = '=LT($D2,median($D$2:$D$11))';

    let rule2ConditionFormat = Sheets.newCellFormat();
    rule2ConditionFormat.textFormat = Sheets.newTextFormat();
    rule2ConditionFormat.textFormat.foregroundColor = Sheets.newColor();
    rule2ConditionFormat.textFormat.foregroundColor.red = 1;
    rule2ConditionFormat.textFormat.foregroundColor.green = 0.4;
    rule2ConditionFormat.textFormat.foregroundColor.blue = 0.4;

    let rule2Condition = Sheets.newBooleanCondition();
    rule2Condition.type = 'CUSTOM_FORMULA';
    rule2Condition.values = [rule2ConditionalValue];

    let rule2BooleanRule = Sheets.newBooleanRule();
    rule2BooleanRule.condition = rule2Condition;
    rule2BooleanRule.format = rule2ConditionFormat;

    let rule2 = Sheets.newConditionalFormatRule();
    rule2.ranges = [myRange];
    rule2.booleanRule = rule2BooleanRule;

    let request2 = Sheets.newRequest();
    let addConditionalFormatRuleRequest2 =
      Sheets.newAddConditionalFormatRuleRequest();
    addConditionalFormatRuleRequest2.rule = rule2;
    addConditionalFormatRuleRequest2.index = 0;
    request2.addConditionalFormatRule = addConditionalFormatRuleRequest2;

    // Batch send the requests
    const requests = [request1, request2];
    let batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
    batchUpdate.requests = requests;
    const response =
      Sheets.Spreadsheets.batchUpdate(batchUpdate, spreadsheetId);
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/ConditionalFormatting.java

العرض على GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddConditionalFormatRuleRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.BooleanCondition;
import com.google.api.services.sheets.v4.model.BooleanRule;
import com.google.api.services.sheets.v4.model.CellFormat;
import com.google.api.services.sheets.v4.model.Color;
import com.google.api.services.sheets.v4.model.ConditionValue;
import com.google.api.services.sheets.v4.model.ConditionalFormatRule;
import com.google.api.services.sheets.v4.model.GridRange;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.TextFormat;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Conditional Formatting API */
public class ConditionalFormatting {
  /**
   * Create conditional formatting.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @return updated changes count.
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    List<GridRange> ranges = Collections.singletonList(new GridRange()
        .setSheetId(0)
        .setStartRowIndex(1)
        .setEndRowIndex(11)
        .setStartColumnIndex(0)
        .setEndColumnIndex(4)
    );
    List<Request> requests = Arrays.asList(
        new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest()
            .setRule(new ConditionalFormatRule()
                .setRanges(ranges)
                .setBooleanRule(new BooleanRule()
                    .setCondition(new BooleanCondition()
                        .setType("CUSTOM_FORMULA")
                        .setValues(Collections.singletonList(
                            new ConditionValue().setUserEnteredValue(
                                "=GT($D2,median($D$2:$D$11))")
                        ))
                    )
                    .setFormat(new CellFormat().setTextFormat(
                        new TextFormat().setForegroundColor(
                            new Color().setRed(0.8f))
                    ))
                )
            )
            .setIndex(0)
        ),
        new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest()
            .setRule(new ConditionalFormatRule()
                .setRanges(ranges)
                .setBooleanRule(new BooleanRule()
                    .setCondition(new BooleanCondition()
                        .setType("CUSTOM_FORMULA")
                        .setValues(Collections.singletonList(
                            new ConditionValue().setUserEnteredValue(
                                "=LT($D2,median($D$2:$D$11))")
                        ))
                    )
                    .setFormat(new CellFormat().setBackgroundColor(
                        new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f)
                    ))
                )
            )
            .setIndex(0)
        )
    );

    BatchUpdateSpreadsheetResponse result = null;
    try {
      // Execute the requests.
      BatchUpdateSpreadsheetRequest body =
          new BatchUpdateSpreadsheetRequest()
              .setRequests(requests);
      result = service.spreadsheets()
          .batchUpdate(spreadsheetId, body)
          .execute();
      System.out.printf("%d cells updated.", result.getReplies().size());
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_conditional_formatting.js

العرض على GitHub

function conditionalFormatting(spreadsheetId, callback) {
  const myRange = {
    sheetId: 0,
    startRowIndex: 1,
    endRowIndex: 11,
    startColumnIndex: 0,
    endColumnIndex: 4,
  };
  const requests = [{
    addConditionalFormatRule: {
      rule: {
        ranges: [myRange],
        booleanRule: {
          condition: {
            type: 'CUSTOM_FORMULA',
            values: [{userEnteredValue: '=GT($D2,median($D$2:$D$11))'}],
          },
          format: {
            textFormat: {foregroundColor: {red: 0.8}},
          },
        },
      },
      index: 0,
    },
  }, {
    addConditionalFormatRule: {
      rule: {
        ranges: [myRange],
        booleanRule: {
          condition: {
            type: 'CUSTOM_FORMULA',
            values: [{userEnteredValue: '=LT($D2,median($D$2:$D$11))'}],
          },
          format: {
            backgroundColor: {red: 1, green: 0.4, blue: 0.4},
          },
        },
      },
      index: 0,
    },
  }];

  const body = {
    requests,
  };
  try {
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.replies.length} cells updated.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_conditional_formatting.js

العرض على GitHub

/**
 * Conditionally formats a Spreadsheet.
 * @param {string} spreadsheetId A Spreadsheet ID.
 * @return {obj} spreadsheet information
 */
async function conditionalFormatting(spreadsheetId) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  const myRange = {
    sheetId: 0,
    startRowIndex: 1,
    endRowIndex: 11,
    startColumnIndex: 0,
    endColumnIndex: 4,
  };
  const requests = [
    {
      addConditionalFormatRule: {
        rule: {
          ranges: [myRange],
          booleanRule: {
            condition: {
              type: 'CUSTOM_FORMULA',
              values: [{userEnteredValue: '=GT($D2,median($D$2:$D$11))'}],
            },
            format: {
              textFormat: {foregroundColor: {red: 0.8}},
            },
          },
        },
        index: 0,
      },
    },
    {
      addConditionalFormatRule: {
        rule: {
          ranges: [myRange],
          booleanRule: {
            condition: {
              type: 'CUSTOM_FORMULA',
              values: [{userEnteredValue: '=LT($D2,median($D$2:$D$11))'}],
            },
            format: {
              backgroundColor: {red: 1, green: 0.4, blue: 0.4},
            },
          },
        },
        index: 0,
      },
    },
  ];
  const resource = {
    requests,
  };
  try {
    const response = await service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    console.log(`${response.data.replies.length} cells updated.`);
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetConditionalFormatting.php

العرض على GitHub

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\Request;

function conditionalFormatting($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);

        try{
            $myRange = [
                'sheetId' => 0,
                'startRowIndex' => 1,
                'endRowIndex' => 11,
                'startColumnIndex' => 0,
                'endColumnIndex' => 4,
            ];
            //execute the request
            $requests = [
                new Google_Service_Sheets_Request([
                'addConditionalFormatRule' => [
                    'rule' => [
                        'ranges' => [ $myRange ],
                        'booleanRule' => [
                            'condition' => [
                                'type' => 'CUSTOM_FORMULA',
                                'values' => [ [ 'userEnteredValue' => '=GT($D2,median($D$2:$D$11))' ] ]
                            ],
                            'format' => [
                                'textFormat' => [ 'foregroundColor' => [ 'red' => 0.8 ] ]
                                ]
                                ]
                            ],
                            'index' => 0
                            ]
                        ]),
                        new Google_Service_Sheets_Request([
                'addConditionalFormatRule' => [
                    'rule' => [
                        'ranges' => [ $myRange ],
                        'booleanRule' => [
                            'condition' => [
                                'type' => 'CUSTOM_FORMULA',
                                'values' => [ [ 'userEnteredValue' => '=LT($D2,median($D$2:$D$11))' ] ]
                            ],
                            'format' => [
                                'backgroundColor' => [ 'red' => 1, 'green' => 0.4, 'blue' => 0.4 ]
                            ]
                            ]
                        ],
                    'index' => 0
                ]
                ])
        ];

        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
            'requests' => $requests
        ]);
        $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        printf("%d cells updated.", count($response->getReplies()));
        return $response;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
    }
}

Python

sheets/snippets/sheets_conditional_formatting.py

العرض على GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def conditional_formatting(spreadsheet_id):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)

    my_range = {
        "sheetId": 0,
        "startRowIndex": 1,
        "endRowIndex": 11,
        "startColumnIndex": 0,
        "endColumnIndex": 4,
    }
    requests = [
        {
            "addConditionalFormatRule": {
                "rule": {
                    "ranges": [my_range],
                    "booleanRule": {
                        "condition": {
                            "type": "CUSTOM_FORMULA",
                            "values": [
                                {
                                    "userEnteredValue": (
                                        "=GT($D2,median($D$2:$D$11))"
                                    )
                                }
                            ],
                        },
                        "format": {
                            "textFormat": {"foregroundColor": {"red": 0.8}}
                        },
                    },
                },
                "index": 0,
            }
        },
        {
            "addConditionalFormatRule": {
                "rule": {
                    "ranges": [my_range],
                    "booleanRule": {
                        "condition": {
                            "type": "CUSTOM_FORMULA",
                            "values": [
                                {
                                    "userEnteredValue": (
                                        "=LT($D2,median($D$2:$D$11))"
                                    )
                                }
                            ],
                        },
                        "format": {
                            "backgroundColor": {
                                "red": 1,
                                "green": 0.4,
                                "blue": 0.4,
                            }
                        },
                    },
                },
                "index": 0,
            }
        },
    ]
    body = {"requests": requests}
    response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    print(f"{(len(response.get('replies')))} cells updated.")
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id
  conditional_formatting("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

العرض على GitHub

my_range = {
  sheet_id:           0,
  start_row_index:    1,
  end_row_index:      11,
  start_column_index: 0,
  end_column_index:   4
}
requests = [{
  add_conditional_format_rule: {
    rule:  {
      ranges:       [my_range],
      boolean_rule: {
        condition: {
          type:   'CUSTOM_FORMULA',
          values: [{ user_entered_value: '=GT($D2,median($D$2:$D$11))' }]
        },
        format:    {
          text_format: { foreground_color: { red: 0.8 } }
        }
      }
    },
    index: 0
  }
}, {
  add_conditional_format_rule: {
    rule:  {
      ranges:       [my_range],
      boolean_rule: {
        condition: {
          type:   'CUSTOM_FORMULA',
          values: [{ user_entered_value: '=LT($D2,median($D$2:$D$11))' }]
        },
        format:    {
          background_color: { red: 1, green: 0.4, blue: 0.4 }
        }
      }
    },
    index: 0
  }
}]
body = {
  requests: requests
}
batch_update = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
batch_update.requests = requests
result = service.batch_update_spreadsheet(spreadsheet_id, batch_update)
puts "#{result.replies.length} cells updated."