############################################################################ # Copyright 2022 Workiva Inc. # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # http://www.apache.org/licenses/LICENSE-2.0 # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. ############################################################################ import decimal import os import time from enum import Enum import requests AUTH_URL = "https://api.app.wdesk.com/oauth2/token" SS_API_URL = "https://api.app.wdesk.com/spreadsheets/" API_VERSION = "2026-01-01" REQUEST_TIMEOUT = 30 OPERATION_TIMEOUT = 120 # Capture parameters when you run the script from the Scripting Editor or Chain. CLIENT_ID = os.getenv("CLIENT_ID") CLIENT_SECRET = os.getenv("CLIENT_SECRET") DOCUMENT_ID = os.getenv("DOCUMENT_ID") # Automations include these metadata variables. They are used here only to # distinguish an automation run from a manual run. IS_AUTOMATION = bool(os.getenv("INPUT_RESOURCE_ID")) if IS_AUTOMATION: CLIENT_ID = "" # Enter the automation API grant client ID. CLIENT_SECRET = "" # Enter the automation API grant client secret. class NumberPrecision(Enum): BASIS_POINTS = 0.0001 HUNDREDTHS = 0.01 ONES = 1 THOUSANDS = 1000 TEN_THOUSANDS = 10_000 MILLIONS = 1_000_000 HUNDRED_MILLIONS = 100_000_000 BILLIONS = 1_000_000_000 TRILLIONS = 1_000_000_000_000 class ApiAuth: def __init__(self): self._headers = { "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8", "X-Version": API_VERSION, } def get_auth_token(self): if not CLIENT_ID or not CLIENT_SECRET: raise ValueError("CLIENT_ID and CLIENT_SECRET are required") token_res = requests.post( AUTH_URL, data={ "client_id": CLIENT_ID, "client_secret": CLIENT_SECRET, "grant_type": "client_credentials", }, headers=self._headers, timeout=REQUEST_TIMEOUT, ) token_res.raise_for_status() return token_res.json()["access_token"] class SpreadsheetApi: def __init__(self, access_token): self._headers = { "Authorization": f"Bearer {access_token}", "X-Version": API_VERSION, } self._totalRowsHidden = 0 def wait_for_operation(self, operation_url): deadline = time.monotonic() + OPERATION_TIMEOUT while time.monotonic() < deadline: response = requests.get( operation_url, headers=self._headers, timeout=REQUEST_TIMEOUT, ) response.raise_for_status() operation = response.json() if operation["status"] == "completed": return if operation["status"] == "failed": raise RuntimeError( operation.get("failureReason", "Spreadsheet update failed") ) time.sleep(float(response.headers.get("Retry-After", "2"))) raise TimeoutError("Spreadsheet update did not complete before the timeout") def submit_update(self, url, body): response = requests.post( url, headers=self._headers, json=body, timeout=REQUEST_TIMEOUT, ) response.raise_for_status() operation_url = response.headers.get("Location") if not operation_url: operation_url = response.json().get("operationLocation") if not operation_url: raise RuntimeError("Spreadsheet update did not return an operation URL") self.wait_for_operation(operation_url) def get_document_tables(self, doc_id): """Retrieve the identifiers for all tables in a document.""" ids = [] url = f"{SS_API_URL}{doc_id}/sheets" while url: response = requests.get( url, headers=self._headers, timeout=REQUEST_TIMEOUT, ) response.raise_for_status() body = response.json() ids.extend(table["id"] for table in body.get("data", [])) url = body.get("@nextLink") return ids def get_table_data(self, doc_id, table_id): """Retrieve the data for a table.""" cells = [] url = f"{SS_API_URL}{doc_id}/sheets/{table_id}/sheetdata" params = { "$fields": "cells.calculatedValue,cells.formats.valueFormat", } while url: response = requests.get( url, headers=self._headers, params=params, timeout=REQUEST_TIMEOUT, ) response.raise_for_status() body = response.json(parse_float=decimal.Decimal) cells.extend(body["data"]["cells"]) url = body.get("@nextLink") params = None return cells def hide_table_rows(self, doc_id, table_id, row_indices): """Hide the rows specified in row_indices.""" if not row_indices: return self._totalRowsHidden += len(row_indices) row_indices = sorted(set(row_indices)) intervals = [] start_index = row_indices[0] end_index = row_indices[0] for index in row_indices[1:]: if index > end_index + 1: intervals.append({"start": start_index, "end": end_index}) start_index = index end_index = index intervals.append({"start": start_index, "end": end_index}) url = f"{SS_API_URL}{doc_id}/sheets/{table_id}/update" self.submit_update( url, { "hideRows": { "force": True, "intervals": intervals, } }, ) def unhide_table_rows(self, doc_id, table_id): """Unhide all rows in a table.""" url = f"{SS_API_URL}{doc_id}/sheets/{table_id}/update" self.submit_update( url, {"unhideRows": {"intervals": [{}]}}, ) def get_rows_as_displayed(self, doc_id, table_id): """Return rows with their rounded and scaled display values.""" rows_as_displayed = [] for row in self.get_table_data(doc_id, table_id): row_as_displayed = [] for cell in row: if not cell: row_as_displayed.append(None) continue calculated_value = cell.get("calculatedValue") displayed_value = calculated_value if calculated_value is not None and not isinstance( calculated_value, bool ): try: displayed_value = decimal.Decimal(str(calculated_value)) except decimal.InvalidOperation: pass if isinstance(displayed_value, decimal.Decimal): value_format = cell.get("formats", {}).get("valueFormat", {}) shown_in = value_format.get("shownIn") if shown_in: precision_name = str(shown_in).replace(" ", "_").upper() try: shown_in_value = NumberPrecision[precision_name].value except KeyError as exc: raise ValueError( f"Unsupported shownIn value: {shown_in}" ) from exc displayed_value /= decimal.Decimal(str(shown_in_value)) precision = value_format.get("precision") if precision and not precision.get("auto", False): displayed_value = displayed_value.quantize( decimal.Decimal(10) ** -int(precision["value"]), decimal.ROUND_HALF_UP, ) row_as_displayed.append(displayed_value) rows_as_displayed.append(row_as_displayed) return rows_as_displayed def section_rows_to_hide( self, start_row, stop_row, zero_rows, has_numeric_data, has_non_zero_numeric_data, ): """Return the rows to hide for a content section.""" if has_non_zero_numeric_data: return zero_rows if has_numeric_data: return range(start_row, stop_row + 1) # Don't hide sections with no numeric rows. return [] def find_rows_to_hide(self, rows): """Return rows that are zero rows or belong to all-zero sections.""" rows_to_hide = [] # Content section variables title_row = None zero_rows = [] has_numeric_data = False has_non_zero_numeric_data = False for row_index, row in enumerate(rows): # Row variables is_spacer_row = True has_nums = False all_zeroes = True for cell in row: if cell not in (None, ""): is_spacer_row = False if isinstance(cell, decimal.Decimal): has_nums = True if cell != 0: all_zeroes = False break if is_spacer_row: if title_row is not None: rows_to_hide.extend( self.section_rows_to_hide( title_row, row_index, zero_rows, has_numeric_data, has_non_zero_numeric_data, ) ) title_row = None zero_rows = [] has_numeric_data = False has_non_zero_numeric_data = False else: if title_row is None: title_row = row_index if has_nums: has_numeric_data = True if all_zeroes: zero_rows.append(row_index) else: has_non_zero_numeric_data = True if title_row is not None: rows_to_hide.extend( self.section_rows_to_hide( title_row, len(rows) - 1, zero_rows, has_numeric_data, has_non_zero_numeric_data, ) ) return rows_to_hide def hide_rows(self, doc_id): """Hide zero rows and empty content sections in every table.""" table_ids = self.get_document_tables(doc_id) print(f"Hiding rows in {len(table_ids)} tables") for index, table_id in enumerate(table_ids): print(f"Hiding rows in table {index + 1}: {table_id}") rows_as_displayed = self.get_rows_as_displayed(doc_id, table_id) rows_to_hide = self.find_rows_to_hide(rows_as_displayed) self.hide_table_rows(doc_id, table_id, rows_to_hide) print(f"Total rows hidden: {self._totalRowsHidden}") def unhide_all_rows(self, doc_id): """Unhide every row in every table.""" table_ids = self.get_document_tables(doc_id) print(f"Unhiding {len(table_ids)} tables") for index, table_id in enumerate(table_ids): print(f"Unhiding table {index + 1}: {table_id}") self.unhide_table_rows(doc_id, table_id) def main(): if not DOCUMENT_ID: raise ValueError("DOCUMENT_ID is required") auth_token = ApiAuth().get_auth_token() spreadsheet_api = SpreadsheetApi(auth_token) spreadsheet_api.unhide_all_rows(DOCUMENT_ID) spreadsheet_api.hide_rows(DOCUMENT_ID) if __name__ == "__main__": main()