We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
For self-hosted instance, Google sheet connection test failed, but can't find any logs, how to trouble shooting such cases
![image](https://github.com/getredash/redash/assets/39286702/5b480d44-b38d-4035-a68e-49a5bc15220d)

![image](https://github.com/getredash/redash/assets/39286702/6c7a4e14-a7f7-4335-84e2-67eead78e260)

When test connections timeout, can't find any logs on worker/scheduler/server docker logs.
even make sure the log level is debug, can't find any related logs. how to trouble shooting this

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `redash/query_runner/google_spreadsheets.py`
Content:
```
1 import logging
2 import re
3 from base64 import b64decode
4 
5 from dateutil import parser
6 from requests import Session
7 from xlsxwriter.utility import xl_col_to_name
8 
9 from redash.query_runner import (
10     TYPE_BOOLEAN,
11     TYPE_DATETIME,
12     TYPE_FLOAT,
13     TYPE_INTEGER,
14     TYPE_STRING,
15     BaseQueryRunner,
16     guess_type,
17     register,
18 )
19 from redash.utils import json_dumps, json_loads
20 
21 logger = logging.getLogger(__name__)
22 
23 try:
24     import google.auth
25     import gspread
26     from google.oauth2.service_account import Credentials
27     from gspread.exceptions import APIError
28     from gspread.exceptions import WorksheetNotFound as GSWorksheetNotFound
29 
30     enabled = True
31 except ImportError:
32     enabled = False
33 
34 
35 def _load_key(filename):
36     with open(filename, "rb") as f:
37         return json_loads(f.read())
38 
39 
40 def _get_columns_and_column_names(row):
41     column_names = []
42     columns = []
43     duplicate_counter = 1
44 
45     for i, column_name in enumerate(row):
46         if not column_name:
47             column_name = "column_{}".format(xl_col_to_name(i))
48 
49         if column_name in column_names:
50             column_name = "{}{}".format(column_name, duplicate_counter)
51             duplicate_counter += 1
52 
53         column_names.append(column_name)
54         columns.append({"name": column_name, "friendly_name": column_name, "type": TYPE_STRING})
55 
56     return columns, column_names
57 
58 
59 def _value_eval_list(row_values, col_types):
60     value_list = []
61     raw_values = zip(col_types, row_values)
62     for typ, rval in raw_values:
63         try:
64             if rval is None or rval == "":
65                 val = None
66             elif typ == TYPE_BOOLEAN:
67                 val = True if str(rval).lower() == "true" else False
68             elif typ == TYPE_DATETIME:
69                 val = parser.parse(rval)
70             elif typ == TYPE_FLOAT:
71                 val = float(rval)
72             elif typ == TYPE_INTEGER:
73                 val = int(rval)
74             else:
75                 # for TYPE_STRING and default
76                 val = str(rval)
77             value_list.append(val)
78         except (ValueError, OverflowError):
79             value_list.append(rval)
80     return value_list
81 
82 
83 HEADER_INDEX = 0
84 
85 
86 class WorksheetNotFoundError(Exception):
87     def __init__(self, worksheet_num, worksheet_count):
88         message = "Worksheet number {} not found. Spreadsheet has {} worksheets. Note that the worksheet count is zero based.".format(
89             worksheet_num, worksheet_count
90         )
91         super(WorksheetNotFoundError, self).__init__(message)
92 
93 
94 class WorksheetNotFoundByTitleError(Exception):
95     def __init__(self, worksheet_title):
96         message = "Worksheet title '{}' not found.".format(worksheet_title)
97         super(WorksheetNotFoundByTitleError, self).__init__(message)
98 
99 
100 def parse_query(query):
101     values = query.split("|")
102     key = values[0]  # key of the spreadsheet
103     worksheet_num_or_title = 0  # A default value for when a number of inputs is invalid
104     if len(values) == 2:
105         s = values[1].strip()
106         if len(s) > 0:
107             if re.match(r"^\"(.*?)\"$", s):
108                 # A string quoted by " means a title of worksheet
109                 worksheet_num_or_title = s[1:-1]
110             else:
111                 # if spreadsheet contains more than one worksheet - this is the number of it
112                 worksheet_num_or_title = int(s)
113 
114     return key, worksheet_num_or_title
115 
116 
117 def parse_worksheet(worksheet):
118     if not worksheet:
119         return {"columns": [], "rows": []}
120 
121     columns, column_names = _get_columns_and_column_names(worksheet[HEADER_INDEX])
122 
123     if len(worksheet) > 1:
124         for j, value in enumerate(worksheet[HEADER_INDEX + 1]):
125             columns[j]["type"] = guess_type(value)
126 
127     column_types = [c["type"] for c in columns]
128     rows = [dict(zip(column_names, _value_eval_list(row, column_types))) for row in worksheet[HEADER_INDEX + 1 :]]
129     data = {"columns": columns, "rows": rows}
130 
131     return data
132 
133 
134 def parse_spreadsheet(spreadsheet, worksheet_num_or_title):
135     worksheet = None
136     if isinstance(worksheet_num_or_title, int):
137         worksheet = spreadsheet.get_worksheet_by_index(worksheet_num_or_title)
138         if worksheet is None:
139             worksheet_count = len(spreadsheet.worksheets())
140             raise WorksheetNotFoundError(worksheet_num_or_title, worksheet_count)
141     elif isinstance(worksheet_num_or_title, str):
142         worksheet = spreadsheet.get_worksheet_by_title(worksheet_num_or_title)
143         if worksheet is None:
144             raise WorksheetNotFoundByTitleError(worksheet_num_or_title)
145 
146     worksheet_values = worksheet.get_all_values()
147 
148     return parse_worksheet(worksheet_values)
149 
150 
151 def is_url_key(key):
152     return key.startswith("https://")
153 
154 
155 def parse_api_error(error):
156     error_data = error.response.json()
157 
158     if "error" in error_data and "message" in error_data["error"]:
159         message = error_data["error"]["message"]
160     else:
161         message = str(error)
162 
163     return message
164 
165 
166 class SpreadsheetWrapper:
167     def __init__(self, spreadsheet):
168         self.spreadsheet = spreadsheet
169 
170     def worksheets(self):
171         return self.spreadsheet.worksheets()
172 
173     def get_worksheet_by_index(self, index):
174         return self.spreadsheet.get_worksheet(index)
175 
176     def get_worksheet_by_title(self, title):
177         try:
178             return self.spreadsheet.worksheet(title)
179         except GSWorksheetNotFound:
180             return None
181 
182 
183 class TimeoutSession(Session):
184     def request(self, *args, **kwargs):
185         kwargs.setdefault("timeout", 300)
186         return super(TimeoutSession, self).request(*args, **kwargs)
187 
188 
189 class GoogleSpreadsheet(BaseQueryRunner):
190     should_annotate_query = False
191 
192     def __init__(self, configuration):
193         super(GoogleSpreadsheet, self).__init__(configuration)
194         self.syntax = "custom"
195 
196     @classmethod
197     def name(cls):
198         return "Google Sheets"
199 
200     @classmethod
201     def type(cls):
202         return "google_spreadsheets"
203 
204     @classmethod
205     def enabled(cls):
206         return enabled
207 
208     @classmethod
209     def configuration_schema(cls):
210         return {
211             "type": "object",
212             "properties": {"jsonKeyFile": {"type": "string", "title": "JSON Key File (ADC is used if omitted)"}},
213             "required": [],
214             "secret": ["jsonKeyFile"],
215         }
216 
217     def _get_spreadsheet_service(self):
218         scopes = ["https://spreadsheets.google.com/feeds"]
219 
220         try:
221             key = json_loads(b64decode(self.configuration["jsonKeyFile"]))
222             creds = Credentials.from_service_account_info(key, scopes=scopes)
223         except KeyError:
224             creds = google.auth.default(scopes=scopes)[0]
225 
226         timeout_session = Session()
227         timeout_session.requests_session = TimeoutSession()
228         spreadsheetservice = gspread.Client(auth=creds, session=timeout_session)
229         spreadsheetservice.login()
230         return spreadsheetservice
231 
232     def test_connection(self):
233         service = self._get_spreadsheet_service()
234         test_spreadsheet_key = "1S0mld7LMbUad8LYlo13Os9f7eNjw57MqVC0YiCd1Jis"
235         try:
236             service.open_by_key(test_spreadsheet_key).worksheets()
237         except APIError as e:
238             message = parse_api_error(e)
239             raise Exception(message)
240 
241     def run_query(self, query, user):
242         logger.debug("Spreadsheet is about to execute query: %s", query)
243         key, worksheet_num_or_title = parse_query(query)
244 
245         try:
246             spreadsheet_service = self._get_spreadsheet_service()
247 
248             if is_url_key(key):
249                 spreadsheet = spreadsheet_service.open_by_url(key)
250             else:
251                 spreadsheet = spreadsheet_service.open_by_key(key)
252 
253             data = parse_spreadsheet(SpreadsheetWrapper(spreadsheet), worksheet_num_or_title)
254 
255             return json_dumps(data), None
256         except gspread.SpreadsheetNotFound:
257             return (
258                 None,
259                 "Spreadsheet ({}) not found. Make sure you used correct id.".format(key),
260             )
261         except APIError as e:
262             return None, parse_api_error(e)
263 
264 
265 register(GoogleSpreadsheet)
266
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/redash/query_runner/google_spreadsheets.py b/redash/query_runner/google_spreadsheets.py
--- a/redash/query_runner/google_spreadsheets.py
+++ b/redash/query_runner/google_spreadsheets.py
@@ -23,6 +23,7 @@
 try:
     import google.auth
     import gspread
+    from google.auth.exceptions import GoogleAuthError
     from google.oauth2.service_account import Credentials
     from gspread.exceptions import APIError
     from gspread.exceptions import WorksheetNotFound as GSWorksheetNotFound
@@ -230,13 +231,17 @@
         return spreadsheetservice
 
     def test_connection(self):
-        service = self._get_spreadsheet_service()
         test_spreadsheet_key = "1S0mld7LMbUad8LYlo13Os9f7eNjw57MqVC0YiCd1Jis"
         try:
+            service = self._get_spreadsheet_service()
             service.open_by_key(test_spreadsheet_key).worksheets()
         except APIError as e:
+            logger.exception(e)
             message = parse_api_error(e)
             raise Exception(message)
+        except GoogleAuthError as e:
+            logger.exception(e)
+            raise Exception(str(e))
 
     def run_query(self, query, user):
         logger.debug("Spreadsheet is about to execute query: %s", query)

{"golden_diff": "diff --git a/redash/query_runner/google_spreadsheets.py b/redash/query_runner/google_spreadsheets.py\n--- a/redash/query_runner/google_spreadsheets.py\n+++ b/redash/query_runner/google_spreadsheets.py\n@@ -23,6 +23,7 @@\n try:\n     import google.auth\n     import gspread\n+    from google.auth.exceptions import GoogleAuthError\n     from google.oauth2.service_account import Credentials\n     from gspread.exceptions import APIError\n     from gspread.exceptions import WorksheetNotFound as GSWorksheetNotFound\n@@ -230,13 +231,17 @@\n         return spreadsheetservice\n \n     def test_connection(self):\n-        service = self._get_spreadsheet_service()\n         test_spreadsheet_key = \"1S0mld7LMbUad8LYlo13Os9f7eNjw57MqVC0YiCd1Jis\"\n         try:\n+            service = self._get_spreadsheet_service()\n             service.open_by_key(test_spreadsheet_key).worksheets()\n         except APIError as e:\n+            logger.exception(e)\n             message = parse_api_error(e)\n             raise Exception(message)\n+        except GoogleAuthError as e:\n+            logger.exception(e)\n+            raise Exception(str(e))\n \n     def run_query(self, query, user):\n         logger.debug(\"Spreadsheet is about to execute query: %s\", query)\n", "issue": "For self-hosted instance, Google sheet connection test failed, but can't find any logs, how to trouble shooting such cases\n![image](https://github.com/getredash/redash/assets/39286702/5b480d44-b38d-4035-a68e-49a5bc15220d)\r\n\r\n![image](https://github.com/getredash/redash/assets/39286702/6c7a4e14-a7f7-4335-84e2-67eead78e260)\r\n\r\nWhen test connections timeout, can't find any logs on worker/scheduler/server docker logs.\r\neven make sure the log level is debug, can't find any related logs. how to trouble shooting this\n", "before_files": [{"content": "import logging\nimport re\nfrom base64 import b64decode\n\nfrom dateutil import parser\nfrom requests import Session\nfrom xlsxwriter.utility import xl_col_to_name\n\nfrom redash.query_runner import (\n    TYPE_BOOLEAN,\n    TYPE_DATETIME,\n    TYPE_FLOAT,\n    TYPE_INTEGER,\n    TYPE_STRING,\n    BaseQueryRunner,\n    guess_type,\n    register,\n)\nfrom redash.utils import json_dumps, json_loads\n\nlogger = logging.getLogger(__name__)\n\ntry:\n    import google.auth\n    import gspread\n    from google.oauth2.service_account import Credentials\n    from gspread.exceptions import APIError\n    from gspread.exceptions import WorksheetNotFound as GSWorksheetNotFound\n\n    enabled = True\nexcept ImportError:\n    enabled = False\n\n\ndef _load_key(filename):\n    with open(filename, \"rb\") as f:\n        return json_loads(f.read())\n\n\ndef _get_columns_and_column_names(row):\n    column_names = []\n    columns = []\n    duplicate_counter = 1\n\n    for i, column_name in enumerate(row):\n        if not column_name:\n            column_name = \"column_{}\".format(xl_col_to_name(i))\n\n        if column_name in column_names:\n            column_name = \"{}{}\".format(column_name, duplicate_counter)\n            duplicate_counter += 1\n\n        column_names.append(column_name)\n        columns.append({\"name\": column_name, \"friendly_name\": column_name, \"type\": TYPE_STRING})\n\n    return columns, column_names\n\n\ndef _value_eval_list(row_values, col_types):\n    value_list = []\n    raw_values = zip(col_types, row_values)\n    for typ, rval in raw_values:\n        try:\n            if rval is None or rval == \"\":\n                val = None\n            elif typ == TYPE_BOOLEAN:\n                val = True if str(rval).lower() == \"true\" else False\n            elif typ == TYPE_DATETIME:\n                val = parser.parse(rval)\n            elif typ == TYPE_FLOAT:\n                val = float(rval)\n            elif typ == TYPE_INTEGER:\n                val = int(rval)\n            else:\n                # for TYPE_STRING and default\n                val = str(rval)\n            value_list.append(val)\n        except (ValueError, OverflowError):\n            value_list.append(rval)\n    return value_list\n\n\nHEADER_INDEX = 0\n\n\nclass WorksheetNotFoundError(Exception):\n    def __init__(self, worksheet_num, worksheet_count):\n        message = \"Worksheet number {} not found. Spreadsheet has {} worksheets. Note that the worksheet count is zero based.\".format(\n            worksheet_num, worksheet_count\n        )\n        super(WorksheetNotFoundError, self).__init__(message)\n\n\nclass WorksheetNotFoundByTitleError(Exception):\n    def __init__(self, worksheet_title):\n        message = \"Worksheet title '{}' not found.\".format(worksheet_title)\n        super(WorksheetNotFoundByTitleError, self).__init__(message)\n\n\ndef parse_query(query):\n    values = query.split(\"|\")\n    key = values[0]  # key of the spreadsheet\n    worksheet_num_or_title = 0  # A default value for when a number of inputs is invalid\n    if len(values) == 2:\n        s = values[1].strip()\n        if len(s) > 0:\n            if re.match(r\"^\\\"(.*?)\\\"$\", s):\n                # A string quoted by \" means a title of worksheet\n                worksheet_num_or_title = s[1:-1]\n            else:\n                # if spreadsheet contains more than one worksheet - this is the number of it\n                worksheet_num_or_title = int(s)\n\n    return key, worksheet_num_or_title\n\n\ndef parse_worksheet(worksheet):\n    if not worksheet:\n        return {\"columns\": [], \"rows\": []}\n\n    columns, column_names = _get_columns_and_column_names(worksheet[HEADER_INDEX])\n\n    if len(worksheet) > 1:\n        for j, value in enumerate(worksheet[HEADER_INDEX + 1]):\n            columns[j][\"type\"] = guess_type(value)\n\n    column_types = [c[\"type\"] for c in columns]\n    rows = [dict(zip(column_names, _value_eval_list(row, column_types))) for row in worksheet[HEADER_INDEX + 1 :]]\n    data = {\"columns\": columns, \"rows\": rows}\n\n    return data\n\n\ndef parse_spreadsheet(spreadsheet, worksheet_num_or_title):\n    worksheet = None\n    if isinstance(worksheet_num_or_title, int):\n        worksheet = spreadsheet.get_worksheet_by_index(worksheet_num_or_title)\n        if worksheet is None:\n            worksheet_count = len(spreadsheet.worksheets())\n            raise WorksheetNotFoundError(worksheet_num_or_title, worksheet_count)\n    elif isinstance(worksheet_num_or_title, str):\n        worksheet = spreadsheet.get_worksheet_by_title(worksheet_num_or_title)\n        if worksheet is None:\n            raise WorksheetNotFoundByTitleError(worksheet_num_or_title)\n\n    worksheet_values = worksheet.get_all_values()\n\n    return parse_worksheet(worksheet_values)\n\n\ndef is_url_key(key):\n    return key.startswith(\"https://\")\n\n\ndef parse_api_error(error):\n    error_data = error.response.json()\n\n    if \"error\" in error_data and \"message\" in error_data[\"error\"]:\n        message = error_data[\"error\"][\"message\"]\n    else:\n        message = str(error)\n\n    return message\n\n\nclass SpreadsheetWrapper:\n    def __init__(self, spreadsheet):\n        self.spreadsheet = spreadsheet\n\n    def worksheets(self):\n        return self.spreadsheet.worksheets()\n\n    def get_worksheet_by_index(self, index):\n        return self.spreadsheet.get_worksheet(index)\n\n    def get_worksheet_by_title(self, title):\n        try:\n            return self.spreadsheet.worksheet(title)\n        except GSWorksheetNotFound:\n            return None\n\n\nclass TimeoutSession(Session):\n    def request(self, *args, **kwargs):\n        kwargs.setdefault(\"timeout\", 300)\n        return super(TimeoutSession, self).request(*args, **kwargs)\n\n\nclass GoogleSpreadsheet(BaseQueryRunner):\n    should_annotate_query = False\n\n    def __init__(self, configuration):\n        super(GoogleSpreadsheet, self).__init__(configuration)\n        self.syntax = \"custom\"\n\n    @classmethod\n    def name(cls):\n        return \"Google Sheets\"\n\n    @classmethod\n    def type(cls):\n        return \"google_spreadsheets\"\n\n    @classmethod\n    def enabled(cls):\n        return enabled\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            \"type\": \"object\",\n            \"properties\": {\"jsonKeyFile\": {\"type\": \"string\", \"title\": \"JSON Key File (ADC is used if omitted)\"}},\n            \"required\": [],\n            \"secret\": [\"jsonKeyFile\"],\n        }\n\n    def _get_spreadsheet_service(self):\n        scopes = [\"https://spreadsheets.google.com/feeds\"]\n\n        try:\n            key = json_loads(b64decode(self.configuration[\"jsonKeyFile\"]))\n            creds = Credentials.from_service_account_info(key, scopes=scopes)\n        except KeyError:\n            creds = google.auth.default(scopes=scopes)[0]\n\n        timeout_session = Session()\n        timeout_session.requests_session = TimeoutSession()\n        spreadsheetservice = gspread.Client(auth=creds, session=timeout_session)\n        spreadsheetservice.login()\n        return spreadsheetservice\n\n    def test_connection(self):\n        service = self._get_spreadsheet_service()\n        test_spreadsheet_key = \"1S0mld7LMbUad8LYlo13Os9f7eNjw57MqVC0YiCd1Jis\"\n        try:\n            service.open_by_key(test_spreadsheet_key).worksheets()\n        except APIError as e:\n            message = parse_api_error(e)\n            raise Exception(message)\n\n    def run_query(self, query, user):\n        logger.debug(\"Spreadsheet is about to execute query: %s\", query)\n        key, worksheet_num_or_title = parse_query(query)\n\n        try:\n            spreadsheet_service = self._get_spreadsheet_service()\n\n            if is_url_key(key):\n                spreadsheet = spreadsheet_service.open_by_url(key)\n            else:\n                spreadsheet = spreadsheet_service.open_by_key(key)\n\n            data = parse_spreadsheet(SpreadsheetWrapper(spreadsheet), worksheet_num_or_title)\n\n            return json_dumps(data), None\n        except gspread.SpreadsheetNotFound:\n            return (\n                None,\n                \"Spreadsheet ({}) not found. Make sure you used correct id.\".format(key),\n            )\n        except APIError as e:\n            return None, parse_api_error(e)\n\n\nregister(GoogleSpreadsheet)\n", "path": "redash/query_runner/google_spreadsheets.py"}], "after_files": [{"content": "import logging\nimport re\nfrom base64 import b64decode\n\nfrom dateutil import parser\nfrom requests import Session\nfrom xlsxwriter.utility import xl_col_to_name\n\nfrom redash.query_runner import (\n    TYPE_BOOLEAN,\n    TYPE_DATETIME,\n    TYPE_FLOAT,\n    TYPE_INTEGER,\n    TYPE_STRING,\n    BaseQueryRunner,\n    guess_type,\n    register,\n)\nfrom redash.utils import json_dumps, json_loads\n\nlogger = logging.getLogger(__name__)\n\ntry:\n    import google.auth\n    import gspread\n    from google.auth.exceptions import GoogleAuthError\n    from google.oauth2.service_account import Credentials\n    from gspread.exceptions import APIError\n    from gspread.exceptions import WorksheetNotFound as GSWorksheetNotFound\n\n    enabled = True\nexcept ImportError:\n    enabled = False\n\n\ndef _load_key(filename):\n    with open(filename, \"rb\") as f:\n        return json_loads(f.read())\n\n\ndef _get_columns_and_column_names(row):\n    column_names = []\n    columns = []\n    duplicate_counter = 1\n\n    for i, column_name in enumerate(row):\n        if not column_name:\n            column_name = \"column_{}\".format(xl_col_to_name(i))\n\n        if column_name in column_names:\n            column_name = \"{}{}\".format(column_name, duplicate_counter)\n            duplicate_counter += 1\n\n        column_names.append(column_name)\n        columns.append({\"name\": column_name, \"friendly_name\": column_name, \"type\": TYPE_STRING})\n\n    return columns, column_names\n\n\ndef _value_eval_list(row_values, col_types):\n    value_list = []\n    raw_values = zip(col_types, row_values)\n    for typ, rval in raw_values:\n        try:\n            if rval is None or rval == \"\":\n                val = None\n            elif typ == TYPE_BOOLEAN:\n                val = True if str(rval).lower() == \"true\" else False\n            elif typ == TYPE_DATETIME:\n                val = parser.parse(rval)\n            elif typ == TYPE_FLOAT:\n                val = float(rval)\n            elif typ == TYPE_INTEGER:\n                val = int(rval)\n            else:\n                # for TYPE_STRING and default\n                val = str(rval)\n            value_list.append(val)\n        except (ValueError, OverflowError):\n            value_list.append(rval)\n    return value_list\n\n\nHEADER_INDEX = 0\n\n\nclass WorksheetNotFoundError(Exception):\n    def __init__(self, worksheet_num, worksheet_count):\n        message = \"Worksheet number {} not found. Spreadsheet has {} worksheets. Note that the worksheet count is zero based.\".format(\n            worksheet_num, worksheet_count\n        )\n        super(WorksheetNotFoundError, self).__init__(message)\n\n\nclass WorksheetNotFoundByTitleError(Exception):\n    def __init__(self, worksheet_title):\n        message = \"Worksheet title '{}' not found.\".format(worksheet_title)\n        super(WorksheetNotFoundByTitleError, self).__init__(message)\n\n\ndef parse_query(query):\n    values = query.split(\"|\")\n    key = values[0]  # key of the spreadsheet\n    worksheet_num_or_title = 0  # A default value for when a number of inputs is invalid\n    if len(values) == 2:\n        s = values[1].strip()\n        if len(s) > 0:\n            if re.match(r\"^\\\"(.*?)\\\"$\", s):\n                # A string quoted by \" means a title of worksheet\n                worksheet_num_or_title = s[1:-1]\n            else:\n                # if spreadsheet contains more than one worksheet - this is the number of it\n                worksheet_num_or_title = int(s)\n\n    return key, worksheet_num_or_title\n\n\ndef parse_worksheet(worksheet):\n    if not worksheet:\n        return {\"columns\": [], \"rows\": []}\n\n    columns, column_names = _get_columns_and_column_names(worksheet[HEADER_INDEX])\n\n    if len(worksheet) > 1:\n        for j, value in enumerate(worksheet[HEADER_INDEX + 1]):\n            columns[j][\"type\"] = guess_type(value)\n\n    column_types = [c[\"type\"] for c in columns]\n    rows = [dict(zip(column_names, _value_eval_list(row, column_types))) for row in worksheet[HEADER_INDEX + 1 :]]\n    data = {\"columns\": columns, \"rows\": rows}\n\n    return data\n\n\ndef parse_spreadsheet(spreadsheet, worksheet_num_or_title):\n    worksheet = None\n    if isinstance(worksheet_num_or_title, int):\n        worksheet = spreadsheet.get_worksheet_by_index(worksheet_num_or_title)\n        if worksheet is None:\n            worksheet_count = len(spreadsheet.worksheets())\n            raise WorksheetNotFoundError(worksheet_num_or_title, worksheet_count)\n    elif isinstance(worksheet_num_or_title, str):\n        worksheet = spreadsheet.get_worksheet_by_title(worksheet_num_or_title)\n        if worksheet is None:\n            raise WorksheetNotFoundByTitleError(worksheet_num_or_title)\n\n    worksheet_values = worksheet.get_all_values()\n\n    return parse_worksheet(worksheet_values)\n\n\ndef is_url_key(key):\n    return key.startswith(\"https://\")\n\n\ndef parse_api_error(error):\n    error_data = error.response.json()\n\n    if \"error\" in error_data and \"message\" in error_data[\"error\"]:\n        message = error_data[\"error\"][\"message\"]\n    else:\n        message = str(error)\n\n    return message\n\n\nclass SpreadsheetWrapper:\n    def __init__(self, spreadsheet):\n        self.spreadsheet = spreadsheet\n\n    def worksheets(self):\n        return self.spreadsheet.worksheets()\n\n    def get_worksheet_by_index(self, index):\n        return self.spreadsheet.get_worksheet(index)\n\n    def get_worksheet_by_title(self, title):\n        try:\n            return self.spreadsheet.worksheet(title)\n        except GSWorksheetNotFound:\n            return None\n\n\nclass TimeoutSession(Session):\n    def request(self, *args, **kwargs):\n        kwargs.setdefault(\"timeout\", 300)\n        return super(TimeoutSession, self).request(*args, **kwargs)\n\n\nclass GoogleSpreadsheet(BaseQueryRunner):\n    should_annotate_query = False\n\n    def __init__(self, configuration):\n        super(GoogleSpreadsheet, self).__init__(configuration)\n        self.syntax = \"custom\"\n\n    @classmethod\n    def name(cls):\n        return \"Google Sheets\"\n\n    @classmethod\n    def type(cls):\n        return \"google_spreadsheets\"\n\n    @classmethod\n    def enabled(cls):\n        return enabled\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            \"type\": \"object\",\n            \"properties\": {\"jsonKeyFile\": {\"type\": \"string\", \"title\": \"JSON Key File (ADC is used if omitted)\"}},\n            \"required\": [],\n            \"secret\": [\"jsonKeyFile\"],\n        }\n\n    def _get_spreadsheet_service(self):\n        scopes = [\"https://spreadsheets.google.com/feeds\"]\n\n        try:\n            key = json_loads(b64decode(self.configuration[\"jsonKeyFile\"]))\n            creds = Credentials.from_service_account_info(key, scopes=scopes)\n        except KeyError:\n            creds = google.auth.default(scopes=scopes)[0]\n\n        timeout_session = Session()\n        timeout_session.requests_session = TimeoutSession()\n        spreadsheetservice = gspread.Client(auth=creds, session=timeout_session)\n        spreadsheetservice.login()\n        return spreadsheetservice\n\n    def test_connection(self):\n        test_spreadsheet_key = \"1S0mld7LMbUad8LYlo13Os9f7eNjw57MqVC0YiCd1Jis\"\n        try:\n            service = self._get_spreadsheet_service()\n            service.open_by_key(test_spreadsheet_key).worksheets()\n        except APIError as e:\n            logger.exception(e)\n            message = parse_api_error(e)\n            raise Exception(message)\n        except GoogleAuthError as e:\n            logger.exception(e)\n            raise Exception(str(e))\n\n    def run_query(self, query, user):\n        logger.debug(\"Spreadsheet is about to execute query: %s\", query)\n        key, worksheet_num_or_title = parse_query(query)\n\n        try:\n            spreadsheet_service = self._get_spreadsheet_service()\n\n            if is_url_key(key):\n                spreadsheet = spreadsheet_service.open_by_url(key)\n            else:\n                spreadsheet = spreadsheet_service.open_by_key(key)\n\n            data = parse_spreadsheet(SpreadsheetWrapper(spreadsheet), worksheet_num_or_title)\n\n            return json_dumps(data), None\n        except gspread.SpreadsheetNotFound:\n            return (\n                None,\n                \"Spreadsheet ({}) not found. Make sure you used correct id.\".format(key),\n            )\n        except APIError as e:\n            return None, parse_api_error(e)\n\n\nregister(GoogleSpreadsheet)\n", "path": "redash/query_runner/google_spreadsheets.py"}]}