1"""Google Sheets over the REST API, authenticated by a SERVICE ACCOUNT.
2
3Why a service account and not OAuth — this is the whole reason the Actor exists.
4
5The incumbent (`lukaskrivka/google-sheets`, 673 users and **61,480 runs in 30 days**) authenticates by
6having the user "connect your Google account" through OAuth, and **48% of those runs succeed**. An
7OAuth grant is a living thing: it expires, it gets revoked, the connect flow is abandoned half way, the
8token lands in a key-value store that a later run cannot find, and a user with several Google accounts
9picks the wrong one. Every one of those is a failed run that looks like a broken Actor.
10
11A service-account key is a static credential. It does not expire, there is no browser flow to abandon,
12and the same JSON works on every run forever. It also means the failure that remains is a single,
13nameable one: *the sheet was never shared with the service account*, which this module detects and says
14out loud.
15
16But a service account demands a Google Cloud project, and that is developer work — too much to ask of a
17marketer who just wants scraped rows in a spreadsheet. So **three credential types are accepted**, and
18the docstring order is the reliability order:
19
201. `service_account` — a JSON key. Never expires. Needs a GCP project.
212. `refresh_token` — client id + secret + refresh token. Survives across runs, and can be revoked. No
22 GCP project needed if the user already has an OAuth client.
233. `access_token` — pasted from Google's OAuth Playground. **Easiest by far** (a browser click and one
24 copy-paste, no GCP anything) but valid for about an hour, so it suits a one-off run rather than a
25 schedule.
26
27Types 2 and 3 reintroduce the failure class this Actor was built to avoid, and that is an honest
28trade-off rather than a regression — the difference is that an expired or revoked grant is *detected and
29named* here, instead of surfacing as an unexplained crash.
30
31Writes default to **RAW**, not `USER_ENTERED`, and that is a data-integrity decision rather than a
32preference. Measured against a real sheet, `USER_ENTERED` silently rewrote scraped values:
33
34 "=1+1" -> 2 a scraped string became a LIVE FORMULA
35 "+84901234567" -> 84901234567 the leading + was dropped from a phone number
36 "0123" -> 123 the leading zero was dropped
37
38The first is formula injection: any scraped cell beginning with `=` executes inside the user's
39spreadsheet. RAW preserves all three exactly as the source produced them. Callers who genuinely want
40Sheets to parse dates can opt in per run.
41
42No Google SDK. The token exchange is a signed JWT (RS256) and the rest is plain REST, which keeps the
43dependency surface to `cryptography` alone.
44"""
45
46from __future__ import annotations
47
48import base64
49import json
50import logging
51import time
52import urllib.error
53import urllib.parse
54import urllib.request
55
56log = logging.getLogger("sheets")
57
58TOKEN_URL = "https://oauth2.googleapis.com/token"
59API = "https://sheets.googleapis.com/v4/spreadsheets"
60SCOPE = "https://www.googleapis.com/auth/spreadsheets"
61TIMEOUT = 60
62
63
64
65MIN_GAP = 1.05
66RETRY_STATUS = (429, 500, 502, 503, 504)
67BACKOFF = (2.0, 6.0, 15.0)
68
69_REFRESH_HINT = (
70 "The usual causes, in order of likelihood: the client id/secret do not match the client the token "
71 'was issued to; the grant was revoked; or the OAuth app is still in "Testing" publishing status — '
72 "Google expires those refresh tokens after 7 days. Re-authorise, or publish the app."
73)
74
75
76class SheetsError(RuntimeError):
77 """Something went wrong that the caller can act on. The message is aimed at a human."""
78
79
80class AuthError(SheetsError):
81 """The credential itself is unusable — a different problem from a permission error."""
82
83
84class PermissionError_(SheetsError):
85 """The credential is fine but it cannot see the spreadsheet. Almost always an unshared sheet."""
86
87
88def _b64(raw: bytes) -> bytes:
89 return base64.urlsafe_b64encode(raw).rstrip(b"=")
90
91
92def _post_form(url: str, fields: dict, what: str = "service-account key") -> dict:
93 body = urllib.parse.urlencode(fields).encode()
94 req = urllib.request.Request(
95 url, data=body, headers={"Content-Type": "application/x-www-form-urlencoded"}
96 )
97 try:
98 return json.loads(urllib.request.urlopen(req, timeout=TIMEOUT).read())
99 except urllib.error.HTTPError as exc:
100 detail = exc.read().decode("utf-8", "replace")
101
102
103
104 raise AuthError(
105 "Google refused the %s (HTTP %s): %s" % (what, exc.code, detail[:300])
106 ) from None
107 except urllib.error.URLError as exc:
108 raise SheetsError("cannot reach Google (%s)" % exc) from None
109
110
111def build_auth(cfg: dict):
112 """Pick the credential from the input and return (auth_kind, payload).
113
114 Exactly one method may be supplied. Accepting several silently and choosing one would make a
115 misconfiguration invisible — the user would think a token was in use when it was not.
116 """
117 key_raw = cfg.get("serviceAccountKey")
118 refresh = (cfg.get("oauthRefreshToken") or "").strip()
119 access = (cfg.get("oauthAccessToken") or "").strip()
120 given = [n for n, v in (("serviceAccountKey", key_raw), ("oauthRefreshToken", refresh),
121 ("oauthAccessToken", access)) if v]
122 if not given:
123 raise AuthError(
124 "No credential given. Pick ONE:\n"
125 " • serviceAccountKey — most reliable, never expires (needs a Google Cloud project)\n"
126 " • oauthRefreshToken + oauthClientId + oauthClientSecret — survives across runs\n"
127 " • oauthAccessToken — easiest: get one at developers.google.com/oauthplayground "
128 "(scope https://www.googleapis.com/auth/spreadsheets). Valid about an hour, so it suits a "
129 "one-off run."
130 )
131 if len(given) > 1:
132 raise AuthError(
133 "More than one credential given (%s). Supply exactly one so it is unambiguous which is "
134 "in use." % ", ".join(given)
135 )
136 if key_raw:
137 return "service_account", parse_key(key_raw)
138 if refresh:
139 cid = (cfg.get("oauthClientId") or "").strip()
140 secret = (cfg.get("oauthClientSecret") or "").strip()
141 missing = [n for n, v in (("oauthClientId", cid), ("oauthClientSecret", secret)) if not v]
142 if missing:
143 raise AuthError(
144 "A refresh token also needs %s — it is the OAuth client the token was issued to. "
145 "If you do not have one, use oauthAccessToken from the OAuth Playground instead."
146 % " and ".join(missing)
147 )
148 return "refresh_token", {"client_id": cid, "client_secret": secret, "refresh_token": refresh}
149 return "access_token", {"access_token": access}
150
151
152def parse_key(raw) -> dict:
153 """Accept the key as a dict or as pasted JSON, and fail with the field that is missing.
154
155 Worth being fussy here: a truncated paste or a wrong file (an OAuth *client* secret rather than a
156 service-account key) is the most likely first-run mistake, and "KeyError: private_key" tells the
157 user nothing.
158 """
159 if isinstance(raw, dict):
160 key = raw
161 else:
162 text = (raw or "").strip()
163 if not text:
164 raise AuthError(
165 "No service-account key given. Create one in Google Cloud Console → IAM & Admin → "
166 "Service Accounts → Keys → Add key (JSON), then paste the whole file here."
167 )
168 try:
169 key = json.loads(text)
170 except ValueError:
171 raise AuthError(
172 "The service-account key is not valid JSON. Paste the entire .json file, including "
173 "the surrounding { }."
174 ) from None
175 if key.get("type") != "service_account":
176 raise AuthError(
177 'That JSON is not a service-account key (its "type" is %r). An OAuth client secret '
178 "will not work — you need a key created under a Service Account."
179 % (key.get("type") or "missing")
180 )
181 for field in ("client_email", "private_key", "token_uri"):
182 if not key.get(field):
183 raise AuthError("The service-account key is missing %r." % field)
184 return key
185
186
187class SheetsClient:
188 def __init__(self, key: dict, kind: str = "service_account"):
189 self.kind = kind
190 self.key = key
191
192
193 self.email = key.get("client_email") if kind == "service_account" else None
194 self._token = key.get("access_token") if kind == "access_token" else None
195 self._expires = (time.time() + 3600) if kind == "access_token" else 0.0
196 self._last = 0.0
197 self.requests_made = 0
198
199 def _share_hint(self) -> str:
200 if self.kind == "service_account":
201 return ("The service account almost certainly does not have access: open the sheet, press "
202 "Share, and add\n\n %s\n\nas an Editor." % self.email)
203 return ("The signed-in Google account cannot open this spreadsheet. Check the id, and that the "
204 "account you authorised actually has edit access to it.")
205
206
207 def _sign_jwt(self) -> str:
208 from cryptography.hazmat.primitives import hashes, serialization
209 from cryptography.hazmat.primitives.asymmetric import padding
210
211 try:
212 private = serialization.load_pem_private_key(
213 self.key["private_key"].encode(), password=None
214 )
215 except Exception as exc:
216 raise AuthError(
217 "The `private_key` in the service-account key could not be read (%s). It usually "
218 "means the JSON was edited or the newlines were mangled — re-download the key."
219 % type(exc).__name__
220 ) from None
221
222 now = int(time.time())
223 header = {"alg": "RS256", "typ": "JWT"}
224 claims = {
225 "iss": self.email,
226 "scope": SCOPE,
227 "aud": self.key.get("token_uri") or TOKEN_URL,
228
229 "exp": now + 3600,
230 "iat": now,
231 }
232 signing_input = b".".join(
233 _b64(json.dumps(p, separators=(",", ":")).encode())
234 for p in (header, claims)
235 )
236 signature = private.sign(signing_input, padding.PKCS1v15(), hashes.SHA256())
237 return (signing_input + b"." + _b64(signature)).decode()
238
239 def token(self) -> str:
240 if self._token and time.time() < self._expires - 120:
241 return self._token
242 if self.kind == "access_token":
243
244 raise AuthError(
245 "The pasted access token has expired (they last about an hour). Get a fresh one from "
246 "the OAuth Playground, or switch to a refresh token / service account for runs on a "
247 "schedule."
248 )
249 if self.kind == "refresh_token":
250 try:
251 res = _post_form(
252 TOKEN_URL, {"grant_type": "refresh_token", **self.key}, "refresh token"
253 )
254 except AuthError as exc:
255 raise AuthError("%s\n\n%s" % (exc, _REFRESH_HINT)) from None
256 if not res.get("access_token"):
257 raise AuthError(
258 "Google returned no access token for the refresh token: %s\n\n%s"
259 % (json.dumps(res)[:200], _REFRESH_HINT)
260 )
261 self._token = res["access_token"]
262 self._expires = time.time() + float(res.get("expires_in") or 3600)
263 return self._token
264 res = _post_form(
265 self.key.get("token_uri") or TOKEN_URL,
266 {
267 "grant_type": "urn:ietf:params:oauth:grant-type:jwt-bearer",
268 "assertion": self._sign_jwt(),
269 },
270 )
271 self._token = res.get("access_token")
272 if not self._token:
273 raise AuthError(
274 "Google returned no access token: %s" % json.dumps(res)[:200]
275 )
276 self._expires = time.time() + float(res.get("expires_in") or 3600)
277 return self._token
278
279
280 def _pace(self) -> None:
281 wait = MIN_GAP - (time.monotonic() - self._last)
282 if wait > 0:
283 time.sleep(wait)
284 self._last = time.monotonic()
285
286 def _call(self, method: str, path: str, body=None, params: dict | None = None):
287 url = "%s%s" % (API, path)
288 if params:
289 url += "?" + urllib.parse.urlencode(params)
290 data = json.dumps(body).encode() if body is not None else None
291 for attempt, pause in enumerate((None,) + BACKOFF):
292 if pause:
293 log.warning("Google returned a retryable error — waiting %.0fs", pause)
294 time.sleep(pause)
295 self._pace()
296 req = urllib.request.Request(
297 url,
298 data=data,
299 method=method,
300 headers={
301 "Authorization": "Bearer " + self.token(),
302 "Content-Type": "application/json",
303 },
304 )
305 try:
306 self.requests_made += 1
307 raw = urllib.request.urlopen(req, timeout=TIMEOUT).read()
308 return json.loads(raw) if raw else {}
309 except urllib.error.HTTPError as exc:
310 detail = exc.read().decode("utf-8", "replace")
311 if exc.code in RETRY_STATUS and attempt < len(BACKOFF):
312 continue
313
314
315 if exc.code == 401:
316
317
318 raise AuthError(
319 "Google rejected the credential (401). %s (Google's reply: %s)"
320 % (
321 "The pasted access token is invalid or has expired — they last about an "
322 "hour; get a fresh one from the OAuth Playground."
323 if self.kind == "access_token"
324 else "Re-check the credential.",
325 _reason(detail),
326 )
327 ) from None
328 if exc.code in (403, 404):
329 raise PermissionError_(
330 "Google says %s for this spreadsheet. %s (Google's reply: %s)"
331 % (exc.code, self._share_hint(), _reason(detail))
332 ) from None
333 raise SheetsError(
334 "Google Sheets API %s on %s: %s" % (exc.code, path, _reason(detail))
335 ) from None
336 except urllib.error.URLError as exc:
337 if attempt < len(BACKOFF):
338 continue
339 raise SheetsError("cannot reach Google Sheets (%s)" % exc) from None
340 raise SheetsError("Google Sheets kept refusing %s %s" % (method, path))
341
342
343 def metadata(self, spreadsheet_id: str) -> dict:
344 return self._call(
345 "GET",
346 "/%s" % spreadsheet_id,
347 params={"fields": "properties.title,sheets.properties"},
348 )
349
350 def tab_names(self, spreadsheet_id: str) -> list[str]:
351 meta = self.metadata(spreadsheet_id)
352 return [
353 (s.get("properties") or {}).get("title")
354 for s in (meta.get("sheets") or [])
355 if (s.get("properties") or {}).get("title")
356 ]
357
358 def add_tab(self, spreadsheet_id: str, title: str) -> None:
359 self._call(
360 "POST",
361 "/%s:batchUpdate" % spreadsheet_id,
362 body={"requests": [{"addSheet": {"properties": {"title": title}}}]},
363 )
364
365 def read(self, spreadsheet_id: str, a1: str) -> list[list]:
366 """UNFORMATTED_VALUE, not the default FORMATTED_VALUE.
367
368 Measured on a Vietnamese-locale sheet: the number 9.5 reads back as the STRING "9,5" when
369 formatted, because Sheets renders it with the spreadsheet's locale. A round-trip through this
370 Actor would therefore turn numbers into locale-specific text. Unformatted returns 9.5.
371 """
372 res = self._call(
373 "GET",
374 "/%s/values/%s" % (spreadsheet_id, urllib.parse.quote(a1)),
375 params={"majorDimension": "ROWS", "valueRenderOption": "UNFORMATTED_VALUE"},
376 )
377 return res.get("values") or []
378
379 def clear(self, spreadsheet_id: str, a1: str) -> None:
380 self._call(
381 "POST", "/%s/values/%s:clear" % (spreadsheet_id, urllib.parse.quote(a1))
382 )
383
384 def write(self, spreadsheet_id: str, a1: str, rows: list[list], value_input: str = "RAW") -> int:
385 res = self._call(
386 "PUT",
387 "/%s/values/%s" % (spreadsheet_id, urllib.parse.quote(a1)),
388 body={"values": rows},
389 params={"valueInputOption": value_input},
390 )
391 return int(res.get("updatedCells") or 0)
392
393 def append(self, spreadsheet_id: str, a1: str, rows: list[list],
394 value_input: str = "RAW") -> int:
395 res = self._call(
396 "POST",
397 "/%s/values/%s:append" % (spreadsheet_id, urllib.parse.quote(a1)),
398 body={"values": rows},
399 params={
400 "valueInputOption": value_input,
401
402
403 "insertDataOption": "INSERT_ROWS",
404 },
405 )
406 return int((res.get("updates") or {}).get("updatedCells") or 0)
407
408
409def _reason(detail: str) -> str:
410 """Google's human-readable message, without the JSON envelope."""
411 try:
412 return ((json.loads(detail).get("error") or {}).get("message") or detail)[:200]
413 except Exception:
414 return detail[:200]
415
416
417def spreadsheet_id(value: str) -> str | None:
418 """Accept a bare id or any Google Sheets URL.
419
420 Users paste the URL far more often than the id, and rejecting it would be a failed run over
421 something trivially fixable.
422 """
423 v = (value or "").strip()
424 if not v:
425 return None
426 if "/spreadsheets/d/" in v:
427 rest = v.split("/spreadsheets/d/", 1)[1]
428 return rest.split("/")[0].split("?")[0].split("#")[0] or None
429 return v.split("?")[0].split("#")[0] or None
