Repository: frictionlessdata/tabulator-py (python)

Issue:
Add Google Sheet parser
# Description

We would like a Google Sheets parser. This is quite high priority, as we'd like it for direct use in OpenSpending.

Some google sheets are "public on the web", and some need credentials. first implementation can assume public on the web and completely bypass credentials.


Pull request:
Added Google Spreadsheet support
- fixes #117

---

@akariv
@pwalsh 

If I've got it right (and you know any google docs are the best in the world=) that any usage of Sheets API should include API Key:
- https://developers.google.com/sheets/quickstart/python
- https://developers.google.com/sheets/guides/authorizing

It looks like there is no such thing as just load gsheet as anonymous thru API (there was `gdata` API to do so but now it doesn't work). Correct me if I'm wrong. 

For public sheets end-users getting an API Key could be a problem and we cant provide ours because of the limits. So for now as a POC I just read csv export from any publicly available spreadsheet (no user actions like public to the web are needed for it - only link if access set to anyone).

Thoughts, ideas?)

Patch:
diff --git a/README.md b/README.md
index cc6d0e1..9b77439 100644
--- a/README.md
+++ b/README.md
@@ -12,7 +12,7 @@ Consistent interface for stream reading and writing tabular data (csv/xls/json/e
 
 ## Features
 
-- supports various formats: csv/tsv/xls/xlsx/json/ndjson/ods/native/etc
+- supports various formats: csv/tsv/xls/xlsx/json/ndjson/ods/gsheet/native/etc
 - reads data from variables, filesystem or Internet
 - streams data instead of using a lot of memory
 - processes data via simple user processors
diff --git a/tabulator/config.py b/tabulator/config.py
index 81ac008..ee6fd9b 100644
--- a/tabulator/config.py
+++ b/tabulator/config.py
@@ -16,15 +16,17 @@ LOADERS = {
     'file': 'tabulator.loaders.file.FileLoader',
     'ftp': 'tabulator.loaders.web.WebLoader',
     'ftps': 'tabulator.loaders.web.WebLoader',
+    'gsheet': None,
     'http': 'tabulator.loaders.web.WebLoader',
     'https': 'tabulator.loaders.web.WebLoader',
-    'native': 'tabulator.loaders.native.NativeLoader',
+    'native': None,
     'stream': 'tabulator.loaders.stream.StreamLoader',
     'text': 'tabulator.loaders.text.TextLoader',
 }
 
 PARSERS = {
     'csv': 'tabulator.parsers.csv.CSVParser',
+    'gsheet': 'tabulator.parsers.gsheet.GsheetParser',
     'json': 'tabulator.parsers.json.JSONParser',
     'jsonl': 'tabulator.parsers.ndjson.NDJSONParser',
     'ndjson': 'tabulator.parsers.ndjson.NDJSONParser',
diff --git a/tabulator/helpers.py b/tabulator/helpers.py
index b434c43..6c3a4a6 100644
--- a/tabulator/helpers.py
+++ b/tabulator/helpers.py
@@ -29,6 +29,9 @@ def detect_scheme(source):
     if hasattr(source, 'read'):
         scheme = 'stream'
     elif isinstance(source, six.string_types):
+        if 'docs.google.com/spreadsheets' in source:
+            if 'export' not in source:
+                return 'gsheet'
         match = re.search(r'^([a-zA-Z]{2,}):\/{2}', source)
         if not match:
             return None
@@ -47,6 +50,9 @@ def detect_format(source):
     if hasattr(source, 'read'):
         format = ''
     elif isinstance(source, six.string_types):
+        if 'docs.google.com/spreadsheets' in source:
+            if 'export' not in source:
+                return 'gsheet'
         parsed_source = urlparse(source)
         path = parsed_source.path or parsed_source.netloc
         format = os.path.splitext(path)[1]
diff --git a/tabulator/loaders/native.py b/tabulator/loaders/native.py
deleted file mode 100644
index 6ec73cd..0000000
--- a/tabulator/loaders/native.py
+++ /dev/null
@@ -1,21 +0,0 @@
-# -*- coding: utf-8 -*-
-from __future__ import division
-from __future__ import print_function
-from __future__ import absolute_import
-from __future__ import unicode_literals
-
-from . import api
-
-
-# Module API
-
-class NativeLoader(api.Loader):
-    """Null loader to pass python native lists.
-    """
-
-    # Public
-
-    options = []
-
-    def load(self, source, encoding, mode):
-        pass
diff --git a/tabulator/parsers/gsheet.py b/tabulator/parsers/gsheet.py
new file mode 100644
index 0000000..9438b83
--- /dev/null
+++ b/tabulator/parsers/gsheet.py
@@ -0,0 +1,51 @@
+# -*- coding: utf-8 -*-
+from __future__ import division
+from __future__ import print_function
+from __future__ import absolute_import
+from __future__ import unicode_literals
+
+import re
+from ..stream import Stream
+from . import api
+
+
+# Module API
+
+class GsheetParser(api.Parser):
+    """Parser to parse Google Spreadsheets.
+    """
+
+    # Public
+
+    options = []
+
+    def __init__(self):
+        self.__stream = None
+
+    @property
+    def closed(self):
+        return self.__stream is None or self.__stream.closed
+
+    def open(self, source, encoding, loader):
+        self.close()
+        url = 'https://docs.google.com/spreadsheets/d/%s/export?format=csv&id=%s&gid=%s'
+        match = re.search(r'.*/d/(?P<key>[^/]+)/.*(?:gid=(?P<gid>\d+))?.*', source)
+        key, gid = '', ''
+        if match:
+            key = match.group('key')
+            gid = match.group('gid') or '0'
+        url = url % (key, key, gid)
+        self.__stream = Stream(url, format='csv', encoding=encoding).open()
+        self.__extended_rows = self.__stream.iter(extended=True)
+
+    def close(self):
+        if not self.closed:
+            self.__stream.close()
+
+    def reset(self):
+        self.__stream.reset()
+        self.__extended_rows = self.__iter_extended_rows()
+
+    @property
+    def extended_rows(self):
+        return self.__extended_rows
diff --git a/tabulator/parsers/ndjson.py b/tabulator/parsers/ndjson.py
index d7731b2..d5c4dfd 100644
--- a/tabulator/parsers/ndjson.py
+++ b/tabulator/parsers/ndjson.py
@@ -21,8 +21,7 @@ class NDJSONParser(api.Parser):
 
     # Public
 
-    options = [
-    ]
+    options = []
 
     def __init__(self, **options):
         self.__options = options
diff --git a/tabulator/stream.py b/tabulator/stream.py
index 616ee6f..55f8f80 100644
--- a/tabulator/stream.py
+++ b/tabulator/stream.py
@@ -32,6 +32,7 @@ class Stream(object):
                 - file (default)
                 - ftp
                 - ftps
+                - gsheet
                 - http
                 - https
                 - native
@@ -48,6 +49,7 @@ class Stream(object):
                     - quotechar
                     - quoting
                     - skipinitialspace
+                - gsheet
                 - json
                   options:
                     - prefix
@@ -154,6 +156,7 @@ class Stream(object):
         options = copy(self.__options)
 
         # Initiate loader
+        self.__loader = None
         if scheme is None:
             scheme = helpers.detect_scheme(self.__source)
             if not scheme:
@@ -161,9 +164,11 @@ class Stream(object):
         if scheme not in config.LOADERS:
             message = 'Scheme "%s" is not supported' % scheme
             raise exceptions.SchemeError(message)
-        loader_class = helpers.import_attribute(config.LOADERS[scheme])
-        loader_options = helpers.extract_options(options, loader_class.options)
-        self.__loader = loader_class(**loader_options)
+        loader_path = config.LOADERS[scheme]
+        if loader_path:
+            loader_class = helpers.import_attribute(loader_path)
+            loader_options = helpers.extract_options(options, loader_class.options)
+            self.__loader = loader_class(**loader_options)
 
         # Initiate parser
         if format is None:


Test patch:
diff --git a/tests/test_stream.py b/tests/test_stream.py
index 501fc37..a5800cf 100644
--- a/tests/test_stream.py
+++ b/tests/test_stream.py
@@ -8,7 +8,7 @@ import pytest
 from tabulator import Stream, exceptions
 
 
-# Tests [formats]
+# Tests [format:csv]
 
 def test_stream_csv_excel():
     source = 'value1,value2\nvalue3,value4'
@@ -38,6 +38,26 @@ def test_stream_csv_escaping():
         ]
 
 
+# Tests [format:gsheet]
+
+def test_stream_gsheet():
+    source = 'https://docs.google.com/spreadsheets/d/1HXCBuWlekSd359yvHimvMJiD2CC_2og75WlL4EFAcmg/edit?usp=sharing'
+    with Stream(source) as stream:
+        assert stream.read() == [['id', 'name'], ['1', 'english'], ['2', '中国人']]
+
+
+def test_stream_gsheet_with_gid():
+    source = 'https://docs.google.com/spreadsheets/d/1HXCBuWlekSd359yvHimvMJiD2CC_2og75WlL4EFAcmg/edit#gid=1137640675'
+    with Stream(source) as stream:
+        assert stream.read() == [['id', 'name'], ['1', 'english'], ['2', '中国人']]
+
+
+def test_stream_gsheet_bad_url():
+    stream = Stream('https://docs.google.com/spreadsheets/d/bad')
+    with pytest.raises(exceptions.HTTPError) as excinfo:
+        stream.open()
+
+
 def test_stream_csv_doublequote():
     with Stream('data/special/doublequote.csv') as stream:
         for row in  stream: