Google Sheets to Woosmap
In this tutorial you will learn how to connect to Google Sheets using OAuth 2.0, get the data from a private spreadsheet accessible with your authorized account, convert and import them to Woosmap.
- Python Script : googlesheet_to_woosmap.py
Prerequisites
To run this tutorial, you’ll need:
- Python 2.7 or greater.
- The pip package management tool.
- Access to the internet.
- A Woosmap account.
- A Google Account
Google Sheets
The Google Sheets API lets you read values from cells, ranges, sets of ranges and entire sheets. The examples on this page illustrate how some common read operations can be achieved on a private google spreadsheet imported from this csv file and with the spreadsheets.values collection of this API.
Authorize Requests
When your application requests private data, the request must be authorized by an authenticated user who has access to that data with OAuth 2.O When your application requests public data, the request doesn’t need to be authorized, but does need to be accompanied by an API key identifier.
The details of the authorization process, or “flow,” for OAuth 2.0 vary somewhat depending on what kind of application you’re writing. The following general process applies to all application types:
- When you create your application, you register it using the Google API Console. Google then provides information you’ll need later, such as a client ID and a client secret.
- Activate the Google Sheets API in the Google API Console. (If the API isn’t listed in the API Console, then skip this step.)
- When your application needs access to user data, it asks Google for a particular scope of access.
- Google displays a consent screen to the user, asking them to authorize your application to request some of their data.
- If the user approves, then Google gives your application a short-lived access token.
- Your application requests user data, attaching the access token to the request.
- If Google determines that your request and the token are valid, it returns the requested data.
Get Credentials
First, you’ll need to get a credentials JSON file to authorize requests to the Google Sheets API. Follow this wizard to create or select a project in the Google Developers Console and automatically turn on the API. For detailed steps, go to https://developers.google.com/sheets/api/quickstart/python and check the Turn on the Google Sheets API section.
For our needs, please specify a Redirect URI when configuring your credentials and set it to http://localhost:8080
Once you have downloaded the client_secret.json file, you can then begin writing your script.
Run the following command to install the library using pip:
pip install --upgrade google-api-python-client
And implement the following code to get your credentials:
from oauth2client.client import flow_from_clientsecrets
from oauth2client import tools
from oauth2client.file import Storage
GOOGLE_CREDENTIALS_PATH = 'client_secret.json'
class GoogleSheets(object):
"""A wrapper around the Google Sheets API."""
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
APPLICATION_NAME = 'Google Sheets To Woosmap'
REDIRECT_URI = 'http://localhost:8080'
def __init__(self, credentials_path):
self.credentials_path = credentials_path
self.credentials = self.get_credentials()
self.http = self.credentials.authorize(httplib2.Http())
if self.credentials is not None and self.credentials.access_token_expired:
self.credentials.refresh(self.http)
def get_credentials(self):
storage_path = '.' + os.path.splitext(os.path.basename(__file__))[0] + '.credentials'
storage = Storage(storage_path)
credentials = storage.get() if os.path.exists(storage_path) else None
if credentials is None or credentials.invalid:
flow = flow_from_clientsecrets(self.credentials_path, self.SCOPES, redirect_uri=self.REDIRECT_URI)
flow.user_agent = self.APPLICATION_NAME
credentials = tools.run_flow(flow, storage)
return credentials
Read Spreadsheet Values
You can use the Google API Discovery Service to build client libraries, IDE plugins, and other tools that interact with Google APIs. It provides a lightweight, JSON-based API that exposes machine-readable metadata about Google APIs. In this tutorial, we’ve used this API to interact with the Google Sheets API.
So first step before reading values from your spreadsheet is to build a service:
from apiclient import discovery
API_NAME = 'sheets'
API_VERSION = 'v4'
DISCOVERY_URI = 'https://sheets.googleapis.com/$discovery/rest?version={apiVersion}'
def build_service(self):
self.http = self.credentials.authorize(httplib2.Http())
return discovery.build(self.API_NAME, self.API_VERSION, http=self.http,
discoveryServiceUrl=self.DISCOVERY_URI)
To read data from a sheet, you will need the spreadsheet ID and the sheet name.
def get_values(self):
self.range_name = self.get_first_sheetname()
return self.service.spreadsheets().values().get(spreadsheetId=self.spreadsheet_id,
range=self.range_name).execute()
The sheet name of the first sheet could be retrieved by this way:
def get_first_sheetname(self):
sheet_metadata = self.service.spreadsheets().get(spreadsheetId=self.spreadsheet_id).execute()
return sheet_metadata.get('sheets', '')[0].get("properties", {}).get("title", "Sheet1")
Import to Woosmap
We preferred to iterate over the data rows using a list of Dict that looks like: [{'field1': 'valueA'; 'field2':'valueB'},{'field1': 'valueC'; 'field2':'valueD'}].
It takes advantage of the header row with the column names and gives you the opportunity to grab it cell value by its name.
We transformed the rows using zip python module:
sheet_data = google_sheets.get_values().get('values', [])
header = sheet_data.pop(0)
assets_as_dict = [dict(zip(header, item)) for item in sheet_data]
The conversion of this flat data to a Woosmap required structure and the import process using Woosmap Data API are already explained in another tutorial: CSV to Woosmap.
