Data Validation and Formatting

class Worksheet:
    def set_data_validation(self, crange, condition_type, condition_values, **kwargs):
        """
        Set data validation rule for range.
        
        Parameters:
        - crange (str): Range to apply validation to
        - condition_type (str): Type of validation condition
        - condition_values (list): Values for validation condition
        - **kwargs: Additional validation options (strict, show_custom_ui, input_message, etc.)
        """

class Worksheet:
    def add_conditional_formatting(self, crange, condition_type, condition_values, format=None, **kwargs):
        """
        Add conditional formatting rule.
        
        Parameters:
        - crange (str): Range to apply formatting to
        - condition_type (str): Type of condition
        - condition_values (list): Values for condition
        - format (dict): Formatting to apply when condition is met
        - **kwargs: Additional formatting options
        """

class Worksheet:
    def apply_format(self, ranges, cell_format, fields="userEnteredFormat"):
        """
        Apply formatting to specified ranges.
        
        Parameters:
        - ranges: Range or list of ranges to format
        - cell_format (dict or Cell): Format specification
        - fields (str): Fields to update in the format
        """
    
    def merge_cells(self, start, end, merge_type='MERGE_ALL'):
        """
        Merge cells in specified range.
        
        Parameters:
        - start (str): Start cell address
        - end (str): End cell address
        - merge_type (str): Type of merge operation
        """

class Worksheet:
    def set_basic_filter(self, start_row=1, end_row=None, start_col=1, end_col=None):
        """
        Set basic filter on worksheet range.
        
        Parameters:
        - start_row (int): Starting row for filter
        - end_row (int): Ending row for filter
        - start_col (int): Starting column for filter
        - end_col (int): Ending column for filter
        """
    
    def clear_basic_filter(self):
        """Remove basic filter from worksheet."""
    
    def sort_range(self, start, end, basecolumnindex=0, sortorder="ASCENDING"):
        """
        Sort range by specified column.
        
        Parameters:
        - start (str): Start cell of range
        - end (str): End cell of range
        - basecolumnindex (int): Column index to sort by (0-indexed)
        - sortorder (str): Sort order ('ASCENDING' or 'DESCENDING')
        """

class Worksheet:
    def create_named_range(self, name, start, end=None, gid=None):
        """
        Create named range.
        
        Parameters:
        - name (str): Name for the range
        - start (str): Start cell address
        - end (str): End cell address
        - gid (int): Worksheet ID (uses current worksheet if None)
        
        Returns:
        str: Named range ID
        """
    
    def get_named_range(self, name) -> DataRange:
        """
        Get named range by name.
        
        Parameters:
        - name (str): Name of the range
        
        Returns:
        DataRange: Named range object
        """
    
    def get_named_ranges(self) -> list:
        """
        Get all named ranges in worksheet.
        
        Returns:
        list: List of DataRange objects
        """
    
    def delete_named_range(self, name):
        """
        Delete named range.
        
        Parameters:
        - name (str): Name of range to delete
        """
    
    def create_protected_range(self, start, end=None, named_range_id='', **kwargs) -> str:
        """
        Create protected range.
        
        Parameters:
        - start (str): Start cell address
        - end (str): End cell address
        - named_range_id (str): ID of named range to protect
        - **kwargs: Additional protection options (description, editors, etc.)
        
        Returns:
        str: Protected range ID
        """
    
    def remove_protected_range(self, protected_range_id):
        """
        Remove protected range.
        
        Parameters:
        - protected_range_id (str): ID of protected range to remove
        """
    
    def get_protected_ranges(self) -> list:
        """
        Get all protected ranges in worksheet.
        
        Returns:
        list: List of DataRange objects
        """

import pygsheets

# Get worksheet
gc = pygsheets.authorize()
sh = gc.open('Data Entry Form')
wks = sh.sheet1

# Dropdown validation (list of values)
wks.set_data_validation(
    'B2:B10',
    'ONE_OF_LIST',
    ['Option 1', 'Option 2', 'Option 3'],
    strict=True,
    show_custom_ui=True
)

# Number range validation
wks.set_data_validation(
    'C2:C10',
    'NUMBER_BETWEEN',
    [1, 100],
    input_message='Enter a number between 1 and 100'
)

# Date validation
from datetime import date
wks.set_data_validation(
    'D2:D10',
    'DATE_AFTER',
    [date.today()],
    strict=True
)

# Email validation (regex pattern)
wks.set_data_validation(
    'E2:E10',
    'CUSTOM_FORMULA',
    ['=ISEMAIL(E2)'],
    input_message='Enter a valid email address'
)

# Checkbox validation
wks.set_data_validation(
    'F2:F10',
    'CHECKBOX',
    [True, False]
)

# Highlight cells based on value
red_format = {
    'backgroundColor': {'red': 1.0, 'green': 0.8, 'blue': 0.8}
}

# Highlight values greater than 100
wks.add_conditional_formatting(
    'A1:A20',
    'NUMBER_GREATER',
    [100],
    format=red_format
)

# Color scale formatting
wks.add_conditional_formatting(
    'B1:B20',
    'COLOR_SCALE',
    [],
    format={
        'colorScale': {
            'minpoint': {'color': {'red': 1, 'green': 1, 'blue': 1}},
            'maxpoint': {'color': {'red': 0, 'green': 1, 'blue': 0}}
        }
    }
)

