When you save your Google Maps Location History as a backup, you can download it in JSON format. However, JSON files are not easy to read as-is, so converting them into CSV makes the data much more accessible. This article explains how to convert Google Maps backup files into CSV and how to handle them in Google Sheets.
Structure of the JSON File
A Google Maps backup file (JSON) has the following structure:
{
"timelineObjects": [
{
"activitySegment": {
"startLocation": { "latitudeE7": "start_latitude", "longitudeE7": "start_longitude" },
"endLocation": { "latitudeE7": "end_latitude", "longitudeE7": "end_longitude" },
"duration": { "startTimestamp": "start_time", "endTimestamp": "end_time" },
"distance": "distance_value",
"activityType": "activity_type",
"confidence": "confidence_level"
}
},
{
"placeVisit": {
"location": { "latitudeE7": "visit_latitude", "longitudeE7": "visit_longitude" },
"duration": { "startTimestamp": "visit_start_time", "endTimestamp": "visit_end_time" },
"placeConfidence": "place_confidence",
"visitConfidence": "visit_confidence"
}
}
]
}
This JSON file contains your location history as an array under timelineObjects.
Each object is stored as either an activitySegment or a placeVisit.
Converting JSON to CSV
To convert the JSON file into CSV format, you can use the following headers:
- startLocationLatitude
- startLocationLongitude
- endLocationLatitude
- endLocationLongitude
- startTimestamp
- endTimestamp
- distance
- activityType
- confidence
- visitLatitude
- visitLongitude
- visitStartTimestamp
- visitEndTimestamp
- placeConfidence
- visitConfidence
Steps
- Load the JSON file.
- Extract the necessary data and format it into CSV.
- Save the output as a CSV file.
Below is an example Python script for this process:
import json
import csv
# Load the JSON file
with open('backup.json', 'r') as file:
data = json.load(file)
# Write to CSV
with open('output.csv', 'w', newline='') as csvfile:
fieldnames = [
'startLocationLatitude', 'startLocationLongitude', 'endLocationLatitude', 'endLocationLongitude',
'startTimestamp', 'endTimestamp', 'distance', 'activityType', 'confidence',
'visitLatitude', 'visitLongitude', 'visitStartTimestamp', 'visitEndTimestamp',
'placeConfidence', 'visitConfidence'
]
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
for item in data['timelineObjects']:
if 'activitySegment' in item:
row = {
'startLocationLatitude': item['activitySegment']['startLocation']['latitudeE7'],
'startLocationLongitude': item['activitySegment']['startLocation']['longitudeE7'],
'endLocationLatitude': item['activitySegment']['endLocation']['latitudeE7'],
'endLocationLongitude': item['activitySegment']['endLocation']['longitudeE7'],
'startTimestamp': item['activitySegment']['duration']['startTimestamp'],
'endTimestamp': item['activitySegment']['duration']['endTimestamp'],
'distance': item['activitySegment']['distance'],
'activityType': item['activitySegment']['activityType'],
'confidence': item['activitySegment']['confidence'],
}
writer.writerow(row)
elif 'placeVisit' in item:
row = {
'visitLatitude': item['placeVisit']['location']['latitudeE7'],
'visitLongitude': item['placeVisit']['location']['longitudeE7'],
'visitStartTimestamp': item['placeVisit']['duration']['startTimestamp'],
'visitEndTimestamp': item['placeVisit']['duration']['endTimestamp'],
'placeConfidence': item['placeVisit']['placeConfidence'],
'visitConfidence': item['placeVisit']['visitConfidence'],
}
writer.writerow(row)
Importing JSON Data into Google Sheets
To load JSON data directly into Google Sheets, you can use Google Apps Script. Follow these steps:
- Open a Google Sheet.
- Go to Extensions > Apps Script.
- Paste the following code:
function importJSON() {
var url = 'https://example.com/path/to/your/backup.json'; // JSON file URL
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var header = [
'startLocationLatitude', 'startLocationLongitude', 'endLocationLatitude', 'endLocationLongitude',
'startTimestamp', 'endTimestamp', 'distance', 'activityType', 'confidence',
'visitLatitude', 'visitLongitude', 'visitStartTimestamp', 'visitEndTimestamp',
'placeConfidence', 'visitConfidence'
];
sheet.appendRow(header);
var timelineObjects = json.timelineObjects;
for (var i = 0; i < timelineObjects.length; i++) {
var item = timelineObjects[i];
if (item.activitySegment) {
var row = [
item.activitySegment.startLocation.latitudeE7, item.activitySegment.startLocation.longitudeE7,
item.activitySegment.endLocation.latitudeE7, item.activitySegment.endLocation.longitudeE7,
item.activitySegment.duration.startTimestamp, item.activitySegment.duration.endTimestamp,
item.activitySegment.distance, item.activitySegment.activityType, item.activitySegment.confidence,
'', '', '', '', '', ''
];
sheet.appendRow(row);
} else if (item.placeVisit) {
var row = [
'', '', '', '', '', '',
'', '', '',
item.placeVisit.location.latitudeE7, item.placeVisit.location.longitudeE7,
item.placeVisit.duration.startTimestamp, item.placeVisit.duration.endTimestamp,
item.placeVisit.placeConfidence, item.placeVisit.visitConfidence
];
sheet.appendRow(row);
}
}
}
- Save the script.
- Run importJSON from the Run menu.
Extracting Dates and Weekdays
Because timestamps are hard to read, you may want to extract readable dates or weekdays.
Extracting the date
If startTimestamp is in cell A2:
=DATEVALUE(MID(A2, 1, 10))
Extracting the weekday
=TEXT(DATEVALUE(MID(A2, 1, 10)), "dddd")
These formulas make it easy to convert timestamps into meaningful calendar information.
Summary
In this article, we explained how to convert Google Maps backup files (JSON) into CSV, how to import JSON directly into Google Sheets using Apps Script, and how to extract dates and weekdays from timestamp data. These methods allow you to view and manage your Google Maps Location History more easily.
