Metadata-Version: 2.1
Name: sheetFeeder
Version: 0.1.2
Summary: Python tool set for interacting with Google Sheets data.
Home-page: https://github.com/dwhodges2/sheetFeeder
Author: David W Hodges
Author-email: dwhodges@gmail.com
License: MIT
Keywords: googlesheets api tabular data
Platform: UNKNOWN
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.4.0
Description-Content-Type: text/markdown
Requires-Dist: requests
Requires-Dist: google-api-python-client
Requires-Dist: oauth2client


# sheetFeeder
_(Formerly googlesheet_tools, GoogleSheetAPITools)_

Basic Python functions for operations on a Google Sheet. See https://developers.google.com/sheets/api/quickstart/python for more setup details. See API documentation: https://developers.google.com/sheets/api/reference/rest.
This module has been heavily used in Columbia University Libraries' archival data migrations and other activites; a case study involving its use can be found in https://journal.code4lib.org/articles/14871.

## Requirements

* Python 3.4 or higher.
* A Google Apps account.
* Generated `token.json` and `credentials.json` files in project folder (see quickstart instructions below).
* Python packages:
  * `requests`
  * `googleapiclient`
  * `oauth2client`
  * `httplib2`

## Setup

### Python dependencies

It is recommended to create a virtual Python environment (optional but recommended), to isolate project dependencies. See https://docs.python.org/3/library/venv.html.

```
python3 -m venv sfvenv
```

Activate the virtual environment to which dependencies will be added:

```
source sfvenv/bin/activate
```

(To deactivate later use command `deactivate`.)

Dependencies to install into environment:

* `pip install requests`
* `pip install --upgrade google-api-python-client`
* `pip install oauth2client`
* `pip install lxml==4.3.4`

(Note, can use current or other version of `lxml`; 4.3.4 is used for compatibility with Python 3.4.)

### Enable Google Sheets API 

* Go to https://developers.google.com/sheets/api/quickstart/python. Make sure you are signed in as the Google identity you want to enable API access for. 
* Click "Enable the Google Sheets API" button. Download the API credentials as `credentials.json` and put it in the same folder with sheetFeeder.
* Authenticate and grant access.
  * Run `quickstart.py`.
  * The first time you use the API you will be asked to select the Google identity to use (if more than one) and to verify access. Note that you may see a warning that the application is not verified by Google and could pose a security risk. You can go to the "advanced" option and proceed with the QuickStart authentication process from there.
  * Click through to grant read/write permission to your Google Sheets account. If successful you will see a message saying "The authentication flow has completed."

You should now have a file called `token.json` created in your directory. If you want to redo authentication, delete the file and run the script again. Any instance of sheetFeeder will need appropriate `credentials.json` and `token.json` files adjacent.


## Using sheetFeeder

### Classes and Methods

The core class is dataSheet(id,range). Define a dataSheet to operate on using the id string of a Google Sheet (the long string between "https://docs.google.com/spreadsheets/d/" and "/edit#gid=0" or the like), and a range including a tab name. Example:

```python
from sheetFeeder import dataSheet

my_sheet = dataSheet('1YzM1diaFchenQnchemgogyU2menGxv5Gme','Sheet1!A:Z')
```

This enables several methods on the dataSheet class:

* `.clear()`: Clears content of range.
* `.getData()`: Returns data in form of a list of rows.
* `.getDataColumns()`: Returns data in form of a list of columns.
* `.appendData(some_data)`: Appends rows of data to sheet.  Note: the range is only used to identify a table; values will be appended as rows at the end of table, not at end of range.
* `.lookup(search_str,col_search,col_result)`: Provide string to match, the column to match in, and col(s) to return. The col_result can either be an integer or a list of integers, e.g., col_search=0, col_result=[1,2], which will return an array of results. Will return multiple matches in a list.
* `.importCSV(csv_path,delim=)`: Import a CSV file into a designated sheet range, overwriting what is there. Delimeter is comma by default, but can be any character, e.g., pipe ('|').
* `.matchingRows(self,queries,regex=True,operator='or')`: Return a list of rows for which at least one queried column matches regex query. Assumes the first row contains heads. Queries are pairs of column heads and matching strings, e.g., [['ID','123'],['Author','Yeats']]. They are regex by default and can be joined by either 'and' or 'or' logic.

#### Additional Subclasses

* `.id`: Returns id part of dataSheet 
* `.range`: Returns range part of dataSheet 
* `.initInfo`: Returns dictionary of metadata about sheet (all tabs, not just the one defined in 2nd arg of dataSheet).
* `.initTabs`: Returns a list of names of tabs in spreadsheet.
* `.url`: Returns public url of sheet of form https://docs.google.com/spreadsheets/d/{sheet_id}/edit#gid={tab_id}


### Sample Commands

* `my_sheet.getData()`
  * Result: [['head1', 'head2'],['a', 'b'],['one','two']]
* `my_sheet.appendData[[5,"e", 'xx'],[6,"f"],[7,"g"]]`
  * Result: add some rows.
* `my_sheet.lookup('Smith',2,[3,4])`
  * Result: Return values of columns 3 and 4 for any row where column 2 equals "Smith".
* `my_sheet.matchingRows([['ID', '123'], ['Title', '.*Humph.*']])`
  * Result: Return all rows where ID = 123 *or* Title matches the regex expression `.*Humph.*`. 
* `my_sheet.matchingRows([['ID', '123'], ['Title', '.*Humph.*']], operator='and')`
  * Result: Return all rows where ID = 123 *and* Title matches the regex expression `.*Humph.*`. 
* `my_sheet.clear()`: Empty the contents of dataSheet.
* `my_sheet.importCSV(my_file,delim='|')`: Import contents of pipe-delimited text file into dataSheet.



## Notes

This is a work in progress. Comments/suggestions as well as forking very welcome. 

TODO: Compile into package and register with PyPi for easier installation! 