# Text contains formatting
yellow_format = {
    'backgroundColor': {'red': 1.0, 'green': 1.0, 'blue': 0.8}
}
wks.add_conditional_formatting(
    'C1:C20',
    'TEXT_CONTAINS',
    ['error'],
    format=yellow_format
)

# Custom formula formatting
wks.add_conditional_formatting(
    'D1:D20',
    'CUSTOM_FORMULA',
    ['=D1>AVERAGE($D$1:$D$20)'],
    format={'textFormat': {'bold': True}}
)

# Create formatted header row
header_format = {
    'backgroundColor': {'red': 0.2, 'green': 0.4, 'blue': 0.8},
    'textFormat': {
        'bold': True,
        'fontSize': 12,
        'foregroundColor': {'red': 1, 'green': 1, 'blue': 1}
    },
    'horizontalAlignment': 'CENTER'
}

wks.apply_format('A1:E1', header_format)

# Number formatting for currency
currency_format = {
    'numberFormat': {
        'type': 'CURRENCY',
        'pattern': '$#,##0.00'
    }
}
wks.apply_format('C2:C100', currency_format)

# Date formatting
date_format = {
    'numberFormat': {
        'type': 'DATE',
        'pattern': 'mm/dd/yyyy'
    }
}
wks.apply_format('D2:D100', date_format)

# Percentage formatting
percent_format = {
    'numberFormat': {
        'type': 'PERCENT',
        'pattern': '0.00%'
    }
}
wks.apply_format('E2:E100', percent_format)

# Add borders to range
border_format = {
    'borders': {
        'top': {'style': 'SOLID', 'width': 1},
        'bottom': {'style': 'SOLID', 'width': 1},
        'left': {'style': 'SOLID', 'width': 1},
        'right': {'style': 'SOLID', 'width': 1}
    }
}
wks.apply_format('A1:E10', border_format)

# Merge cells for title
wks.merge_cells('A1', 'E1', merge_type='MERGE_ALL')
wks.update_value('A1', 'Report Title')

# Apply title formatting
title_format = {
    'textFormat': {
        'bold': True,
        'fontSize': 16
    },
    'horizontalAlignment': 'CENTER'
}
wks.apply_format('A1', title_format)

# Create named range
wks.update_values('A1:C3', [['Name', 'Score', 'Grade'], ['Alice', 95, 'A'], ['Bob', 87, 'B']])
wks.create_named_range('student_data', 'A1', 'C3')

# Use named range in formula
wks.update_value('E1', '=AVERAGE(student_data)')

# Get named range
student_range = wks.get_named_range('student_data')
print(f"Range: {student_range.range}")

# Create protected range
protected_id = wks.create_protected_range(
    'A1', 'C1',  # Protect header row
    description='Header row - do not edit',
    editors=['admin@example.com']
)

# List all protected ranges
protected_ranges = wks.get_protected_ranges()
for prange in protected_ranges:
    print(f"Protected: {prange.range}")

# Set up basic filter on data
wks.set_basic_filter(start_row=1, end_row=100, start_col=1, end_col=5)

# Sort data by column
wks.sort_range('A2', 'E100', basecolumnindex=2, sortorder='DESCENDING')  # Sort by column C

# Clear filter when done
wks.clear_basic_filter()

class ValidationConditionType:
    NUMBER_GREATER = 'NUMBER_GREATER'
    NUMBER_GREATER_THAN_EQ = 'NUMBER_GREATER_THAN_EQ'
    NUMBER_LESS = 'NUMBER_LESS'
    NUMBER_LESS_THAN_EQ = 'NUMBER_LESS_THAN_EQ'
    NUMBER_EQ = 'NUMBER_EQ'
    NUMBER_NOT_EQ = 'NUMBER_NOT_EQ'
    NUMBER_BETWEEN = 'NUMBER_BETWEEN'
    NUMBER_NOT_BETWEEN = 'NUMBER_NOT_BETWEEN'
    TEXT_CONTAINS = 'TEXT_CONTAINS'
    TEXT_NOT_CONTAINS = 'TEXT_NOT_CONTAINS'
    TEXT_STARTS_WITH = 'TEXT_STARTS_WITH'
    TEXT_ENDS_WITH = 'TEXT_ENDS_WITH'
    TEXT_EQ = 'TEXT_EQ'
    TEXT_IS_EMAIL = 'TEXT_IS_EMAIL'
    TEXT_IS_URL = 'TEXT_IS_URL'
    DATE_EQ = 'DATE_EQ'
    DATE_BEFORE = 'DATE_BEFORE'
    DATE_AFTER = 'DATE_AFTER'
    DATE_ON_OR_BEFORE = 'DATE_ON_OR_BEFORE'
    DATE_ON_OR_AFTER = 'DATE_ON_OR_AFTER'
    DATE_BETWEEN = 'DATE_BETWEEN'
    ONE_OF_RANGE = 'ONE_OF_RANGE'
    ONE_OF_LIST = 'ONE_OF_LIST'
    BLANK = 'BLANK'
    NOT_BLANK = 'NOT_BLANK'
    CUSTOM_FORMULA = 'CUSTOM_FORMULA'
    CHECKBOX = 'CHECKBOX'

class MergeType:
    MERGE_ALL = 'MERGE_ALL'
    MERGE_COLUMNS = 'MERGE_COLUMNS' 
    MERGE_ROWS = 'MERGE_ROWS'