3fi&w>dZddlmZmZmZmZmZmZddlm Z ddl m Z m Z m Z ddlmZddlmZmZmZddlmZmZGd d e ZGd d eZGd deZGddeZGddeZGddeZGdde ZGddeZGddeZy)z*Tools for reading data from Google Sheets.)AnyDictListOptionalTypeUnion)CallbackManagerForToolRun) BaseModelFieldmodel_validator)SheetsBaseTool)DateTimeRenderOptionMajorDimensionValueRenderOption)validate_range_namevalidate_spreadsheet_idceZdZUdZedZeed<eejdZ eed<ee jdZ e ed <ed d Zeed <ed dZeed<y)ReadBaseSchemaz3Base schema for read operations with common fields.z.The ID of the Google Spreadsheet to read from. descriptionspreadsheet_idz,How values should be rendered in the output.defaultrvalue_render_optionz3How dates, times, and durations should be rendered.date_time_render_optionFzUWhether to convert data to records (list of dictionaries) using first row as headers.convert_to_recordsTzAWhether to automatically convert string numbers to numeric types.numericise_valuesN)__name__ __module__ __qualname____doc__r rstr__annotations__rFORMATTED_VALUErr SERIAL_NUMBERrrboolrp/var/www/auto_recruiter/arenv/lib/python3.12/site-packages/langchain_google_community/sheets/read_sheet_tools.pyrrs=DNC.3!11B.* 5:$22I51 %&  $Wtr)rc eZdZdZdedeeeeffdZde ede e de e fdZ de e ede d e dee e e e ffd Z d e e de e efd Zd e e de e efd Zy) BaseReadToolzBase class for Google Sheets read operations. Provides shared functionality for data processing, value extraction, and record conversion that is common across all read tools. valuereturnct|ts|S|dk(ry|jdd} t|S#t$r" t |cYS#t$r|cYcYSwxYwwxYw)zConvert string values to numbers when possible. Args: value: String value to convert Returns: Converted value or original string. ,) isinstancer#replaceint ValueErrorfloat)selfr- cleaned_values r* _numericisezBaseReadTool._numericiseAsr%%L B; c2.  }% %  ]++    s- 7 A" A A" AA"AA"headersvaluesc R|Dcgc]}tt||c}Scc}w)zConvert 2D array to list of dictionaries. Args: headers: List of column headers values: 2D array of data values Returns: List of dictionaries with headers as keys. )dictzip)r7r:r;rows r* _to_recordszBaseReadTool._to_records[s$4::CS#&':::s$rrc|sgS|rvt|dkDrh|d}|dd}|rJg}|D]1}|Dcgc]}|j|} }|j| 3|j||S|j||S|r:g} |D]1}|Dcgc]}|j|} }| j| 3| S|Scc}wcc}w)aVProcess raw data from Google Sheets. Args: values: 2D array of raw values from Google Sheets convert_to_records: Whether to convert to records format numericise_values: Whether to convert string numbers to numeric types Returns: Processed data as 2D array or records. r rN)lenr9appendr@) r7r;rrr: data_rowsprocessed_rowsr?cell processed_rowprocessed_valuess r* _process_datazBaseReadTool._process_datags I #f+/QiGqr I !#$9CHK$LT%5%5d%;$LM$L"))-89''@@'';; !  7DG HD!1!1$!7 H H '' 6 7$ # %M!Is B; C grid_datac|sgSg}|D]k}|jdgxsgD]P}g}|jdgxsgD]"}|j|j|$|j|Rm|S)aExtract a simple 2D array from ALL GridData segments, preserving order. This method processes ALL grid segments returned by the API, not just the first one. This is critical for preventing data loss when the API returns multiple segments (e.g., filtered reads, paginated responses). Args: grid_data: List of GridData segments from Google Sheets API Returns: Simple 2D array of values from all segments concatenated. rowDatar;)getrC_safe_get_cell_value)r7rJresultgridrow_data row_values cell_datas r*_extract_simple_data_allz%BaseReadTool._extract_simple_data_allsI"$ *D HHY39r *(* !)h!;!ArLI%%d&?&? &JKL j)  * * r)c$|j|S)a.Extract simple 2D array from complex GridData structure. Backward-compatible method name. Now processes ALL segments to prevent data loss. Args: grid_data: GridData from Google Sheets API Returns: 2D array of values from all segments. )rT)r7rJs r*_extract_simple_dataz!BaseReadTool._extract_simple_datas,,Y77r)N)rr r!r"r#rr4r6r9rr=r@r'rIrTrVr(r)r*r,r,:s sC)?4 ;49 ;d4j ;T$Z ;)T#Y)!) ) tDz4:% & )V$t*d3i6 8d4j 8T$s)_ 8r)r,c.eZdZUdZedZeed<y)ReadSheetDataSchemaz&Input schema for `SheetsReadDataTool`.z/A1 notation range to read from the spreadsheet.r range_nameN)rr r!r"r rYr#r$r(r)r*rXrXs0EJr)rXceZdZUdZdZeed<dZeed<eZ e e ed<e jejddd fd ed ed e d edededeedeeeffdZy )SheetsReadDataToolaNTool for reading data from a single range in Google Sheets. Inherits from [`BaseReadTool`][langchain_google_community.sheets.read_sheet_tools.BaseReadTool]. Reads data from a single range with support for various rendering options and data transformations. Tool Output: success (bool): Whether operation succeeded. spreadsheet_id (str): The spreadsheet ID. range (str): The actual range that was read (A1 notation). values (list): Processed data (2D array or list of dictionaries). major_dimension (str): The major dimension ('ROWS' or 'COLUMNS'). render_options (dict): Applied rendering options. processing_options (dict): Applied processing options. ???+ example "Basic Usage" Read data from a range: ```python from langchain_google_community.sheets import SheetsReadDataTool tool = SheetsReadDataTool(api_key="your_api_key") result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "range_name": "A1:E10", } ) print(result["values"]) ``` ??? example "Convert to Records" Convert 2D array to list of dictionaries using first row as headers: ```python result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "range_name": "A1:E10", "convert_to_records": True, } ) # Returns: [{"Name": "Alice", "Age": 25, ...}, ...] ``` ??? example "Custom Rendering Options" Get unformatted values and formulas: ```python result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "range_name": "A1:E10", "value_render_option": "UNFORMATTED_VALUE", } ) ``` Raises: ValueError: If `spreadsheet_id` or `range_name` is invalid. Exception: For API errors or connection issues. sheets_read_datanamea Read data from a single range in Google Sheets with comprehensive rendering options and data transformation capabilities. Supports formatted/unformatted values, record conversion, and numeric processing. Perfect for extracting specific data from single sheet ranges.r args_schemaFTNrrYrrrr run_managerr.c  t|t||j}|jj j |||j |j } | j} | j dg} |j| ||} d|| j d|| | j dd|j |j d||dd } | S#t$r}td ||d }~wwxYw) aRead data from a single range in Google Sheets. Args: spreadsheet_id: ID of the spreadsheet to read from. range_name: A1 notation range (e.g., `'A1:E10'` or `'Sheet1!A1:D5'`). value_render_option: How values should be rendered. date_time_render_option: How dates/times should be rendered. convert_to_records: Convert to list of dictionaries using first row as headers. numericise_values: Convert string numbers to numeric types. run_manager: Optional callback manager. Returns: success (bool): Whether operation succeeded. spreadsheet_id (str): The spreadsheet ID. range (str): The actual range that was read. values (list): Processed data (2D array or list of dicts). major_dimension (str): The major dimension. render_options (dict): Applied rendering options. processing_options (dict): Applied processing options. Raises: ValueError: If `spreadsheet_id` or `range_name` is invalid. Exception: For API errors or connection issues. ) spreadsheetIdrangevalueRenderOptiondateTimeRenderOptionr;TrbmajorDimensionROWS)rr)rr)successrrbr;major_dimensionrender_optionsprocessing_optionszError reading sheet data: N) rr _get_service spreadsheetsr;rMr-executerI Exception)r7rrYrrrrr_servicerequestresponser;processed_datarOerrors r*_runzSheetsReadDataTool._runs$J/ M #N 3  +'')G$$&"0$&9&?&?)@)F)F  (H\\(B/F"//*,=N  "0!gz:(#+<<0@&#I+>+D+D/F/L/L# +=):'F M M8@Au L MsC%C(( D1DD)rr r!r"r]r#r$rrXr^rr rr%rr&r'rr rrrtr(r)r*r[r[sBH#D#" I$7Ki6 2C1R1R . .#("&;?TMTMTM/ TM "6 TM!TM TM78TM c3hTMr)r[cfeZdZUdZedZeeed<ee jdZ e ed<y) BatchReadSheetDataSchemaz+Input schema for `SheetsBatchReadDataTool`.z8List of A1 notation ranges to read from the spreadsheet.rrangesz,The major dimension that results should use.rrhN) rr r!r"r rwrr#r$rrfrhr(r)r*rvrvvs<5NFDI',##B'O^r)rvceZdZUdZdZeed<dZeed<eZ e e ed<e jejej"ddd fd ed eed e d ededededeedeeeffdZy )SheetsBatchReadDataToolaiTool for reading data from multiple ranges in Google Sheets efficiently. Inherits from [`BaseReadTool`][langchain_google_community.sheets.read_sheet_tools.BaseReadTool]. Reads multiple ranges in a single API call, reducing network overhead and improving performance. Tool Output: success (bool): Whether operation succeeded. spreadsheet_id (str): The spreadsheet ID. requested_ranges (list): The ranges that were requested. total_ranges (int): Total number of ranges processed. successful_ranges (int): Number of successfully processed ranges. failed_ranges (int): Number of failed ranges. results (list): List of results for each range with data and error fields. value_render_option (str): Applied value rendering option. date_time_render_option (str): Applied date/time rendering option. major_dimension (str): Applied major dimension. convert_to_records (bool): Whether data was converted to records. numericise_values (bool): Whether values were numericised. ???+ example "Basic Usage" Read multiple ranges in one call: ```python from langchain_google_community.sheets import SheetsBatchReadDataTool tool = SheetsBatchReadDataTool(api_key="your_api_key") result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "ranges": ["A1:C5", "F1:H5", "Sheet2!A1:D10"], } ) for r in result["results"]: print(f"{r['range']}: {len(r['data'])} rows") ``` ??? example "With Record Conversion" Read and convert to dictionaries: ```python result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "ranges": ["Sheet1!A1:D10", "Sheet2!A1:E10"], "convert_to_records": True, } ) ``` Raises: ValueError: If `spreadsheet_id` is invalid or ranges list is empty. Exception: For API errors or connection issues. sheets_batch_read_datar]zRead data from multiple ranges in Google Sheets efficiently using batch API calls. Supports multiple A1 notation ranges, various rendering options, and data transformation. Optimized for extracting data from multiple sheets or ranges simultaneously.rr^FTNrrwrrrhrrr_r.c < t||s td|D] } t| |j} | j j j |||j|j|j} | j} g} d}d}| jdg}t|D]\\}}|jdd|} |jdg}d} |j|||}|d z }| j| ||d ^d |||j|j|j||t|||| d }|S#t$r}d}t|}|d z }Yd}~od}~wwxYw#t$r}td ||d}~wwxYw)a#Read data from multiple ranges in a Google Spreadsheet efficiently. Args: spreadsheet_id: ID of the spreadsheet to read from. ranges: List of A1 notation ranges to read. value_render_option: How values should be rendered. date_time_render_option: How dates/times should be rendered. major_dimension: Major dimension for results. convert_to_records: Convert to list of dictionaries. numericise_values: Convert string numbers to numeric types. run_manager: Optional callback manager. Returns: success (bool): Whether operation succeeded. spreadsheet_id (str): The spreadsheet ID. requested_ranges (list): The ranges that were requested. total_ranges (int): Total number of ranges processed. successful_ranges (int): Number of successfully processed ranges. failed_ranges (int): Number of failed ranges. results (list): List of results for each range. value_render_option (str): Applied value rendering option. date_time_render_option (str): Applied date/time rendering option. major_dimension (str): Applied major dimension. convert_to_records (bool): Whether data was converted to records. numericise_values (bool): Whether values were numericised. Raises: ValueError: If `spreadsheet_id` is invalid or ranges list is empty. Exception: For API errors or connection issues. z$At least one range must be specified)rarwrcrdrer valueRangesrbrange_r;Nr )rbdatarsT) rgrrequested_rangesrrrhrr total_rangessuccessful_ranges failed_rangesresultsz Error batch reading sheet data: )rr5rrkrlr;batchGetr-rmrM enumeraterIrnr#rCrB)r7rrwrrrhrrr_rYrorprqrrr value_rangesi value_ranger;rsrrebatch_metadatas r*rtzSheetsBatchReadDataTool._runsVJ S #N 3 !GHH$ 0 #J/ 0'')G$$&"0!&9&?&?)@)F)F#2#8#8  (HG ! M#<< r:L"+L"9 ;(__WqclC $26'%)%7%7 24E&N&*% !+ .!& 0 "0$*':'@'@+B+H+H#2#8#8&8%6 #F %6!." N" !9!'%)NFE!Q&M'< S>ugFGU R SsCC,E>/EAE> E;E61E>6E;;E>> FFF)rr r!r"r]r#r$rrvr^rr rr%rr&rrfrr'rr rrrtr(r)r*ryrys9v)D#( 4$GXc]+0  :+IxS#X'9>  @9Xd38n5'"#r)rcTeZdZUdZeddZeeed<eddZ e ed <y ) FilteredReadSheetDataSchemaz.Input schema for `SheetsFilteredReadDataTool`..z~List of DataFilters specifying which ranges to read. Each filter selects a range using a1Range, gridRange, or metadata lookup.r data_filtersFzyWhether to include detailed grid data (cell properties, formatting). Note: This can significantly increase response size.rinclude_grid_dataN) rr r!r"r rrrr$rr'r(r)r*rrusB8+0  X,L$'($ Ctr)rceZdZUdZdZeed<dZeed<eZ e e ed<de jejddd fd ed eed ed e dedededeedeeeffdZdedededefdZy )SheetsFilteredReadDataToolaTool for reading data from Google Sheets using DataFilters. [`BaseReadTool`][langchain_google_community.sheets.read_sheet_tools.BaseReadTool]. Uses `getByDataFilter` API to read ranges specified by A1 notation, grid coordinates, or developer metadata. Optionally includes detailed cell formatting. !!! note "Authentication Required" Requires OAuth2 authentication (not API key). !!! warning "Range Selection Only" This tool is for `RANGE SELECTION`, not conditional filtering like `'score > 50'`. For conditional filtering, create a Filter View using Sheets UI or `batchUpdate` API. Tool Output: success (bool): Whether operation succeeded. spreadsheet_id (str): The spreadsheet ID. properties (dict): Spreadsheet-level properties. sheets (list): List of sheets with filtered data containing properties and data segments. ???+ example "Basic Usage with A1 Notation" Read using A1 notation: ```python from langchain_google_community.sheets import SheetsFilteredReadDataTool tool = SheetsFilteredReadDataTool(api_resource=service) result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "data_filters": [{"a1Range": "Sheet1!A1:E10"}], "include_grid_data": True, } ) ``` ??? example "Using Grid Coordinates" Read using grid coordinates: ```python result = tool.run( { "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "data_filters": [ { "gridRange": { "sheetId": 0, "startRowIndex": 0, "endRowIndex": 10, "startColumnIndex": 0, "endColumnIndex": 5, } } ], } ) ``` Raises: ValueError: If `spreadsheet_id` is invalid or `data_filters` is empty. Exception: For API errors, authentication issues, or connection problems. sheets_get_by_data_filterr]a@Read data from Google Sheets using DataFilters (getByDataFilter API). Select ranges using A1 notation, grid coordinates, or developer metadata. Optionally include detailed cell formatting and properties (gridData). Useful for reading multiple ranges or ranges with specific metadata tags. Requires OAuth2 authentication.rr^FTNrrrrrrrr_r.c V t||s td|j} |j|} | |d} | j j || } | j } |j| ||}d|d<|S#t$r}td||d}~wwxYw)aRead data from Google Sheets using DataFilters. Args: spreadsheet_id: ID of the spreadsheet to read from. data_filters: List of DataFilter objects specifying ranges to read. include_grid_data: Include detailed cell formatting. value_render_option: How values should be rendered. date_time_render_option: How dates/times should be rendered. convert_to_records: Convert to list of dictionaries. numericise_values: Convert string numbers to numeric types. run_manager: Optional callback manager. Returns: success (bool): Whether operation succeeded. spreadsheet_id (str): The spreadsheet ID. properties (dict): Spreadsheet-level properties. sheets (list): List of sheets with filtered data. Raises: ValueError: If `spreadsheet_id` is invalid or `data_filters` is empty. Exception: For API errors, authentication issues, or connection problems. z*At least one data filter must be specified) dataFiltersincludeGridData)rabodyTrgz#Error filtered reading sheet data: N) rr5rk_convert_to_dict_listrlgetByDataFilterrm_process_filtered_responsern)r7rrrrrrrr_rodata_filters_dict request_bodyrprqrOrss r*rtzSheetsFilteredReadDataTool._runsF$ V #N 3 !MNN'')G!% : :< H  1#4L **,<<,<=G(H44,.?F !%F9 M VA%IJPU U VsBB B(B##B(rqc |jd|jdigd}|jdgD]}|jdigd}|jdgD]?}|jdr|j|g}|rt|dkDr|d } |dd } |r]g} | D]1} | D cgc]} |j| }} | j |3|dj |j | | |dj |j | | |rNg}|D]1} | D cgc]} |j| }} |j |3|dj ||dj |,|dj gB|dj ||Scc} wcc} w) z5Process the filtered response from Google Sheets API.ra properties)rrsheetsr)rr~r~rLr rN)rMrVrBr9rCr@)r7rqrrrOsheet sheet_inforJ simple_datar:rDrEr?rFrGrrs r*rz5SheetsFilteredReadDataTool._process_filtered_response$s 'll?;",,|R8  \\(B/( 0E#ii b9J #YYvr2 2 ==+"&";";YK"HK)c+.>.B"-a.$/O ,-/N'0ETW0XD1A1A$1G0X 0X . 5 5m DE'v.55 $ 0 0. I'v.55 $ 0 0) D--/N'2ETW0XD1A1A$1G0X 0X . 5 5m DE'v.55nE&v.55kBv&--b1? 2B 8  # #J /Q( 0T /1Y1Ys 2F? 6G )rr r!r"r]r#r$rrr^rr rr%rr&rr=r'rr rrrtrr(r)r*rrsCJ,D#+ *$?Ki> #(1B1R1R . .#("&;?GVGV4jGV GV / GV "6 GV!GV GV78GV c3hGVR44264KO4 4r)rN) r"typingrrrrrrlangchain_core.callbacksr pydanticr r r baserenumsrrrutilsrrrr,rXr[rvryrrrr(r)r*rs099>66  YD@8>@8P.dMdMX ~ |Sl|SH+y+\.(OOr)