A CSV export can look clean while losing the information that makes a search result interpretable. A title and URL do not tell the next analyst which query, market, or observation produced the row. A blank cell also cannot explain whether a field was absent, explicitly null, or invalid for the expected type.
This tutorial exports the organic array from a saved Google SERP capture while keeping request context on every row. It also writes a run metadata file so an empty export remains distinguishable from pending or failed collection.
Scrapeless Google Search API is the upstream source. The exporter is a local Python transform; it does not send API requests, retrieve pending tasks, or measure Google results itself.
Start With a Capture Envelope
The input file should contain the original request, the recorded http_status, and the API response, together with a run identifier and client timestamps when available. These outer fields belong to your collector, not to the API response schema.
The Google Search request workflow returns task data with HTTP 200 and a pending task with HTTP 201. Preserve that distinction before exporting. Reading organic_results from every JSON body without checking the outcome can turn a pending task into a misleading empty spreadsheet.
Keep the original capture file. CSV is a projection for analysis, not a lossless replacement for nested JSON. The exporter retains the request on each row and points back to the original file in its sidecar, but it does not copy every response module into the spreadsheet.
No API key is needed for the transform. Producing a live capture requires your own authenticated collection step; this article does not claim such a run. The local checks use synthetic records that deliberately contain nulls, missing fields, and spreadsheet-sensitive strings.
Decide Which Meaning Each Column Preserves
The exported context contains the run identifier, requested and received times, exact query when parameter mode supplies it, and the serialized request. The complete request protects settings that a short fixed list of columns might overlook.
For example, country and language inputs belong to the search context. Full-URL mode may carry settings inside url rather than separate fields. The q column can therefore be blank while request_json still preserves the submitted URL. Do not reconstruct an effective query by guessing from missing columns.
Each organic item gets an array ordinal and the returned position. Ordinal is zero-based source order. Position is only retained as a number if the value is a positive integer; a Boolean is not accepted as a position despite Python's Boolean/integer relationship.
The title, link, snippet, and position also receive state columns. missing means the key was absent, null means it was explicitly null, and value means the expected value was retained. An invalid position gets invalid; an unexpected type in a text field is serialized as JSON and marked unexpected_type.
Use a CSV Writer, Not String Concatenation
A field can contain commas, quotation marks, or line breaks. Python's CSV writer handles those values according to its dialect instead of relying on a manual join operation.
Open the output with newline="" so the CSV module handles record boundaries. The program uses utf-8-sig to include a UTF-8 signature, which can help spreadsheet applications recognize Unicode text. Import behavior still depends on the application and its settings.
CSV quoting solves delimiter handling. It does not by itself tell a spreadsheet to treat a cell as inert text. That is a separate concern when titles, snippets, or even queries come from untrusted data.
Make Spreadsheet Interpretation Explicit
Some spreadsheet applications interpret values beginning with characters such as =, +, -, or @, including full-width variants in some locales as formulas. Leading control characters and whitespace can complicate that behavior. The CSV injection guidance explains why an otherwise valid CSV can still be interpreted in an unintended way.
The exporter prefixes an apostrophe when a text value begins with a relevant formula character after whitespace, or begins with a tab or line break. It applies this policy to context fields as well as result text. Numeric positions are validated separately.
This is a documented spreadsheet-oriented transform, not a universal security guarantee across every application and import setting. Test the file in the spreadsheet application your team uses, import relevant columns as text, and keep the original JSON for exact values. Saving and reopening a file can change how escape prefixes are handled; include that path in the spreadsheet import test. The prefix may be visible in some viewers and intentionally changes the exported cell representation.
Do not remove that prefix later merely to make a screenshot cleaner without considering how the file will be opened. If another program needs the exact original string, read the archived JSON or use an explicitly defined machine-data export.
Run the Local Exporter
Save the program as serp_csv.py and run python3 serp_csv.py capture.json organic.csv, replacing the input filename with a saved capture. It uses only the Python standard library and writes organic.csv plus organic.csv.run.json.
The output files are replaced if they already exist. The program refuses output paths that would overwrite the input capture. Use a dedicated export directory or unique filenames when retaining several versions.
import argparse
import csv
import json
from pathlib import Path
FIELDS = ["run_id", "requested_at", "received_at", "q", "request_json", "ordinal",
"position", "position_state", "title", "title_state", "link", "link_state",
"snippet", "snippet_state"]
def spreadsheet_text(value):
text = "" if value is None else str(value)
stripped = text.lstrip()
if (stripped.startswith(("=", "+", "-", "@", "＝", "＋", "－", "＠"))
or text.startswith(("\t", "\r", "\n"))):
return "'" + text
return text
def field(row, name):
if name not in row:
return "", "missing"
value = row[name]
if value is None:
return "", "null"
if name == "position":
return (value, "value") if type(value) is int and value > 0 else ("", "invalid")
if isinstance(value, str):
return spreadsheet_text(value), "value"
return spreadsheet_text(json.dumps(value, ensure_ascii=False)), "unexpected_type"
def export(source, target):
source, target = Path(source), Path(target)
sidecar = target.with_suffix(target.suffix + ".run.json")
if source.resolve() in (target.resolve(), sidecar.resolve()):
raise ValueError("Output paths must differ from input")
record = json.loads(source.read_text(encoding="utf-8"))
request = record.get("request")
if not isinstance(request, dict) or not isinstance(request.get("input"), dict):
raise ValueError("Expected a capture record with request.input")
payload = record.get("response")
rows = payload.get("organic_results") if isinstance(payload, dict) else None
status = record.get("http_status")
if status == 201:
state, rows = "pending", []
elif status != 200:
state, rows = ("transport_error" if status is None else "http_error"), []
elif not isinstance(rows, list) or any(not isinstance(x, dict) for x in rows):
state, rows = "unmapped", []
else:
state = "observed" if rows else "empty"
context = {
"run_id": spreadsheet_text(record.get("run_id")),
"requested_at": spreadsheet_text(record.get("requested_at")),
"received_at": spreadsheet_text(record.get("received_at")),
"q": spreadsheet_text(request["input"].get("q")),
"request_json": spreadsheet_text(json.dumps(request, ensure_ascii=False, sort_keys=True)),
}
with target.open("w", encoding="utf-8-sig", newline="") as handle:
writer = csv.DictWriter(handle, fieldnames=FIELDS)
writer.writeheader()
for ordinal, item in enumerate(rows):
output = dict(context, ordinal=ordinal)
for name in ("position", "title", "link", "snippet"):
output[name], output[name + "_state"] = field(item, name)
writer.writerow(output)
sidecar.write_text(json.dumps({"source": str(source), "run_id": record.get("run_id"),
"state": state, "rows": len(rows), "request": request,
"requested_at": record.get("requested_at"), "received_at": record.get("received_at"),
"export_policy": "spreadsheet_text_prefix_v1; original values remain in source JSON"},
ensure_ascii=False, indent=2), encoding="utf-8")
print(f"Exported {len(rows)} organic rows; state={state}; metadata={sidecar}")
return state, len(rows)
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument("source")
parser.add_argument("target")
args = parser.parse_args()
export(args.source, args.target)
The program writes a header even when there are no projected organic rows. Its sidecar records the state, row count, source file, request, timestamps, and export-policy description. Keep the CSV and sidecar together when handing them to another analyst.
Inspect Empty and Malformed Results
An empty array produces a header-only CSV with state=empty. A pending task produces the same visible row count but a different sidecar state. An HTTP error or transport failure is also preserved separately.
If the organic field is missing, has the wrong type, or contains a non-object item, the whole run becomes unmapped and no organic rows are exported. This avoids quietly skipping malformed items while presenting the remaining rows as a complete projection. Inspect the raw response before changing that rule.
Individual optional fields are handled more narrowly. A missing snippet does not discard an otherwise usable organic item. Its state column tells the analyst why the cell is blank. A complex unexpected text-field value stays represented as JSON text with an explicit type warning.
These are application policies. Document them with the export because another transform may make different choices. A downstream analyst should not have to infer how nulls were handled by looking at a handful of rows.
Check the Handoff, Not Only the Row Count
Read the file back through a CSV parser and compare the parsed columns, rather than counting physical lines. A quoted snippet can contain a newline without creating another logical result row.
Check that every row carries its run context and that Unicode text survives the round trip. Inspect formula-sensitive cells in your actual spreadsheet application. The local automated checks can validate the prefix policy and CSV parsing, but cannot prove every future application's interpretation.
Also compare the sidecar row count with the parsed record count. Keep the source file accessible, since the spreadsheet omits other search modules and preserves some values in transformed form. A successful export means this projection is internally consistent, not that the upstream search data is complete or representative.
Conclusion
Export context with the results, retain field states where blanks would be ambiguous, and give every header-only file a run record. A CSV built this way is easier to inspect and less likely to turn missing collection or spreadsheet interpretation into a misleading finding.
FAQ
Why keep the whole request in a CSV column?
It preserves settings beyond a short fixed schema, including URL-mode input and optional controls. The query column remains convenient, while the serialized request carries the complete submitted configuration.
Does CSV quoting prevent formula interpretation?
No. Delimiter escaping and spreadsheet interpretation are different. Use a documented text policy and verify the import settings in the destination application.
Can an empty CSV prove that Google returned no results?
No. Inspect the run state. Pending, failed, unmapped, and present-empty responses are distinct outcomes.
Does the exporter change the original JSON?
No. It reads the capture and writes separate files. The original values remain available there for exact reconstruction.
Top comments (0)