Export Google Search JSON to CSV for Analysis

TL;DR:

• A Google search results CSV needs the query context beside its rows. Preserve the request and observation times so each result remains interpretable after export.
• CSV is a projection of JSON. Keep the raw capture, distinguish absent and null values, and record the meaning of transformed cells.
• A header-only file needs a run record. Pending, failed, unmapped, and present-empty captures can all produce no organic rows for different reasons.

A spreadsheet can preserve every title and still lose the meaning of the search that produced it. Without the query, submitted context, and observation time, a row becomes difficult to compare or trace. A blank cell creates another ambiguity: was the value missing, null, or rejected by the transform?

Scrapeless Google Search API supplies structured search data upstream. This guide builds a Google search results CSV from a saved capture using a local Python exporter. CSV generation, spreadsheet handling, and file storage are functions of the example program, not a claim that the API directly returns this export format.

Prerequisites and the Capture Envelope

The exporter needs Python and a saved JSON capture with request, http_status, and response. Include run_id, requested_at, and received_at when the collector records them. These outer fields belong to the collection application, not the API's native response wrapper.

The Google Search request workflow distinguishes HTTP 200 task data from HTTP 201 pending work. Preserve the HTTP outcome with the response before flattening anything. Reading an absent organic field from a pending response and substituting an empty list would erase that distinction.

No API key or third-party package is needed for the local transform. Producing an account capture is a separate authenticated step, which was not performed for this article. The local checks use synthetic captures to exercise data types, empty states, Unicode, and spreadsheet-sensitive text.

The JSON value model preserves arrays, objects, nulls, and strings that do not map directly to flat cells. Keep the original capture available after export so later analysts can recover omitted modules and exact original values.

Decide Which Columns Carry Context and Meaning

Repeat the run identifier, request and receipt times, exact query when available, and serialized request on each result row. The complete request preserves settings beyond the fixed convenience columns and makes the export easier to audit.

The Google Search parameters include country and language settings, but full-URL mode can carry configuration inside url. A blank q column therefore does not necessarily indicate a missing submitted query. Inspect request_json; do not reconstruct an effective query by guessing from an empty convenience field.

Organic array order and returned position are separate columns. ordinal records the item's zero-based source order. position is retained only when it is a positive integer, with booleans rejected explicitly. The exporter does not manufacture a ranking from array order.

Text fields and position each have a companion state column. missing, null, and value explain the common cases. invalid marks an unusable position; unexpected_type marks a nonstring text-field value that is retained as serialized JSON instead of silently discarded.

Separate CSV Quoting From Spreadsheet Interpretation

A CSV writer handles delimiters and quoted text; it does not decide how a spreadsheet evaluates a cell. Use Python's CSV writer for commas, quotation marks, and embedded line breaks rather than joining strings manually.

The program opens its output with newline="" and utf-8-sig. The first setting lets the CSV module manage record boundaries. The UTF-8 signature can help a spreadsheet recognize accented or non-Latin text, but the destination application's import behavior still needs inspection.

A value beginning with a formula character may be interpreted as an expression when opened in a spreadsheet. The CSV injection guidance describes why syntactically correct CSV is not enough to make untrusted text inert.

This exporter prefixes an apostrophe for selected formula-leading characters, including full-width variants, and for leading tabs or line breaks. It applies the policy to request context as well as result text. Numeric positions use a separate validation rule.

Start Scraping with Scrapeless

Power up your web scraping and automation workflow with Scrapeless!
Sign up today and get $5 in free credit — no credit card required.

Claim your free credit now in the Scrapeless Dashboard.

Run the Local JSON-to-CSV Exporter

Save the program as serp_csv.py, then run python3 serp_csv.py capture.json organic.csv. It reads the capture and writes organic.csv plus the companion organic.csv.run.json. Existing output files are replaced; choose a dedicated export directory or unique names when retaining several versions.

The program refuses output paths that would overwrite the input capture. It does not change the original JSON, submit API requests, or retrieve pending tasks.

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 sidecar records the collection state, projected row count, source path, request, timestamps, and export policy. A header-only CSV remains explainable as long as that record stays with it. Keep both output files together when handing the export to another person.

Inspect Nulls and Malformed Organic Data

A present empty organic array produces state=empty. HTTP 201 produces pending, a missing HTTP status becomes transport_error, and another non-200 status becomes http_error in this application's state model. These outcomes can share a zero row count without sharing a meaning.

If the organic field is absent, is not an array, or contains a non-object item, the entire projection becomes unmapped. The program exports no organic rows for that run. This avoids quietly dropping malformed items while describing the remaining rows as a complete projection.

Optional fields are handled more narrowly. A missing snippet leaves a blank cell and an explicit field state without discarding the organic item. An unexpected complex value remains represented as JSON text, so the unusual value can still be inspected.

These are application policies. Document them beside the export and revise them deliberately if the consumer needs a different schema. A field-state column is useful precisely because another analyst should not have to infer the transformation from a few visible cells.

Verify the Spreadsheet Handoff

Read the output with a CSV parser and compare logical records, not physical lines. A quoted snippet may contain a newline without creating a new search result. Confirm that the sidecar count matches the parsed rows and each row carries its request context.

Check Unicode and formula-sensitive values in the application the team actually uses. The apostrophe policy intentionally changes the exported representation and may be visible in some viewers. It is not a universal guarantee across all import settings and spreadsheet applications.

Include save-and-reopen behavior in the test. The spreadsheet import discussion illustrates why escape handling deserves application-specific review. Import relevant columns as text, and use the original JSON whenever exact source strings are required.

A successful local round trip confirms the consistency of this projection. It does not establish that the upstream search sample is exhaustive, representative, or suitable for a ranking conclusion without further analysis.

Conclusion

Export the request context with the results, keep field states where blanks would hide meaning, and retain the raw JSON. A CSV plus a run record gives the next analyst enough information to distinguish a measured empty slice from unavailable collection.

A SERP content research workflow can use the exported observations to build a reviewable reading list before drafting a content brief.

Build Your Next Search Observation

Use Scrapeless Google Search API for the search data in this workflow. Review Scrapeless pricing when planning collection, and keep the Google Search parameters beside your configuration.

Discuss your implementation with the community on Discord or Telegram.

FAQ

Q: Does Google Search API directly create this CSV?

No. The demonstrated exporter transforms saved JSON locally. File generation and the sidecar schema belong to the Python program.

Q: Why keep request_json when the query already has a column?

The complete request preserves optional settings and full-URL input that a query column alone cannot represent.

Q: Do CSV quotes prevent formula interpretation?

No. Delimiter handling and spreadsheet evaluation are separate. Apply a documented text policy and verify the intended import workflow.

Q: Does an empty CSV mean the search returned no results?

Not necessarily. Inspect the sidecar state to distinguish a present empty array from pending, failed, or unmapped collection.

Q: Can the original values be recovered?

Yes, from the retained input capture. The CSV is a projection and intentionally transforms some text representations for spreadsheet use.