a |fq@sdZddlZddlmZmZmZmZmZmZm Z m Z ddl m Z ddl mZddlmZddlmZmZdd lmZmZdd lmZmZdd lmZGd d d ZdS)zX gspread.spreadsheet ~~~~~~~~~~~~~~ This module contains common spreadsheets' models. N)AnyDict GeneratorIterableListMappingOptionalUnion)Response)Cell)WorksheetNotFound) HTTPClient ParamsType)DRIVE_FILES_API_V3_URLSPREADSHEET_DRIVE_URL) ExportFormatfinditem) Worksheetc @seZdZdZeeeeeeffdddZ e edddZ e eddd Z e edd d Z e edd d Ze edddZe edddZe edddZe edddZeeddfdddZedddZeeefedddZeeeeefeddd Zeed!d"d#Zd{eeeeeefed$d%d&Zd|eeeed'd(d)Zd}eeeeed*d+d,Z d~eeeeeeefedd-d.Z!deeeefedd/d0Z"deeed1d2d3Z#e$eed4d5d6Z%deeeeefd1d7d8Z&e$ed9d:d;Z'eee$fed<d=d>Z(de)eed@dAdBZ*eedCdDdEZ+dee$e$ee$edFdGdHZ,de$ee$ee$eeedIdJdKZ-eedLdMdNZ.eee$fedOdPdQZ/e0eedRdSdTZ1deeee)eee)e2dVdWdXZ3e4j5fe4e6dYdZd[Z7eeeeee)ffdd\d]Z8deeeed_d`daZ9ee2dbdcddZ:ee2dbdedfZ;eee<dgdhdiZ=eeddjdkZ>eedCdldmZ?eedndodpZ@eedqdrdsZAe$eedtdudvZBeddwdxZCdddydzZDdS) Spreadsheetz(The class that represents a spreadsheet.) http_client propertiescCs(||_||_|}|j|ddS)Nr)client _propertiesfetch_sheet_metadataupdate)selfrrmetadatarMD:\Projects\storyit_web\backend\venv\Lib\site-packages\gspread/spreadsheet.py__init__szSpreadsheet.__init__)returncCs |jdS)zSpreadsheet ID.idrrrrrr" szSpreadsheet.idcCs |jdS)zSpreadsheet title.titler#r$rrrr%%szSpreadsheet.titlecCs t|jS)zSpreadsheet URL.)rr"r$rrrurl*szSpreadsheet.urlcCsd|jvr||jdS)zSpreadsheet Creation time.Z createdTime)rupdate_drive_metadatar$rrr creationTime/s zSpreadsheet.creationTimecCs*tjdtdd|jvr ||jdS)zSpreadsheet last updated time. Only updated on initialisation. For actual last updated time, use get_lastUpdateTime().zQworksheet.lastUpdateTime is deprecated, please use worksheet.get_lastUpdateTime())category modifiedTime)warningswarnDeprecationWarningrr'r$rrrlastUpdateTime6s zSpreadsheet.lastUpdateTimecCs |jdS)zSpreadsheet timeZonetimeZoner#r$rrrtimezoneCszSpreadsheet.timezonecCs |jdS)zSpreadsheet localelocaler#r$rrrr1HszSpreadsheet.localecCs |dS)z2Shortcut property for getting the first worksheet.r) get_worksheetr$rrrsheet1MszSpreadsheet.sheet1Nccs|EdHdS)N) worksheetsr$rrr__iter__RszSpreadsheet.__iter__cCsd|jjt|j|jS)Nz <{} {} id:{}>)format __class____name__reprr%r"r$rrr__repr__Us zSpreadsheet.__repr__)bodyr!cCs|j|j|S)a Lower-level method that directly calls `spreadsheets/:batchUpdate `_. :param dict body: `Batch Update Request body `_. :returns: `Batch Update Response body `_. :rtype: dict .. versionadded:: 3.0 )r batch_updater"rr;rrrr<\s zSpreadsheet.batch_update)rangeparamsr;r!cCs|j|j|||S)aLower-level method that directly calls `spreadsheets//values:append `_. :param str range: The `A1 notation `_ of a range to search for a logical table of data. Values will be appended after the last row of the table. :param dict params: `Values Append Query parameters `_. :param dict body: `Values Append Request body `_. :returns: `Values Append Response body `_. :rtype: dict .. versionadded:: 3.0 )r values_appendr"rr>r?r;rrrr@gszSpreadsheet.values_append)r>r!cCs|j|j|S)aLower-level method that directly calls `spreadsheets//values:clear `_. :param str range: The `A1 notation `_ of the values to clear. :returns: `Values Clear Response body `_. :rtype: dict .. versionadded:: 3.0 )r values_clearr")rr>rrrrBws zSpreadsheet.values_clear)r?r;r!cCs|j|j||S)aLower-level method that directly calls `spreadsheets//values:batchClear` :param dict params: (optional) `Values Batch Clear Query parameters `_. :param dict body: (optional) `Values Batch Clear request body `_. :rtype: dict )rvalues_batch_clearr")rr?r;rrrrCs zSpreadsheet.values_batch_clear)r>r?r!cCs|jj|j||dS)aLower-level method that directly calls `GET spreadsheets//values/ `_. :param str range: The `A1 notation `_ of the values to retrieve. :param dict params: (optional) `Values Get Query parameters `_. :returns: `Values Get Response body `_. :rtype: dict .. versionadded:: 3.0 r?)r values_getr")rr>r?rrrrEs zSpreadsheet.values_get)rangesr?r!cCs|jj|j||dS)aLower-level method that directly calls `spreadsheets//values:batchGet `_. :param list ranges: List of ranges in the `A1 notation `_ of the values to retrieve. :param dict params: (optional) `Values Batch Get Query parameters `_. :returns: `Values Batch Get Response body `_. :rtype: dict rD)rvalues_batch_getr")rrFr?rrrrGs zSpreadsheet.values_batch_getcCs|jj|j|||dS)ayLower-level method that directly calls `PUT spreadsheets//values/ `_. :param str range: The `A1 notation `_ of the values to update. :param dict params: (optional) `Values Update Query parameters `_. :param dict body: (optional) `Values Update Request body `_. :returns: `Values Update Response body `_. :rtype: dict Example:: sh.values_update( 'Sheet1!A2', params={ 'valueInputOption': 'USER_ENTERED' }, body={ 'values': [[1, 2, 3]] } ) .. versionadded:: 3.0 )r?r;)r values_updater"rArrrrHszSpreadsheet.values_updatecCs|jj|j|dS)a"Lower-level method that directly calls `spreadsheets//values:batchUpdate `_. :param dict body: (optional) `Values Batch Update Request body `_. :returns: `Values Batch Update Response body `_. :rtype: dict )r;)rvalues_batch_updater"r=rrrrIszSpreadsheet.values_batch_update)r?r!cCs|jj|j|dS)zA method stub that directly calls `spreadsheets.get `_.rD)rZspreadsheets_getr"rr?rrr_spreadsheets_getszSpreadsheet._spreadsheets_get)sheet_iddestination_spreadsheet_idr!cCs|j|j||S)zLower-level method that directly calls `spreadsheets.sheets.copyTo `_.)rZspreadsheets_sheets_copy_tor")rrLrMrrr_spreadsheets_sheets_copy_tosz(Spreadsheet._spreadsheets_sheets_copy_tocCs|jj|j|dS)aSimilar to :method spreadsheets_get:`gspread.http_client.spreadsheets_get`, get the spreadsheet form the API but by default **does not get the cells data**. It only retrieve the the metadata from the spreadsheet. :param dict params: (optional) the HTTP params for the GET request. By default sets the parameter ``includeGridData`` to ``false``. :returns: The raw spreadsheet :rtype: dict rD)rrr"rJrrrrs z Spreadsheet.fetch_sheet_metadata)indexr!c CsV|}z$|d|d}t|||j|jWSttfyPtd|Yn0dS)aReturns a worksheet with specified `index`. :param index: An index of a worksheet. Indexes start from zero. :type index: int :returns: an instance of :class:`gspread.worksheet.Worksheet`. :raises: :class:`~gspread.exceptions.WorksheetNotFound`: if can't find the worksheet Example. To get third worksheet of a spreadsheet: >>> sht = client.open('My fancy spreadsheet') >>> worksheet = sht.get_worksheet(2) sheetsrzindex {} not foundN)rrr"rKeyError IndexErrorr r6)rrO sheet_datarrrrr2s zSpreadsheet.get_worksheet)r"r!c s|}z t|Wn.tyB}ztd|WYd}~n d}~00z.tfdd|d}t||d|j|jWSttfyt d Yn0dS)a!Returns a worksheet with specified `worksheet id`. :param id: The id of a worksheet. it can be seen in the url as the value of the parameter 'gid'. :type id: str | int :returns: an instance of :class:`gspread.worksheet.Worksheet`. :raises: :class:`~gspread.exceptions.WorksheetNotFound`: if can't find the worksheet Example. To get the worksheet 123456 of a spreadsheet: >>> sht = client.open('My fancy spreadsheet') >>> worksheet = sht.get_worksheet_by_id(123456) id should be intNcs|ddkSNrsheetIdrxworksheet_id_intrrz1Spreadsheet.get_worksheet_by_id..rPrzid {} not found) rint ValueErrorrrr"r StopIterationrQr r6)rr"rSexitemrrYrget_worksheet_by_ids   zSpreadsheet.get_worksheet_by_idF)exclude_hiddenr!cs4}fdd|dD}|r0dd|D}|S)aReturns a list of all :class:`worksheets ` in a spreadsheet. :param exclude_hidden: (optional) If set to ``True`` will only return visible worksheets. Default is ``False``. :type exclude_hidden: bool :returns: a list of :class:`worksheets `. :rtype: list cs"g|]}t|djjqS)r)rr"r).0sr$rr +sz*Spreadsheet.worksheets..rPcSsg|]}|js|qSr)Z isSheetHidden)rdwrrrrf0r\)r)rrcrSr4rr$rr4s  zSpreadsheet.worksheets)r%r!c sZ|}z.tfdd|d}t||d|j|jWSttfyTtYn0dS)a.Returns a worksheet with specified `title`. :param title: A title of a worksheet. If there're multiple worksheets with the same title, first one will be returned. :type title: str :returns: an instance of :class:`gspread.worksheet.Worksheet`. :raises: WorksheetNotFound: if can't find the worksheet Example. Getting worksheet named 'Annual bonuses' >>> sht = client.open('Sample one') >>> worksheet = sht.worksheet('Annual bonuses') cs|ddkS)Nrr%rrWr%rrr[Hr\z'Spreadsheet.worksheet..rPrN)rrrr"rr_rQr )rr%rSrarrhr worksheet3s zSpreadsheet.worksheet)r%rowscolsrOr!cCsvddd|d||ddiigi}|dur@||ddddd <|j|j|}|d ddd}t|||j|jS) aAdds a new worksheet to a spreadsheet. :param title: A title of a new worksheet. :type title: str :param rows: Number of rows. :type rows: int :param cols: Number of columns. :type cols: int :param index: Position of the sheet. :type index: int :returns: a newly created :class:`worksheets `. requestsZaddSheetrZGRID)ZrowCountZ columnCount)r%Z sheetTypeZgridPropertiesNrrOZreplies)rr<r"r)rr%rjrkrOr;datarrrr add_worksheetOs&zSpreadsheet.add_worksheet)source_sheet_idinsert_sheet_index new_sheet_idnew_sheet_namer!c Cstj|j|j|||||dS)aDuplicates the contents of a sheet. :param int source_sheet_id: The sheet ID to duplicate. :param int insert_sheet_index: (optional) The zero-based index where the new sheet should be inserted. The index of all sheets after this are incremented. :param int new_sheet_id: (optional) The ID of the new sheet. If not set, an ID is chosen. If set, the ID must not conflict with any existing sheet ID. If set, it must be non-negative. :param str new_sheet_name: (optional) The name of the new sheet. If empty, a new name is chosen for you. :returns: a newly created :class:`gspread.worksheet.Worksheet` .. versionadded:: 3.1 )rprqrr)rZ _duplicaterr")rrorprqrrrrrduplicate_sheet{szSpreadsheet.duplicate_sheet)rir!cCs$ddd|jiigi}|j|j|S)zDeletes a worksheet from a spreadsheet. :param worksheet: The worksheet to be deleted. :type worksheet: :class:`~gspread.worksheet.Worksheet` rl deleteSheetrV)r"rr<)rrir;rrr del_worksheetszSpreadsheet.del_worksheet) worksheet_idr!c Cs^z t|}Wn.ty:}ztd|WYd}~n d}~00ddd|iigi}|j|j|S)z+ Deletes a Worksheet by id rTNrlrtrV)r]r^rr<r")rrvrZr`r;rrrdel_worksheet_by_ids   zSpreadsheet.del_worksheet_by_id)worksheets_in_desired_orderr!cCsri}t|D]\}}|||j<q |D]"}|j|vr8q(|d7}|||j<q(ddd|Di}|j|j|S)aUpdates the ``index`` property of each Worksheet to reflect its index in the provided sequence of Worksheets. :param worksheets_in_desired_order: Iterable of Worksheet objects in desired order. Note: If you omit some of the Spreadsheet's existing Worksheet objects from the provided sequence, those Worksheets will be appended to the end of the sequence in the order that they appear in the list returned by :meth:`gspread.spreadsheet.Spreadsheet.worksheets`. .. versionadded:: 3.4 r rlcSs$g|]\}}d||dddiqS)ZupdateSheetProperties)rVrOrOrfieldsr)rdkeyvalrrrrfs z2Spreadsheet.reorder_worksheets..) enumerater"r4itemsrr<)rrxZidx_mapidxrgr;rrrreorder_worksheetss     zSpreadsheet.reorder_worksheetsT) email_address perm_typerolenotify email_message with_linkr!c Cs|jj|j||||||dS)aShare the spreadsheet with other accounts. :param email_address: user or group e-mail address, domain name or None for 'anyone' type. :type email_address: str, None :param perm_type: The account type. Allowed values are: ``user``, ``group``, ``domain``, ``anyone``. :type perm_type: str :param role: The primary role for this user. Allowed values are: ``owner``, ``writer``, ``reader``. :type role: str :param notify: (optional) Whether to send an email to the target user/domain. :type notify: bool :param email_message: (optional) The email to be sent if notify=True :type email_message: str :param with_link: (optional) Whether the link is required for this permission :type with_link: bool Example:: # Give Otto a write permission on this spreadsheet sh.share('otto@example.com', perm_type='user', role='writer') # Give Otto's family a read permission on this spreadsheet sh.share('otto-familly@example.com', perm_type='group', role='reader') )rrrrrr)rZinsert_permissionr")rrrrrrrrrrshares$zSpreadsheet.share)r6r!cCs|j|j|S)aExport the spreadsheet in the given format. :param str file_id: A key of a spreadsheet to export :param format: The format of the resulting file. Possible values are: ``ExportFormat.PDF``, ``ExportFormat.EXCEL``, ``ExportFormat.CSV``, ``ExportFormat.OPEN_OFFICE_SHEET``, ``ExportFormat.TSV``, and ``ExportFormat.ZIPPED_HTML``. See `ExportFormat`_ in the Drive API. Default value is ``ExportFormat.PDF``. :type format: :class:`~gspread.utils.ExportFormat` :returns bytes: The content of the exported file. .. _ExportFormat: https://developers.google.com/drive/api/guides/ref-export-formats )rexportr")rr6rrrr szSpreadsheet.exportcCs|j|jS)z$Lists the spreadsheet's permissions.)rlist_permissionsr"r$rrrr"szSpreadsheet.list_permissionsany)valuerr!csR|j|j}dvrdndfdd|D}|D]}|j|j|q8|S)aRemove permissions from a user or domain. :param value: User or domain to remove permissions from :type value: str :param role: (optional) Permission to remove. Defaults to all permissions. :type role: str Example:: # Remove Otto's write permission for this spreadsheet sh.remove_permissions('otto@example.com', role='writer') # Remove all Otto's permissions for this spreadsheet sh.remove_permissions('otto@example.com') @Z emailAddressdomaincs:g|]2}|kr|dks*dkrt|dqS)rrr")getstr)rdpr{rrrrrf;s"z2Spreadsheet.remove_permissions..)rrr"Zremove_permission)rrrZpermission_listZfiltered_id_list permission_idrrrremove_permissions&szSpreadsheet.remove_permissions)rr!cCs,dt|j|}ddd}|jjd||dS)aTransfer the ownership of this file to a new user. It is necessary to first create the permission with the new owner's email address, get the permission ID then use this method to transfer the ownership. .. note:: You can list all permissions using :meth:`gspread.spreadsheet.Spreadsheet.list_permissions`. .. warning:: You can only transfer ownership to a new user, you cannot transfer ownership to a group or a domain email address. {}/{}/permissions/{}writerT)rZ pendingOwnerpatch)jsonr6rr"rrequest)rrr&payloadrrrtransfer_ownershipFszSpreadsheet.transfer_ownershipcCs4dt|j|}ddi}ddi}|jjd|||dS)zAccept the pending ownership request on that file. It is necessary to edit the permission with the pending ownership. .. note:: You can only accept ownership transfer for the user currently being used. rrownerZtransferOwnershipTr)rr?r)rrr&rr?rrraccept_ownershipbs zSpreadsheet.accept_ownership) named_ranger!cCs |j|S)zreturn a list of :class:`gspread.cell.Cell` objects from the specified named range. :param named_range: A string with a named range value to fetch. :type named_range: str )r3r>)rrrrrr|s zSpreadsheet.named_rangecCs|jddiddgS)z%Lists the spreadsheet's named ranges.rzZ namedRangesrD)rrr$rrrlist_named_rangesszSpreadsheet.list_named_rangescCs0ddd|iddigi}||}||jd<|S)zIRenames the spreadsheet. :param str title: A new title. rlupdateSpreadsheetPropertiesr%ryr<r)rr%r;resrrr update_titles  zSpreadsheet.update_title)r0r!cCs0ddd|iddigi}||}||jd<|S)zUpdates the current spreadsheet timezone. Can be any timezone in CLDR format such as "America/New_York" or a custom time zone such as GMT-07:00. rlrr/ryr)rr0r;rrrrupdate_timezones  zSpreadsheet.update_timezone)r1r!cCs0ddd|iddigi}||}||jd<|S)aUpdate the locale of the spreadsheet. Can be any of the ISO 639-1 language codes, such as: de, fr, en, ... Or an ISO 639-2 if no ISO 639-1 exists. Or a combination of the ISO language code and country code, such as en_US, de_CH, fr_FR, ... .. note:: Note: when updating this field, not all locales/languages are supported. rlrr1ryr)rr1r;rrrr update_locales   zSpreadsheet.update_locale)sheetidr!csX|jddidd}ztfdd|}Wn tyJtdYn0|dgS) z.Lists the spreadsheet's protected named rangesrzz(sheets.properties,sheets.protectedRangesrDrPcs|ddkSrUr)sheetrrrr[r\z3Spreadsheet.list_protected_ranges..zworksheet id {} not foundZprotectedRanges)rrr_r r6r)rrrPrrrrlist_protected_rangess  z!Spreadsheet.list_protected_rangescCs|j|j}|dS)z3Get the lastUpdateTime metadata from the Drive API.r*)rget_file_drive_metadatar")rrrrrget_lastUpdateTimeszSpreadsheet.get_lastUpdateTimecCs"|j|jd}|j|dS)zhFetches the drive metadata from the Drive API and updates the cached values in _properties dict.r"N)rrrr)rZdrive_metadatarrrr'sz!Spreadsheet.update_drive_metadata)NN)N)N)NN)N)N)N)F)N)NNN)TNF)r)Er8 __module__ __qualname____doc__rrrr rr propertyr"r%r&r(r.r0r1rr3rr5r:rr<rr@rBrrCrErrGrHrIrKr]rNrr2rbboolr4rirnrsrurwrrr rrZPDFbytesrrrrrr rrrrrrrr'rrrrrs               / $  * ."  r)rr+typingrrrrrrrr rlr cellr exceptionsr rrrurlsrrutilsrrrirrrrrrs(