> ## Documentation Index > Fetch the complete documentation index at: https://docs.taqtile.com/llms.txt > Use this file to discover all available pages before exploring further. # Use Python and Excel to create custom reports > Learn how to use Python and the Manifest API to create custom Excel reports. # Exporting Manifest Job History to Excel with Python This article walks through `job_history_export.py`, a command-line script that pulls completed job records from the **Taqtile Manifest API** and writes them to a formatted Excel spreadsheet. It also explains how you can adapt the script to build other reports against the same API. *** ## What the Script Does At a high level, the script: 1. Authenticates with the Manifest REST API to get a JWT token 2. Pages through all completed jobs via GraphQL 3. Pages through all meter evidence records via GraphQL 4. Filters both datasets in memory by optional username and/or date range 5. Writes each evidence record as a row in an `.xlsx` file with bold column headers and auto-sized columns The output filename is timestamped automatically (e.g. `job_history_20250325_143012.xlsx`). *** job\_history\_export.py file: ```python expandable theme={null} #!/usr/bin/python3 # ============================================================ # Query completed Job History from the Manifest API and # export all step evidence (notes) to an Excel file. # # Usage: # python3 job_history_export.py # python3 job_history_export.py --username john@example.com # python3 job_history_export.py --start-date 202=6-01-01 --end-date 2026-03-25 # python3 job_history_export.py --username john@example.com --start-date 2026-01-01 # # Taqtile 2026 # Author: John Tomizuka # ============================================================ import requests import json import argparse import datetime import openpyxl from openpyxl.styles import Font # ------------------------------------------------------- # Arguments # ------------------------------------------------------- parser = argparse.ArgumentParser(description="Export Manifest job history evidence to Excel") parser.add_argument("--username", help="Filter by assigned user email") parser.add_argument("--start-date", help="Filter jobs completed on or after this date (YYYY-MM-DD)") parser.add_argument("--end-date", help="Filter jobs completed on or before this date (YYYY-MM-DD)") args = parser.parse_args() # ------------------------------------------------------- # Authentication # ------------------------------------------------------- BASE_URL = "https://your_domain.taqmanifest.com" login = "your_email@your_domain.com" password = "your_p@ssW0rd" auth_payload = '{\r\n "email" : "' + login + '",\r\n "password" : "' + password + '"\r\n}' auth_response = requests.request("POST", f"{BASE_URL}/rest/signin/v2", data=auth_payload, headers={"Content-Type": "application/json", "Cache-Control": "no-cache"}) auth_data = json.loads(auth_response.text) token = auth_data["user"]["token"] print("Authenticated as", auth_data["user"]["email"]) # ------------------------------------------------------- # GraphQL helpers # ------------------------------------------------------- GRAPHQL_URL = f"{BASE_URL}/graphql/v2" gql_headers = { "Content-Type": "application/json", "Authorization": token, "Cache-Control": "no-cache" } def run_query(query, variables=None): response = requests.post( GRAPHQL_URL, json={"query": query, "variables": variables or {}}, headers=gql_headers ) return response.json() # ------------------------------------------------------- # Step 1: Fetch all completed jobs (paginated) # completionDate is a Unix timestamp in milliseconds (string) # ------------------------------------------------------- JOB_QUERY = """ query($pageNumber: Int, $itemsPerPage: Int) { jobs(completed: true, pageNumber: $pageNumber, itemsPerPage: $itemsPerPage) { id title status priority startDate completionDate assignedUserDetails { id email firstName lastName } template { id title } steps { id title completed notes { id type title text meterEvidence dateCreated files { id name url fileType } } } } } """ PAGE_SIZE = 100 page = 1 all_jobs = [] while True: result = run_query(JOB_QUERY, {"pageNumber": page, "itemsPerPage": PAGE_SIZE}) jobs = result["data"]["jobs"] all_jobs.extend(jobs) ext = result.get("extensions", {}) total_pages = ext.get("allPages", 1) print(f"Fetched page {page}/{total_pages} ({len(all_jobs)} jobs so far)") if page >= total_pages or not jobs: break page += 1 print(f"\nTotal completed jobs fetched: {len(all_jobs)}") # ------------------------------------------------------- # Fetch all meter evidence reports and index by jobStepId # ------------------------------------------------------- METER_EVIDENCE_QUERY = """ query($pageNumber: Int, $itemsPerPage: Int) { metersEvidenceReport(pageNumber: $pageNumber, itemsPerPage: $itemsPerPage) { id jobId jobStepId jobStepName meterName meterUnitName meterEvidence meterOriginal meterFlag timeOfRecording } } """ page = 1 meter_evidence_by_step = {} while True: result = run_query(METER_EVIDENCE_QUERY, {"pageNumber": page, "itemsPerPage": PAGE_SIZE}) records = result["data"]["metersEvidenceReport"] for r in records: step_id = r["jobStepId"] meter_evidence_by_step.setdefault(step_id, []).append(r) ext = result.get("extensions", {}) total_pages = ext.get("allPages", 1) print(f"Fetched meter evidence page {page}/{total_pages} ({sum(len(v) for v in meter_evidence_by_step.values())} records so far)") if page >= total_pages or not records: break page += 1 print(f"Total meter evidence records: {sum(len(v) for v in meter_evidence_by_step.values())}") # ------------------------------------------------------- # Step 2: Filter by username and/or date range (client-side) # completionDate is Unix ms — convert to date for comparison # ------------------------------------------------------- def ms_to_date(ms_str): if not ms_str: return None return datetime.datetime.fromtimestamp(int(ms_str) / 1000, tz=datetime.timezone.utc).date() def parse_date(s): return datetime.datetime.strptime(s, "%Y-%m-%d").date() if s else None start_date = parse_date(args.start_date) end_date = parse_date(args.end_date) filtered_jobs = [] for job in all_jobs: if args.username: user = job.get("assignedUserDetails") or {} if user.get("email") != args.username: continue if start_date or end_date: completed = ms_to_date(job.get("completionDate")) if completed: if start_date and completed < start_date: continue if end_date and completed > end_date: continue filtered_jobs.append(job) print(f"Jobs after filtering: {len(filtered_jobs)}") # ------------------------------------------------------- # Step 3: Write to Excel # One row per evidence note; job/step fields repeat. # Jobs or steps with no notes still get a row (evidence fields blank). # ------------------------------------------------------- wb = openpyxl.Workbook() ws = wb.active ws.title = "Job History" columns = [ "Job ID", "Job Title", "Status", "Priority", "Template", "Assigned User", "User Email", "Started At", "Completed At", "Step ID", "Step Title", "Step Completed", "Note Type", "Note Title", "Note Text", "Evidence Meter Value", "Evidence Date", "File Name", "File URL", "File Type" ] for col, header in enumerate(columns, start=1): cell = ws.cell(row=1, column=col, value=header) cell.font = Font(bold=True) def fmt_date(ms_str): d = ms_to_date(ms_str) return str(d) if d else None row = 2 for job in filtered_jobs: user = job.get("assignedUserDetails") or {} user_name = f"{user.get('firstName') or ''} {user.get('lastName') or ''}".strip() template_title = (job.get("template") or {}).get("title") job_cells = [ job.get("id"), job.get("title"), job.get("status"), job.get("priority"), template_title, user_name, user.get("email"), fmt_date(job.get("startDate")), fmt_date(job.get("completionDate")) ] steps = job.get("steps") or [] if not steps: continue for step in steps: step_cells = [step.get("id"), step.get("title"), step.get("completed")] notes = step.get("notes") or [] meter_evidence = meter_evidence_by_step.get(step.get("id"), []) for me in meter_evidence: recorded_date = fmt_date(me.get("timeOfRecording")) ws.append(job_cells + step_cells + [ "meter", me.get("meterName"), None, me.get("meterEvidence"), recorded_date, None, None, None ]) row += 1 # Auto-size columns (cap at 60 chars wide) for col in ws.columns: max_len = max((len(str(cell.value)) for cell in col if cell.value), default=0) ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 60) timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S") filename = f"job_history_{timestamp}.xlsx" wb.save(filename) print(f"\nSaved {len(filtered_jobs)} jobs ({row - 2} rows) to {filename}") ``` ## Requirements ```bash theme={null} pip install requests openpyxl ``` * **Python 3.7+** * **requests** — HTTP client for the REST auth call and GraphQL queries * **openpyxl** — reads and writes `.xlsx` files without needing Excel installed *** ## Usage ```bash theme={null} # Export all completed jobs python3 job_history_export.py # Filter by assigned user python3 job_history_export.py --username john@example.com # Filter by date range python3 job_history_export.py --start-date 2025-01-01 --end-date 2025-03-25 # Combine filters python3 job_history_export.py --username john@example.com --start-date 2025-01-01 ``` All three flags are optional and can be combined freely. Dates must be in `YYYY-MM-DD` format. *** ## How It Works, Step by Step ### 1. Authentication The script POSTs credentials to the Manifest REST endpoint: ``` POST /rest/signin/v2 Content-Type: application/json { "email": "...", "password": "..." } ``` The response JSON contains `user.token`, which is a JWT used as the `Authorization` header on every subsequent GraphQL request. ### 2. Fetching Completed Jobs (Paginated) All completed jobs are fetched with a single GraphQL query, paginated in batches of 100: ```graphql theme={null} query($pageNumber: Int, $itemsPerPage: Int) { jobs(completed: true, pageNumber: $pageNumber, itemsPerPage: $itemsPerPage) { id title status priority startDate completionDate assignedUserDetails { id email firstName lastName } template { id title } steps { id title completed notes { id type title text meterEvidence dateCreated files { id name url fileType } } } } } ``` The API returns a `extensions.allPages` field that tells the script the total number of pages. The loop continues until all pages have been fetched. > **Note on dates:** `completionDate` and `startDate` are Unix timestamps in **milliseconds**, returned as strings. The script converts these with `datetime.fromtimestamp(int(ms) / 1000)`. ### 3. Fetching Meter Evidence (Paginated) A second paginated query fetches all meter evidence records from `metersEvidenceReport`. These are indexed into a dictionary keyed by `jobStepId` so they can be joined with the steps from Step 2: ```python theme={null} meter_evidence_by_step.setdefault(step_id, []).append(record) ``` This avoids a separate API call per step — the full evidence dataset is loaded once and looked up by key. ### 4. Client-Side Filtering Because the API doesn't support server-side filtering by user or date range on the `jobs` query, filtering happens in Python after all data is fetched: * **Username filter** — compares `assignedUserDetails.email` against `--username` * **Date range filter** — converts `completionDate` milliseconds to a `datetime.date` and compares against `--start-date` / `--end-date` Jobs that don't pass both checks are discarded before writing. ### 5. Writing the Excel File The script uses `openpyxl` to create a workbook with a single sheet called **Job History**. The columns are: | Column | Source | | -------------------- | --------------------------------------------- | | Job ID | `job.id` | | Job Title | `job.title` | | Status | `job.status` | | Priority | `job.priority` | | Template | `job.template.title` | | Assigned User | `firstName + lastName` | | User Email | `assignedUserDetails.email` | | Started At | `job.startDate` (converted) | | Completed At | `job.completionDate` (converted) | | Step ID | `step.id` | | Step Title | `step.title` | | Step Completed | `step.completed` | | Note Type | `"meter"` (hardcoded for meter evidence rows) | | Note Title | `meterName` | | Note Text | *(blank for meter evidence)* | | Evidence Meter Value | `meterEvidence` | | Evidence Date | `timeOfRecording` (converted) | | File Name | *(blank for meter evidence)* | | File URL | *(blank for meter evidence)* | | File Type | *(blank for meter evidence)* | There is **one row per evidence record**. Job and step fields repeat across rows that belong to the same step. After writing, the script auto-sizes each column (capped at 60 characters wide) using the longest value in that column. *** ## Adapting the Script for Other Reports The script is structured in clearly separated phases — auth, query, filter, write — making it straightforward to swap in different queries or output shapes. ### Change the GraphQL Query The queries are plain multi-line strings assigned to variables (`JOB_QUERY`, `METER_EVIDENCE_QUERY`). To report on a different resource, replace the query string and update the data extraction path: ```python theme={null} # Example: query assets instead of jobs ASSET_QUERY = """ query($pageNumber: Int, $itemsPerPage: Int) { assets(pageNumber: $pageNumber, itemsPerPage: $itemsPerPage) { id name assetClass { title } location { name } } } """ result = run_query(ASSET_QUERY, {"pageNumber": page, "itemsPerPage": PAGE_SIZE}) records = result["data"]["assets"] ``` ### Add or Remove Columns The `columns` list defines the header row. Add, remove, or reorder entries there, then match the same changes to the `ws.append(...)` call that writes each data row. Keeping both in sync is the only constraint. ### Add Server-Side Filters Some Manifest queries accept filter arguments directly. If the query you're using supports them, pass them as GraphQL variables rather than filtering in Python — this reduces the amount of data transferred: ```python theme={null} # Pass filters as variables result = run_query(JOB_QUERY, { "pageNumber": page, "itemsPerPage": PAGE_SIZE, "assignedUserId": "abc123" # hypothetical filter argument }) ``` ### Write Multiple Sheets `openpyxl` supports multiple sheets in a single workbook. Create additional sheets before saving: ```python theme={null} ws_summary = wb.create_sheet(title="Summary") ws_summary.append(["Total Jobs", len(filtered_jobs)]) ws_summary.append(["Total Evidence Rows", row - 2]) ``` ### Change the Output Format Swap `openpyxl` for the `csv` module to write a flat CSV instead: ```python theme={null} import csv with open(filename.replace(".xlsx", ".csv"), "w", newline="") as f: writer = csv.writer(f) writer.writerow(columns) for data_row in rows: writer.writerow(data_row) ``` ### Point at a Different Environment The `BASE_URL` constant at the top of the script controls the target environment. Change it to switch between staging and production: ```python theme={null} BASE_URL = "https://app.taqmanifest.com" # production # BASE_URL = "https://test.taqmanifest.com" # staging ``` *** ## Security Note The script currently has credentials hardcoded as string literals. Before sharing or deploying the script, move them to environment variables: ```python theme={null} import os login = os.environ["MANIFEST_EMAIL"] password = os.environ["MANIFEST_PASSWORD"] ``` Then set them in your shell before running: ```bash theme={null} export MANIFEST_EMAIL="you@example.com" export MANIFEST_PASSWORD="yourpassword" python3 job_history_export.py ``` *** ## Summary `job_history_export.py` demonstrates the typical pattern for building Manifest reports: 1. Authenticate once via REST to get a token 2. Page through the relevant GraphQL queries until all data is fetched 3. Join or filter the data in Python 4. Write the result to Excel with `openpyxl` Because each phase is independent, swapping in a different query or output format requires changes in only one or two places, making it a good starting template for any new report you want to build against the Manifest API.